如何在Google BigQuery中按触发词、用户ID及时间条件拼接列内容

原始数据表 #

需求说明 #

• 仅当string_col为"Apple"时生成Result，其余行Result为NULL
• 针对每个USER ID，Result拼接的是满足以下条件的行的string_col：
  1. 该行TimeStamp小于当前触发行的TimeStamp
  2. 当前触发行TimeStamp与该行TimeStamp的差值小于4（即差值∈(0,4)）
• 拼接内容按TimeStamp升序排列，用空格分隔

期望输出表 #

解决方案（BigQuery SQL） #

WITH original_data AS (
  SELECT * FROM UNNEST([
    STRUCT(1 AS TimeStamp, 100001 AS `USER ID`, 'Here' AS string_col),
    STRUCT(2, 100001, 'there'),
    STRUCT(5, 100001, 'Apple'),
    STRUCT(1, 200002, 'this is'),
    STRUCT(2, 200002, 'that is'),
    STRUCT(3, 200002, 'Apple'),
    STRUCT(4, 200002, 'some'),
    STRUCT(5, 200002, 'summer'),
    STRUCT(6, 200002, 'winter'),
    STRUCT(8, 200002, 'Apple'),
    STRUCT(9, 200002, 'Apple')
  ])
)
SELECT
  o.TimeStamp,
  o.`USER ID`,
  o.string_col,
  CASE
    WHEN o.string_col = 'Apple' THEN STRING_AGG(s.string_col, ' ' ORDER BY s.TimeStamp)
    ELSE NULL
  END AS Result
FROM original_data o
LEFT JOIN original_data s
  ON o.`USER ID` = s.`USER ID`
  AND s.TimeStamp < o.TimeStamp
  AND o.TimeStamp - s.TimeStamp < 4
GROUP BY o.TimeStamp, o.`USER ID`, o.string_col
ORDER BY o.`USER ID`, o.TimeStamp;

逻辑说明 #

1. CTE定义：original_data用UNNEST和STRUCT模拟原始数据集，实际使用时替换为你的目标表名即可。
2. 自连接关联：将表与自身连接，关联条件限定为同一用户、被连接行时间戳早于当前行、时间差值小于4。
3. 条件聚合拼接：仅当当前行是触发词"Apple"时，调用STRING_AGG按时间戳升序拼接符合条件的string_col，非触发行返回NULL。
4. 分组排序：按用户ID和时间戳分组排序，保证输出顺序与预期一致。

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