如何在Google BigQuery中基于触发词条件拼接指定行数的列数据？

原始数据 #

期望输出 #

要求以Apple为触发词，每个USER ID下触发词所在行的结果为该行前2行的string_col内容拼接，非触发词行结果为null：

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

利用窗口函数标记行号，结合聚合函数筛选触发词前N行内容：

WITH user_ranked AS (
    SELECT 
        TimeStamp,
        "USER ID" AS user_id,
        string_col,
        ROW_NUMBER() OVER (PARTITION BY "USER ID" ORDER BY TimeStamp) AS row_num
    FROM your_table
)
SELECT
    ur.TimeStamp,
    ur.user_id AS "USER ID",
    ur.string_col,
    CASE 
        WHEN ur.string_col = 'Apple' THEN (
            SELECT STRING_AGG(string_col, ' ')
            FROM user_ranked ur2
            WHERE ur2.user_id = ur.user_id
              AND ur2.row_num BETWEEN ur.row_num - 2 AND ur.row_num - 1
        )
        ELSE NULL
    END AS Result
FROM user_ranked ur
ORDER BY ur.TimeStamp;

方法二：Python Pandas实现（本地数据处理） #

通过分组移位拼接内容，再按触发词条件赋值：

import pandas as pd

# 构造原始数据（实际场景可替换为pd.read_csv等读取操作）
df = pd.DataFrame({
    'TimeStamp': [1,2,3,4,5,6,7,8,9,10],
    'USER ID': [100001,100001,100001,200002,200002,200002,200002,200002,200002,200002],
    'string_col': ['Here','there','Apple','this is','that is','Apple','some','summer','winter','Apple']
})

# 分组后拼接前2行的内容
df['Result'] = df.groupby('USER ID')['string_col'].apply(
    lambda x: x.shift(2) + ' ' + x.shift(1)
)

# 非触发词行设置为null
df.loc[df['string_col'] != 'Apple', 'Result'] = None

# 输出结果
print(df)

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