如何编写SQL查询获取时序数据表中连续事件的开始与结束时间

解法思路 #

这是典型的时序数据「孤岛（islands）」计算场景，核心逻辑是给连续的相同事件分配同一个分组标识，再按分组聚合即可：

• 按自增ID（或时间戳）排序，用窗口函数判断当前行事件是否和上一行一致
• 对事件发生变化的行做标记累加，生成连续事件的唯一分组ID
• 按事件和分组ID聚合，取时间最小值为开始时间，最大值为结束时间

通用SQL实现（支持MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库） #

WITH event_mark AS (
    -- 标记事件变化：当前行和上一行事件不同则记1，相同记0
    SELECT 
        Event,
        `Timestamp`,
        SUM(CASE WHEN pre_event != Event THEN 1 ELSE 0 END) OVER(ORDER BY ID) AS group_id
    FROM (
        -- 获取上一行的事件值
        SELECT 
            ID,
            Event,
            `Timestamp`,
            LAG(Event, 1, '') OVER(ORDER BY ID) AS pre_event
        FROM event_log -- 替换为你实际的表名
    ) t1
)
SELECT 
    Event,
    MIN(`Timestamp`) AS `Start Time`,
    MAX(`Timestamp`) AS `End Time`
FROM event_mark
GROUP BY Event, group_id
ORDER BY `Start Time`;

老版本MySQL（不支持窗口函数）适配写法 #

用用户变量实现分组标记：

SELECT 
    Event,
    MIN(`Timestamp`) AS `Start Time`,
    MAX(`Timestamp`) AS `End Time`
FROM (
    SELECT 
        Event,
        `Timestamp`,
        @group_id := IF(@pre_event = Event, @group_id, @group_id + 1) AS group_id,
        @pre_event := Event
    FROM event_log, (SELECT @pre_event := '', @group_id := 0) init
    ORDER BY ID
) t
GROUP BY Event, group_id
ORDER BY `Start Time`;

注意事项 #

• 代码中Timestamp是SQL关键字，用反引号包裹避免语法报错，可根据你实际的字段名调整
• 即使ID存在断号（比如示例中的ID8缺失），上述逻辑也能正常统计，不会影响结果

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