如何在Google BigQuery中按用户分组，基于触发词拼接前置行数据？

原始数据表 #

需求说明 #

以Apple为触发词，每个USER ID下：

• 当string_col为Apple时，Result字段拼接该行上方所有同用户的string_col内容
• 其余行的Result为null

解决方案 #

方法一：SQL实现（数据库场景） #

利用窗口函数给每个用户的行编号，通过自连接拼接符合条件的字符串：

WITH ranked_data AS (
    SELECT 
        `USER ID`,
        string_col,
        ROW_NUMBER() OVER (PARTITION BY `USER ID` ORDER BY (SELECT NULL)) AS row_num
    FROM your_table
)
SELECT 
    rd.`USER ID`,
    rd.string_col,
    CASE 
        WHEN rd.string_col = 'Apple' THEN 
            GROUP_CONCAT(rd_prev.string_col ORDER BY rd_prev.row_num SEPARATOR ' ')
        ELSE NULL
    END AS Result
FROM ranked_data rd
LEFT JOIN ranked_data rd_prev 
    ON rd.`USER ID` = rd_prev.`USER ID` 
    AND rd_prev.row_num < rd.row_num
GROUP BY rd.`USER ID`, rd.string_col, rd.row_num
ORDER BY rd.`USER ID`, rd.row_num;

方法二：Python Pandas实现（数据处理场景） #

通过分组遍历累积字符串，遇到触发词时完成拼接并重置累积：

import pandas as pd

# 加载原始数据（实际场景可替换为读取文件逻辑）
data = {
    'USER ID': [100001, 100001, 100001, 200002, 200002, 200002, 200002],
    'string_col': ['Here', 'there', 'Apple', 'this is', 'that is', 'Apple', 'Cell 4']
}
df = pd.DataFrame(data)

def process_group(group):
    acc = []
    result = []
    for s in group['string_col']:
        if s == 'Apple':
            result.append(' '.join(acc))
            acc = []
        else:
            result.append(None)
            acc.append(s)
    group['Result'] = result
    return group

# 分组处理并输出结果
df = df.groupby('USER ID', group_keys=False).apply(process_group)
print(df)

最终输出结果 #

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