PostgreSQL根据action列交替值合并时间生成设备OFF状态区间

DevOps 小满

2026-8-26

纯SQL实现设备OFF状态区间合并

完全可以直接用标准SQL实现需求，不需要移植PL/pgSQL或者在服务端额外写处理逻辑，核心通过窗口函数就能一步解决连续重复动作消重、区间生成的问题，也不需要单独做重叠区间合并。

实现逻辑

核心思路是给连续相同状态的记录分配同一个分组ID，再按分组聚合取起止时间：

按时间升序排列所有事件，用LAG()取每条记录的上一个动作
遇到动作和上一条不一致时，判定为新状态组的起点，累计计数生成唯一分组ID，连续相同动作的记录会共享同一个分组ID
按分组聚合，取组内最早时间作为区间起点，取下一个状态组的最早时间作为当前区间终点
最后过滤出状态为OFF的闭合区间即可

可直接运行的SQL代码

假设事件表名为device_events，时间字段为event_time，动作字段为action：

WITH marked_events AS (
    SELECT
        event_time,
        action,
        SUM(
            -- 动作和上一条一致就不新增分组，不一致则分组号+1
            CASE WHEN LAG(action) OVER (ORDER BY event_time) = action THEN 0 ELSE 1 END
        ) OVER (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM device_events
),
state_ranges AS (
    SELECT
        group_id,
        action AS state,
        MIN(event_time) AS range_start,
        LEAD(MIN(event_time)) OVER (ORDER BY group_id) AS range_end
    FROM marked_events
    GROUP BY group_id, action
)
SELECT
    CONCAT(range_start, ' - ', range_end) AS range,
    state
FROM state_ranges
WHERE state = 'OFF' AND range_end IS NOT NULL;