如何在Google BigQuery中按数组列唯一元素分组聚合生成结果？

要实现这个需求，核心是先将数组类型的Matches拆分为单个匹配ID，再通过分组聚合合并关联的Name，具体实现步骤如下：

1. 合并表并拆分数组
   先用UNION ALL合并Table_1和Table_2的原始数据，再通过UNNEST(Matches)把每个Name对应的数组拆成一行一个匹配ID的扁平化结构——这一步是把多对多的数组关系转换成便于分组的行数据。

2. 按匹配ID分组聚合Name
   以拆分后的单个匹配ID为分组依据，使用BigQuery内置的STRING_AGG函数，将同一匹配ID下的所有Name拼接成逗号分隔的字符串，还可通过DISTINCT参数避免同一Name重复出现在同一匹配ID的结果中。

完整SQL代码 #

SELECT
  match_id,
  STRING_AGG(DISTINCT name, ', ') AS associated_names
FROM (
  -- 合并两张表并拆分Matches数组
  SELECT name, UNNEST(matches) AS match_id FROM Table_1
  UNION ALL
  SELECT name, UNNEST(matches) AS match_id FROM Table_2
) AS flattened_data
GROUP BY match_id
ORDER BY match_id;

代码细节说明 #

• UNION ALL：高效合并两张表的所有数据，若需要提前去重重复的name+match_id组合，可替换为UNION DISTINCT，但通常用UNION ALL配合后续STRING_AGG的DISTINCT更高效。
• UNNEST(matches)：将数组类型的Matches列展开，每个数组元素对应一行，生成独立的match_id字段。
• STRING_AGG(DISTINCT name, ', ')：对同一match_id下的Name去重后，用「逗号+空格」拼接成字符串，你可根据需求调整分隔符（比如仅保留逗号）。
• GROUP BY match_id：确保每个匹配ID只对应一行结果，实现按匹配ID聚合的目标。

内容的提问来源于stack exchange，提问作者Setu Kumar Basak