BigQuery教程：如何删除会话中最后一次Action后的行

现有如下数据表：

需要实现的逻辑是：对每个Session ID，删除最后一次Action值为1的行之后的所有记录，最终期望结果如下：

实现方案 #

可以通过窗口函数先计算每个会话中最后一次Action=1对应的位置，再筛选保留符合条件的记录，或者直接删除不符合的记录。

方式一：查询保留目标记录 #

WITH session_last_action AS (
    SELECT 
        "Session ID",
        -- 取每个会话中最后一次Action=1的Position
        MAX(CASE WHEN Action = 1 THEN Position END) AS last_action_1_pos
    FROM your_table
    GROUP BY "Session ID"
)
SELECT t.*
FROM your_table t
JOIN session_last_action s 
    ON t."Session ID" = s."Session ID"
WHERE t.Position <= s.last_action_1_pos;

方式二：直接删除不需要的记录 #

如果需要直接修改原表，执行以下删除语句：

WITH session_last_action AS (
    SELECT 
        "Session ID",
        MAX(CASE WHEN Action = 1 THEN Position END) AS last_action_1_pos
    FROM your_table
    GROUP BY "Session ID"
)
DELETE FROM your_table t
USING session_last_action s
WHERE t."Session ID" = s."Session ID"
AND t.Position > s.last_action_1_pos;

逻辑说明 #

1. 先用子查询/CTE计算每个Session ID中，最后一次Action=1对应的Position值（利用Position递增的特性，MAX函数可以直接拿到最后一次出现的位置）；
2. 关联原表后，筛选出Position不超过该值的记录，或者删除超过该值的记录，即可得到目标结果。

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