如何忽略事件表中重复状态行并仅保留首次出现记录？

要剔除连续重复的OPEN（或其他相同状态）记录，仅保留首次出现的条目，用间隙与孤岛的思路就能实现，核心是先把连续相同状态的行归为一组，再每组只取第一条记录。

具体实现步骤 #

• 标记分组起始点：用LAG()窗口函数对比当前行与上一行的状态，当状态变化时标记为新组的起始
• 生成组ID：将起始点标记累加，得到每个连续状态组的唯一ID
• 提取每组第一条记录：按组ID分组，取每组中时间最早的记录，或者用行号筛选第一条

完整SQL示例 #

方法一：分组取最小时间

SELECT 
    MIN(event_time) AS event_time,
    status
FROM (
    SELECT 
        event_time,
        status,
        -- 累加起始标记生成组ID
        SUM(CASE WHEN LAG(status) OVER (ORDER BY event_time) = status THEN 0 ELSE 1 END) 
        OVER (ORDER BY event_time) AS group_id
    FROM your_table
) t
GROUP BY group_id, status
ORDER BY event_time;

方法二：用行号筛选第一条

SELECT event_time, status
FROM (
    SELECT 
        event_time,
        status,
        -- 每个组内按时间排序，行号为1的就是第一条
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY event_time) AS rn
    FROM (
        SELECT 
            event_time,
            status,
            SUM(CASE WHEN LAG(status) OVER (ORDER BY event_time) = status THEN 0 ELSE 1 END) 
            OVER (ORDER BY event_time) AS group_id
        FROM your_table
    ) t1
) t2
WHERE rn = 1
ORDER BY event_time;

逻辑说明 #

• LAG(status) OVER (ORDER BY event_time)：获取当前行的上一行状态，用来判断状态是否连续
• 起始标记CASE语句：当状态与上一行不同时，标记为1（新组开始），否则为0
• 累加起始标记得到group_id：连续相同状态的行会被分到同一个组
• 最后通过分组取最小时间或行号筛选，就能得到每个连续状态组的首次出现记录

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