如何按ID分区筛选start_event及后续的事件数据？

我正在尝试检测用户的首次会话，现有一张大体积的事件数据表，时间戳格式为1970-01-20 06:57:25.583738 UTC。需要按ID分区，保留每个分区中start_event所在行及之后的所有记录，丢弃该事件之前的行。

现有数据示例 #

期望输出 #

已编写的SQL（未完成过滤） #

SELECT ID, event_server_date , Event,
row_number() over(partition by ID ORDER BY event_server_date ASC) AS Row_number

FROM `my_table` 

ORDER BY event_server_date ASC

注：接触SQL仅两个月，对复杂概念不太熟悉。

解决方案 #

可以通过两步实现过滤需求：

1. 给每个ID分区，标记出该ID下最早出现start_event的时间戳
2. 筛选出当前记录时间戳大于等于该最早时间戳的行

完整SQL语句

WITH marked_data AS (
    SELECT 
        ID, 
        event_server_date, 
        Event,
        row_number() over(partition by ID ORDER BY event_server_date ASC) AS Row_number,
        -- 提取当前ID下首次start_event的时间戳
        MIN(CASE WHEN Event = 'start_event' THEN event_server_date END) 
            OVER(PARTITION BY ID) AS first_start_time
    FROM `my_table`
)
SELECT 
    ID, 
    event_server_date AS Timestamp, 
    Event,
    Row_number
FROM marked_data
-- 保留首次start_event及之后的记录
WHERE event_server_date >= first_start_time
ORDER BY ID, event_server_date ASC;

简化版（如果原表已有Row_number字段）

WITH marked_data AS (
    SELECT 
        *,
        MIN(CASE WHEN Event = 'start_event' THEN event_server_date END) 
            OVER(PARTITION BY ID) AS first_start_time
    FROM `my_table`
)
SELECT 
    ID, 
    event_server_date AS Timestamp, 
    Event,
    Row_number
FROM marked_data
WHERE event_server_date >= first_start_time
ORDER BY ID, event_server_date ASC;

逻辑说明

• marked_data临时结果集中，用MIN() OVER(PARTITION BY ID)窗口函数，针对每个ID捕获最早的start_event时间戳
• 外层查询通过WHERE条件过滤掉该时间戳之前的所有记录
• 最后按ID和时间戳排序，保证结果顺序符合预期

内容的提问来源于Stack Exchange，提问作者Boran Göksel