如何在BigQuery中用Row_number函数拼接多行到单单元格（无需多JOIN）

问题场景 #

现有包含ID、event_name、timestamp字段的表，需要拼接每个ID的最近5个事件（按时间倒序）。原查询使用多个WITH子句和JOIN实现，在BigQuery中计算耗时过长，需要优化方案。

表结构示例 #

原查询代码 #

WITH a AS ( 
  SELECT id, timestamp, event_name, row_number() over(partition by ID ORDER BY timestamp DESC) as row_n
  FROM my_table
),
b AS (
  SELECT id, timestamp, event_name, row_n
  FROM a
  WHERE row_n <= 5
),
e1 AS(
    SELECT ID, event_name AS ev1  -- 修正原代码误写的timestamp，匹配期望输出的event_name拼接
    FROM b
    WHERE row_n = 1
),
e2 AS(
    SELECT ID, event_name AS ev2
    FROM b
    WHERE row_n = 2
),
e3 AS(
    SELECT ID, event_name AS ev3
    FROM b
    WHERE row_n = 3
),
e4 AS(
    SELECT ID, event_name AS ev4
    FROM b
    WHERE row_n = 4
),
e5 AS(
    SELECT ID, event_name AS ev5
    FROM b
    WHERE row_n = 5
),
concat_prep AS(
  SELECT b.ID, ev1,ev2,ev3,ev4,ev5
  FROM b
  LEFT JOIN e1 
  ON b.ID = e1.ID
  LEFT JOIN e2
  ON e1.ID = e2.ID
  LEFT JOIN e3
  ON e2.ID = e3.ID
  LEFT JOIN e4
  ON e3.ID = e4.ID
  LEFT JOIN e5
  ON e4.ID= e5.ID
)
SELECT ID, concat(ev1,',',ev2,',',ev3,',',ev4,',',ev5) as concatt
FROM concat_prep
GROUP BY ID ,concat(ev1,',',ev2,',',ev3,',',ev4,',',ev5)

期望输出 #

优化方案 #

原查询的核心问题是通过多次JOIN拆分再合并，导致数据冗余和计算开销增大。BigQuery提供了更高效的聚合函数STRING_AGG，结合窗口函数筛选最近5条数据，可大幅简化查询并提升性能。

优化后代码 #

SELECT
  ID,
  STRING_AGG(event_name, ',' ORDER BY timestamp DESC) AS concat
FROM (
  SELECT
    ID,
    event_name,
    timestamp,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY timestamp DESC) AS row_n
  FROM my_table
  -- 保留你的日期过滤条件，例如：WHERE DATE(timestamp) = '2022-10-21'
)
WHERE row_n <= 5
GROUP BY ID

优化说明 #

1. 简化数据处理链路：仅用一层子查询筛选每个ID的最近5条数据，避免原查询中多个WITH子句和JOIN带来的额外计算开销。
2. 直接聚合拼接：STRING_AGG支持在聚合时指定排序规则，直接按时间倒序拼接event_name，无需拆分后手动拼接。
3. 避免数据冗余：原查询中concat_prep阶段会因JOIN产生重复数据，最终需要GROUP BY去重；优化后的查询仅处理必要数据，无冗余计算。

如果该查询是更大查询的一部分，可将子查询逻辑直接嵌入主查询对应位置，保持整体逻辑简洁。

内容的提问来源于stack exchange，提问作者Boran Göksel