使用窗口函数实现按序列累积的array_agg()计算

示例数据与期望输出 #

原始表t1 #

WITH t1 AS (
  SELECT 1 AS poss, 1 AS sequ, 'jon' AS name UNION ALL
  SELECT 1 AS poss, 2 AS sequ, 'nick' AS name UNION ALL
  SELECT 1 AS poss, 3 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 4 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 5 AS sequ, 'tom' AS name UNION ALL
  SELECT 2 AS poss, 1 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 2 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 3 AS sequ, 'bil' AS name UNION ALL
  SELECT 2 AS poss, 4 AS sequ, 'kev' AS name UNION ALL
  SELECT 2 AS poss, 5 AS sequ, NULL AS name
)
SELECT * FROM t1;

期望输出表output #

WITH output AS (
  SELECT 1 AS poss, 1 AS sequ, 'jon' AS name, ['jon'] AS arrayCol UNION ALL
  SELECT 1 AS poss, 2 AS sequ, 'nick' AS name, ['jon', 'nick'] AS arrayCol UNION ALL
  SELECT 1 AS poss, 3 AS sequ, NULL AS name, ['jon', 'nick'] AS arrayCol UNION ALL
  SELECT 1 AS poss, 4 AS sequ, NULL AS name, ['jon', 'nick'] AS arrayCol UNION ALL
  SELECT 1 AS poss, 5 AS sequ, 'tom' AS name, ['jon', 'nick', 'tom'] AS arrayCol UNION ALL
  SELECT 2 AS poss, 1 AS sequ, NULL AS name, [] AS arrayCol UNION ALL
  SELECT 2 AS poss, 2 AS sequ, NULL AS name, [] AS arrayCol UNION ALL
  SELECT 2 AS poss, 3 AS sequ, 'bil' AS name, ['bil'] AS arrayCol UNION ALL
  SELECT 2 AS poss, 4 AS sequ, 'kev' AS name, ['bil', 'kev'] AS arrayCol UNION ALL
  SELECT 2 AS poss, 5 AS sequ, NULL AS name, ['bil', 'kev'] AS arrayCol
)
SELECT * FROM output;

需求说明 #

在每个poss分组内，以sequ作为排序依据，对name列执行累积式数组聚合：仅收集当前行及之前所有非空的name值形成数组，name为null时数组保留上一次的状态，同时要求保留原表的所有行（不能通过GROUP BY合并行）。

由于array_agg()是聚合函数，直接使用会合并分组内的行，无法满足需求，可通过以下几种方案解决：

方案1：利用窗口函数+数组拼接（适用于BigQuery等支持数组加法的方言） #

利用窗口函数的累积计算特性，结合条件判断仅在name非空时拼接数组，否则保留原数组：

WITH t1 AS (
  SELECT 1 AS poss, 1 AS sequ, 'jon' AS name UNION ALL
  SELECT 1 AS poss, 2 AS sequ, 'nick' AS name UNION ALL
  SELECT 1 AS poss, 3 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 4 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 5 AS sequ, 'tom' AS name UNION ALL
  SELECT 2 AS poss, 1 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 2 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 3 AS sequ, 'bil' AS name UNION ALL
  SELECT 2 AS poss, 4 AS sequ, 'kev' AS name UNION ALL
  SELECT 2 AS poss, 5 AS sequ, NULL AS name
)
SELECT
  poss,
  sequ,
  name,
  SUM(CASE WHEN name IS NOT NULL THEN [name] ELSE [] END) OVER (
    PARTITION BY poss 
    ORDER BY sequ 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS arrayCol
FROM t1
ORDER BY poss, sequ;

原理说明 #

• CASE WHEN name IS NOT NULL THEN [name] ELSE [] END：将非空name转为单元素数组，空值转为空数组
• SUM(...) OVER (...)：在poss分组内按sequ累积求和，BigQuery中数组的SUM操作会自动拼接数组，从而实现累积聚合的效果

方案2：关联子查询（通用SQL方案，适用于绝大多数方言） #

通过关联子查询，针对每一行查询同分组内sequ小于等于当前行的所有非空name，再聚合为数组：

WITH t1 AS (
  SELECT 1 AS poss, 1 AS sequ, 'jon' AS name UNION ALL
  SELECT 1 AS poss, 2 AS sequ, 'nick' AS name UNION ALL
  SELECT 1 AS poss, 3 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 4 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 5 AS sequ, 'tom' AS name UNION ALL
  SELECT 2 AS poss, 1 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 2 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 3 AS sequ, 'bil' AS name UNION ALL
  SELECT 2 AS poss, 4 AS sequ, 'kev' AS name UNION ALL
  SELECT 2 AS poss, 5 AS sequ, NULL AS name
)
SELECT
  t1.poss,
  t1.sequ,
  t1.name,
  ARRAY(
    SELECT t2.name
    FROM t1 t2
    WHERE t2.poss = t1.poss
      AND t2.sequ <= t1.sequ
      AND t2.name IS NOT NULL
  ) AS arrayCol
FROM t1
ORDER BY poss, sequ;

原理说明 #

• 子查询ARRAY(...)：针对当前行的poss和sequ，筛选出同分组内顺序在前的所有非空name，并转为数组
• 该方案无需依赖特定SQL方言的窗口函数特性，兼容性极强

方案3：窗口函数+数组去空（适用于PostgreSQL等支持array_remove的方言） #

先通过窗口化的array_agg累积所有name（包括null），再用array_remove移除数组中的null值：

WITH t1 AS (
  SELECT 1 AS poss, 1 AS sequ, 'jon' AS name UNION ALL
  SELECT 1 AS poss, 2 AS sequ, 'nick' AS name UNION ALL
  SELECT 1 AS poss, 3 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 4 AS sequ, NULL AS name UNION ALL
  SELECT 1 AS poss, 5 AS sequ, 'tom' AS name UNION ALL
  SELECT 2 AS poss, 1 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 2 AS sequ, NULL AS name UNION ALL
  SELECT 2 AS poss, 3 AS sequ, 'bil' AS name UNION ALL
  SELECT 2 AS poss, 4 AS sequ, 'kev' AS name UNION ALL
  SELECT 2 AS poss, 5 AS sequ, NULL AS name
)
SELECT
  poss,
  sequ,
  name,
  array_remove(
    array_agg(name) OVER (PARTITION BY poss ORDER BY sequ),
    NULL
  ) AS arrayCol
FROM t1
ORDER BY poss, sequ;

原理说明 #

• array_agg(name) OVER (...)：在poss分组内按sequ累积聚合所有name（包括null）
• array_remove(..., NULL)：移除数组中的所有null值，得到仅包含非空name的累积数组

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