| USER ID | string_col |
|---|---|
| 100001 | Here |
| 100001 | there |
| 100001 | Apple |
| 200002 | this is |
| 200002 | that is |
| 200002 | Apple |
| 200002 | Cell 4 |
这是我的原始表&；我想使用单词"Apple"作为触发词， for each 用户ID连接上面的所有内容.
下表是我要查找的输出:
| USER ID | string_col | Result |
|---|---|---|
| 100001 | Here | null |
| 100001 | there | null |
| 100001 | Apple | Here There |
| 200002 | this is | null |
| 200002 | that is | null |
| 200002 | Apple | this is that is |
| 200002 | Cell 4 | null |
为了在串联中保持正确的顺序-您应该有额外的列来定义该顺序-这通常是日期、日期时间、时间戳或仅仅是数字等.
因此，下面是针对BigQuery标准SQL的，假设您确实有这样一个名为ts的列
SELECT * EXCEPT(grp),
CASE
WHEN string_col = 'Apple'
THEN STRING_AGG(string_col, ' ') OVER win
END AS Result
FROM (
SELECT *,
COUNTIF(string_col = 'Apple') OVER win AS grp
FROM your_table
WINDOW win AS (PARTITION BY user_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
)
WINDOW win AS (PARTITION BY user_id, grp ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
如果应用于您的问题中的样本数据-输出为
