BigQuery：窗口子句拼接数组报错，求解决方案

由于BigQuery暂不支持将ARRAY_CONCAT_AGG作为分析函数（窗口函数）使用，你可以通过以下两步实现需求：

1. 先为每个分组内的行按sub_grp排序并分配行号，明确每行的顺序位置；
2. 用ARRAY_AGG窗口函数收集当前行之前的所有数组，再通过嵌套UNNEST将这些数组合并为一个扁平数组。

完整实现代码 #

WITH Sequences AS (
  SELECT [0, 1, 3] AS some_numbers UNION ALL
  SELECT [2, 4, 8] UNION ALL
  SELECT [0, 5] UNION ALL
  SELECT [2, 16] UNION ALL
  SELECT [0, 7]
),
numbered_rows AS (
  SELECT
    some_numbers[ORDINAL(1)] AS grp,
    some_numbers[ORDINAL(2)] AS sub_grp,
    some_numbers,
    -- 按grp分组，sub_grp排序分配行号
    ROW_NUMBER() OVER (PARTITION BY grp ORDER BY sub_grp) AS row_num
  FROM Sequences
),
prev_arrays_collected AS (
  SELECT
    grp,
    sub_grp,
    some_numbers,
    -- 收集当前行之前的所有数组（不含当前行）
    ARRAY_AGG(some_numbers) OVER (
      PARTITION BY grp 
      ORDER BY row_num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS previous_arrays
  FROM numbered_rows
)
SELECT
  grp,
  sub_grp,
  some_numbers,
  -- 将数组的数组扁平化为单个拼接数组
  ARRAY(SELECT elem FROM UNNEST(previous_arrays) arr, UNNEST(arr) elem) AS concatenated_arrays
FROM prev_arrays_collected;

代码说明 #

• numbered_rows CTE：为每个grp分组内的行按sub_grp升序分配行号，确保后续能准确筛选出当前行之前的记录；
• prev_arrays_collected CTE：用ARRAY_AGG窗口函数收集当前行之前的所有some_numbers数组，得到一个数组的数组；
• 最终查询：通过嵌套UNNEST将数组的数组展开为单个元素序列，再重新聚合成扁平数组，实现数组拼接效果。

执行后会得到你预期的输出：

• 每组的第一行（最小sub_grp）对应的concatenated_arrays为空数组；
• 后续行的concatenated_arrays为当前行之前所有同组数组的拼接结果。

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