加入多个/一组条目

Question

我想合并下面两个表中的条目，以通过attr将obj与class匹配。

我在单个表中有许多具有不同名称、日期和其他信息的对象(未显示(。这些对象中的每一个都可以根据其属性落入类中。对象obj到其每个属性attr映射在表 A 中。类class到属性attr的映射如表 B 所示。

我想将每个对象obj映射到其匹配的类class，以给出表 C 中所示的所需输出。

在某些方面，这似乎是对组的联接操作。有没有办法在标准sql和/或Google BigQuery中做到这一点？

重要的一点是，class和obj的attr都没有排序。

表 A：

-------------------
|  obj  |  attr   | 
-------------------
|  obj1 |     I   |
|  obj1 |     II  |  
|  obj2 |     I   |
|  obj2 |     II  |
|  obj3 |     I   |
|  obj3 |     II  |  
|  obj3 |     II  |
|  obj4 |     III |
|  obj4 |     I   |
-------------------

表 B：

-------------------
| attr  |  class  | 
-------------------
|  I    |    A    |
|  II   |    A    |  
|  I    |    B    |
|  III  |    B    |
-------------------

期望输出(表C(：

-----------------------
|  obj     |  class   | 
-----------------------
|  obj1    |    A     |
|  obj2    |    A     |
|  obj4    |    B     |
-----------------------

(请注意，obj3不会落入class A，因为它具有额外的II attr。

Answer 1

下面是 BigQuery Standard SQL

#standardSQL
SELECT obj, class FROM (
  SELECT obj, STRING_AGG(attr ORDER BY attr) attr
  FROM `project.dataset.TableA`
  GROUP BY obj
) JOIN (
  SELECT class, STRING_AGG(attr ORDER BY attr) attr
  FROM `project.dataset.TableB`
  GROUP BY class
) USING(attr)  

您可以使用问题中的虚拟数据对其进行测试/播放，如下所示

#standardSQL
WITH `project.dataset.TableA` AS (
  SELECT 'obj1' obj, 'I' attr UNION ALL
  SELECT 'obj1', 'II' UNION ALL
  SELECT 'obj2', 'I' UNION ALL
  SELECT 'obj2', 'II' UNION ALL
  SELECT 'obj3', 'I' UNION ALL
  SELECT 'obj3', 'II' UNION ALL
  SELECT 'obj3', 'II' UNION ALL
  SELECT 'obj4', 'III' UNION ALL
  SELECT 'obj4', 'I' 
), `project.dataset.TableB` AS (
  SELECT 'I' attr, 'A' class UNION ALL
  SELECT 'II', 'A' UNION ALL
  SELECT 'I', 'B' UNION ALL
  SELECT 'III', 'B' 
)
SELECT obj, class FROM (
  SELECT obj, STRING_AGG(attr ORDER BY attr) attr
  FROM `project.dataset.TableA`
  GROUP BY obj
) JOIN (
  SELECT class, STRING_AGG(attr ORDER BY attr) attr
  FROM `project.dataset.TableB`
  GROUP BY class
) USING(attr)   

输出为：

obj     class    
----    -----
obj1    A    
obj2    A    
obj4    B