如何在Presto SQL中移除连续重复的事件序列

我正在使用Presto SQL，想要获取唯一的事件序列，需要移除相邻的重复事件，并且始终保留每组相邻重复事件里最新的那条。

原始数据集 #

期望输出 #

移除10:02的重复B事件，保留最新的10:03的B事件，结果如下：

解决方案 #

用窗口函数给连续重复的事件分组，再取每组最新记录即可，SQL代码如下：

WITH grouped_events AS (
    SELECT 
        id,
        event,
        time,
        -- 给连续相同的event分配同一组ID：当前event和前一条不同时，组ID加1
        SUM(CASE WHEN event = LAG(event) OVER (PARTITION BY id ORDER BY time) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY id ORDER BY time) AS group_id
    FROM your_table_name -- 替换成你的表名
)
SELECT 
    id,
    event,
    MAX(time) AS time
FROM grouped_events
GROUP BY id, event, group_id
ORDER BY id, MAX(time);

代码说明 #

1. 用LAG(event) OVER (PARTITION BY id ORDER BY time)获取同一id下前一条记录的event，对比当前event，不同时就累加1，这样连续相同的event会被分到同一个group_id里。
2. 按id、event、group_id分组，取每组的最大time，也就是该组最新的记录。
3. 最后按id和time排序，得到有序的唯一事件序列。

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