基于T-SQL捕获项目首个On与对应Off状态变更的起止时间

嘿，这个需求其实是经典状态时间配对的变体，核心是要忽略那些连续重复的状态，只关注真正的状态变更，然后给每个On找到紧随其后的第一个Off。咱们直接上干货：

步骤1：先过滤出真正的状态变更记录 #

首先，我们需要把连续重复的状态去掉——比如连续的多个On或者多个Off，只保留每个状态变更的第一条记录。这一步可以用LAG()窗口函数来实现：

WITH FilteredEvents AS (
    SELECT 
        Item,
        EventDate,
        Event,
        -- 标记当前记录是否是状态变更（和上一条记录的状态不同）
        CASE WHEN LAG(Event) OVER (PARTITION BY Item ORDER BY EventDate) != Event THEN 1 ELSE 0 END AS IsStateChange
    FROM YourTableName
),
-- 只保留状态变更的记录，以及每个Item的第一条记录（哪怕它是初始状态）
StateChanges AS (
    SELECT 
        Item,
        EventDate,
        Event,
        -- 给每个状态变更记录按Item分组排序，方便后续配对
        ROW_NUMBER() OVER (PARTITION BY Item ORDER BY EventDate) AS ChangeRowNum
    FROM FilteredEvents
    WHERE IsStateChange = 1 
        OR LAG(Event) OVER (PARTITION BY Item ORDER BY EventDate) IS NULL -- 保留每个Item的第一条记录
)

步骤2：配对On和紧随其后的Off #

接下来，我们可以把状态变更记录中的On和它之后的第一个Off配对。这里用LEAD()窗口函数来获取每个On对应的下一个Off的时间：

SELECT 
    sc.Item,
    sc.EventDate AS StartTime,
    -- 获取当前On之后的第一个Off的时间
    LEAD(sc.EventDate) OVER (PARTITION BY sc.Item ORDER BY sc.EventDate) AS EndTime,
    -- 计算运行时长（如果有EndTime的话）
    DATEDIFF(SECOND, sc.EventDate, LEAD(sc.EventDate) OVER (PARTITION BY sc.Item ORDER BY sc.EventDate)) AS RunDurationSeconds
FROM StateChanges sc
WHERE sc.Event = 'On' -- 只关注On状态的变更记录

思路拆解 #

1. 过滤连续重复状态：用LAG()对比当前记录和上一条的状态，只有当状态变化时才保留，这样我们就得到了每个Item的状态变更序列（比如：On → Off → On → Off...），而不是一堆重复的状态记录。
2. 配对On和Off：在状态变更序列中，每个On的下一个记录必然是Off（因为已经过滤了连续重复），所以用LEAD()直接取当前On的下一条记录的时间，就是对应的结束时间。
3. 处理无匹配的On：如果某个Item最后一个状态是On，那么LEAD()会返回NULL，这时候RunDurationSeconds也会是NULL，你可以根据需求用ISNULL()标记为“未结束”或者其他自定义值。

测试示例 #

假设你的数据源YourTableName有如下数据：

A 2011-10-03 00:01:00 On
A 2011-10-03 00:02:00 On
A 2011-10-03 00:05:00 Off
A 2011-10-03 00:06:00 Off
B 2011-10-03 00:03:00 On
B 2011-10-03 00:07:00 On
B 2011-10-03 00:09:00 Off

运行上面的SQL后，结果会是：

完美忽略了连续的重复状态，只取了第一个On和紧随的第一个Off！

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