BigQuery：数组与结构体列的聚合去重及字段拆分重组

核心逻辑步骤 #

• 拆分C4列：用SPLIT函数拆分多值条目，配合TRIM清理元素前后空格
• 封装结构体：将C5、C6、C7列打包成结构体，便于后续聚合管理
• 分组去重聚合：通过ARRAY_AGG(DISTINCT)按拆分后的C4值分组，同时完成结构体数组的去重

完整SQL代码 #

WITH split_cte AS (
  SELECT
    TRIM(split_item) AS split_c4,
    STRUCT(C5 AS col5, C6 AS col6, C7 AS col7) AS cs_struct
  FROM `your_project.your_dataset.your_source_table`,
       UNNEST(SPLIT(C4, ',')) AS split_item
  WHERE C4 IS NOT NULL
)
SELECT
  split_c4,
  ARRAY_AGG(DISTINCT cs_struct) AS aggregated_struct_array
FROM split_cte
GROUP BY split_c4;

关键细节说明 #

• 若C4列的分隔符不是逗号，直接替换SPLIT(C4, ',')中的逗号即可（如分号、竖线等）
• UNNEST是BigQuery处理多值列的标准方式，会将拆分后的每个元素转为单独行
• DISTINCT嵌入ARRAY_AGG中，直接实现数组内结构体的去重，无需额外处理
• 如需保留数组内结构体的排序，可添加ORDER BY子句，例如：ARRAY_AGG(DISTINCT cs_struct ORDER BY col5 ASC)

内容的提问来源于stack exchange，提问作者Santanu Ghosh