BigQuery中如何按ID跨列聚合x、y值为数组？

针对你需求的纯BigQuery SQL方案，无需依赖UDF，完全适配大规模数据场景：

方案一：按顺序聚合x和y后合并数组 #

如果需要先收集所有x值（按原表行顺序），再收集所有y值合并成一个数组（和你给出的示例结果一致），可以用ARRAY_AGG分别聚合x和y，再用ARRAY_CONCAT合并两个数组：

WITH ordered_rows AS (
  -- 给每个id下的行添加序号，保证聚合顺序和原表一致
  SELECT
    id,
    x,
    y,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY x) AS row_num -- 这里的ORDER BY替换成你实际需要的排序字段，比如插入时间、主键等
  FROM
    `your-project.your-dataset.target-table`
)
SELECT
  id,
  ARRAY_CONCAT(
    ARRAY_AGG(x ORDER BY row_num),
    ARRAY_AGG(y ORDER BY row_num)
  ) AS array
FROM ordered_rows
GROUP BY id

关键说明： #

• ROW_NUMBER()的作用是确保每个id下的x和y聚合顺序与原表行顺序一致，如果你的表本身有明确排序键（如自增ID、时间戳），直接用该字段排序即可。
• ARRAY_AGG是BigQuery原生数组聚合函数，分布式处理效率极高，完全适配id数量庞大的场景。

方案二：逐行拼接x和y后展开（如果需求是每行的x,y连续排列） #

如果你的需求是把每行的x和y先组成[x, y]的子数组，再把所有子数组合并成一个大数组（比如id=a的结果是[1,2,2,3,3,4]），可以用ARRAY_FLATTEN实现：

WITH ordered_rows AS (
  SELECT
    id,
    x,
    y,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY x) AS row_num
  FROM
    `your-project.your-dataset.target-table`
)
SELECT
  id,
  ARRAY_FLATTEN(ARRAY_AGG([x, y] ORDER BY row_num), 1) AS array
FROM ordered_rows
GROUP BY id

这两种方案均为纯SQL实现，无需额外编写UDF，BigQuery会自动利用分布式架构处理大规模数据，比Python脚本的扩展性和效率更高。

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