SQL中统计UpTime从1转0的次数及0状态持续时长的实现问题

现有设备状态表结构及数据如下：

需要统计UpTime从1切换至0的次数，以及每次状态0持续到下一次UpTime切换回1的时长，预期结果如下：

此前尝试用CROSS APPLY但无法隔离每个周期的第一个UpTime=0事件，现寻求解决方案。

可以通过窗口函数标记状态切换点，再匹配对应的恢复时间来实现：

完整SQL代码 #

WITH StateChanges AS (
    SELECT 
        EventTime,
        Device,
        UpTime,
        -- 标记当前记录是否为状态切换点（与上一条UpTime不同）
        CASE 
            WHEN LAG(UpTime) OVER (PARTITION BY Device ORDER BY EventTime) != UpTime 
            THEN 1 
            ELSE 0 
        END AS IsSwitchPoint
    FROM YourTableName
),
SwitchRecords AS (
    SELECT 
        EventTime,
        Device,
        UpTime,
        -- 为每个设备的切换点按时间顺序编号
        ROW_NUMBER() OVER (PARTITION BY Device ORDER BY EventTime) AS SwitchSeq
    FROM StateChanges
    WHERE IsSwitchPoint = 1
)
SELECT 
    s_down.Device,
    s_down.EventTime AS EventTimeDown,
    s_up.EventTime AS EventTimeUp,
    -- 计算持续秒数（四舍五入取整）
    DATEDIFF(SECOND, s_down.EventTime, s_up.EventTime) AS TotalPeriodSecond
FROM SwitchRecords s_down
JOIN SwitchRecords s_up 
    ON s_down.Device = s_up.Device
    AND s_down.SwitchSeq + 1 = s_up.SwitchSeq
WHERE s_down.UpTime = 0;

逻辑说明 #

1. LAG()窗口函数：获取同一设备的上一条记录的UpTime值，判断当前记录是否是状态切换点，过滤掉连续相同状态的冗余记录。
2. ROW_NUMBER()：为每个设备的切换点按时间顺序编号，确保后续能精准配对停机（UpTime=0）和对应的恢复（UpTime=1）记录。
3. 自连接配对：通过切换序号+1的关联条件，将每个停机记录匹配到下一次恢复的记录，直接计算每次停机的持续时长。

内容的提问来源于stack exchange，提问作者Andrea Da Como