PostgreSQL中按ID分组配对开关状态事件，生成启停时间对

这是个很典型的状态事件配对需求，咱们可以通过窗口函数分组合并连续状态，再关联配对的方式来实现，直接上解决方案：

首先，我们需要先把连续的相同状态合并成一个组（比如连续的on或者off只保留第一个出现的时间），然后再把每个on组和紧随其后的off组配对，这样就能得到准确的启停时间对了。

完整SQL代码 #

WITH consecutive_groups AS (
    SELECT 
        id,
        status,
        created,
        -- 生成连续同状态的分组ID：前一个状态和当前不同时，分组ID+1
        SUM(CASE WHEN prev_status = status THEN 0 ELSE 1 END) OVER (PARTITION BY id ORDER BY created) AS group_id
    FROM (
        SELECT 
            id,
            status,
            created,
            -- 获取当前记录的前一个状态
            LAG(status) OVER (PARTITION BY id ORDER BY created) AS prev_status
        FROM test
    ) t
),
grouped_events AS (
    SELECT 
        id,
        status,
        MIN(created) AS event_time, -- 连续同状态取最早的时间作为有效时间
        group_id
    FROM consecutive_groups
    GROUP BY id, status, group_id
)
SELECT 
    g1.id,
    g1.event_time AS switch_on,
    g2.event_time AS switch_off
FROM grouped_events g1
JOIN grouped_events g2 
    ON g1.id = g2.id 
    AND g1.group_id + 1 = g2.group_id 
    AND g1.status = 'on' 
    AND g2.status = 'off'
ORDER BY g1.id, g1.event_time;

代码逻辑拆解 #

• 生成连续状态分组：先用LAG()窗口函数拿到每条记录的前一个状态，然后通过累加判断（前状态和当前状态不同时加1），给连续的相同状态分配同一个分组ID，这样就能把连续的on或off归为一组。
• 合并连续状态：对每个分组取最早的时间，毕竟连续的相同状态里，只有第一个出现的时间是有效的（比如连续触发on，我们只需要第一次启动的时间）。
• 配对启停组：把状态为on的分组和同ID下、分组ID刚好大1的off分组关联起来，这样每个启动事件就对应了紧接着的停止事件，完美生成启停时间对。

边界情况说明 #

• 如果某个ID只有on没有对应的off，这条记录不会出现在结果里（因为找不到匹配的停止事件）
• 如果某个ID只有off没有on，这些停止事件也会被忽略（没有对应的启动事件，无法形成有效配对）
• 连续的相同状态会自动合并，不会产生多余的配对（比如多次触发on只会保留第一个启动时间）

备注：内容来源于stack exchange，提问作者j1n3l0