BigQuery中如何将ARRAY_AGG结果拆分为多个小数组及性能疑问

你遇到的问题是ARRAY_AGG(xxx LIMIT 1000000)只会取每个州的前100万条SSN聚合，不会把大数组拆分成多个小数组。下面是两种可行方案，以及性能对比：

一、分组阶段直接拆分（性能最优） #

这种方法不需要先生成超大数组，而是在分组前就给每个州内的SSN分配批次号，再按「State+批次号」聚合，一步完成拆分，内存压力小、效率高。

示例SQL：

SELECT 
  STATE,
  ARRAY_AGG(SOCIAL_SECURITY_NUMBER) AS ssn_array
FROM (
  SELECT 
    STATE,
    SOCIAL_SECURITY_NUMBER,
    -- 每100万条数据划分为一个批次
    FLOOR((ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY SOCIAL_SECURITY_NUMBER) - 1) / 1000000) AS batch_num
  FROM {table}
) t
GROUP BY STATE, batch_num
ORDER BY STATE, batch_num;

逻辑说明：

1. 内层查询用ROW_NUMBER()按State分区，给每个州的SSN依次编号；
2. 通过FLOOR((行号-1)/1000000)计算批次号，每100万条自动归为同一个批次；
3. 外层按State和批次号聚合，最终每个州会生成多个包含100万（或最后一批不足100万）SSN的小数组。

二、先分组再拆分数组（适合已有聚合结果的场景） #

如果已经提前生成了包含大数组的中间表，或者业务逻辑要求必须先聚合再拆分，可以用这种方法。但它需要先展开大数组再重新聚合，内存消耗更高，性能不如第一种。

示例SQL：

SELECT 
  STATE,
  ARRAY_AGG(ssn) AS ssn_array
FROM (
  SELECT 
    STATE,
    ssn,
    FLOOR((ROW_NUMBER() OVER (PARTITION BY STATE ORDER BY ssn) - 1) / 1000000) AS batch_num
  FROM (
    SELECT 
      STATE,
      UNNEST(ssn_large_array) AS ssn
    FROM (
      -- 先得到每个州的超大数组
      SELECT STATE, ARRAY_AGG(SOCIAL_SECURITY_NUMBER) AS ssn_large_array
      FROM {table}
      GROUP BY STATE
    ) t1
  ) t2
) t3
GROUP BY STATE, batch_num
ORDER BY STATE, batch_num;

逻辑说明：

1. 最内层先聚合出每个州的超大数组；
2. 用UNNEST将大数组拆分成单行数据；
3. 给拆分后的行分配批次号，再重新聚合为小数组。

性能对比结论 #

优先选择分组阶段直接拆分的方案，原因：

• 避免生成几十甚至几百MB级的超大数组，减少数据库内存占用，降低OOM（内存溢出）风险；
• 减少中间数据的生成与处理步骤，执行速度更快，尤其是处理千万级以上数据时，性能差距会更明显。

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