BigQuery中将结构体数组聚合为有序字符串字段的方法

核心思路 #

要保证part_id和part_value聚合后的字符串顺序完全对应，关键是给每个ID分组内的行指定唯一且固定的排序依据，再分别对两个字段进行有序聚合。

步骤1：给数据添加顺序标识（必做） #

如果原表没有天然的顺序字段（比如记录插入时间、自增ID），先为每个ID分组内的行生成排序序号，确保后续聚合顺序稳定：

WITH ordered_rows AS (
  SELECT 
    id,
    part_id,
    part_value,
    -- 替换成你实际的顺序字段（如create_time、insert_id），1=1仅为临时占位
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY 1=1) AS row_seq
  FROM your_table_name
)

步骤2：直接聚合为有序字符串 #

基于带顺序标识的数据集，分别对part_id和part_value做有序聚合，再转成字符串：

SELECT
  id,
  -- 用你需要的分隔符替换逗号
  ARRAY_TO_STRING(ARRAY_AGG(part_id ORDER BY row_seq), ',') AS aggregated_part_ids,
  ARRAY_TO_STRING(ARRAY_AGG(part_value ORDER BY row_seq), ',') AS aggregated_part_values
FROM ordered_rows
GROUP BY id

备选：从结构体数组拆分字段 #

如果已经生成了结构体数组（如你之前的查询结果），可以通过UNNEST带偏移量来拆分并保持顺序：

WITH struct_array_result AS (
  SELECT 
    id,
    ARRAY_AGG(STRUCT(part_id, part_value) ORDER BY row_seq) AS part_structs
  FROM ordered_rows
  GROUP BY id
)
SELECT
  id,
  ARRAY_TO_STRING(ARRAY_AGG(ps.part_id ORDER BY offset), ',') AS aggregated_part_ids,
  ARRAY_TO_STRING(ARRAY_AGG(ps.part_value ORDER BY offset), ',') AS aggregated_part_values
FROM struct_array_result,
UNNEST(part_structs) WITH OFFSET AS offset ps
GROUP BY id

关键注意事项 #

• 绝对不能省略排序依据：如果不指定ORDER BY，ARRAY_AGG的结果顺序是不确定的，会导致part_id和part_value的对应关系混乱，尤其是part_id不唯一的场景。
• 分隔符可自定义：将示例中的逗号替换为你业务需要的分隔符（如|、;）即可，确保两个聚合字段使用相同分隔符。

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