基于窗口函数的SQL Server状态变化查询：如何统计状态块内累计时长

看起来你正在用这段SQL追踪每台机器在不同日期下的状态切换轨迹和耗时统计，我把代码整理成更易读的格式，同时拆解下核心逻辑：

SELECT 
    ks.CalendarId,
    ks.machineid,
    ks.machinestateid,
    LEAD(ks.machinestateid) OVER (
        PARTITION BY ks.machineid, ks.calendarid 
        ORDER BY ks.machinefmstatetime DESC
    ) prevstateid,
    ks.machinestatehistoryid,
    ks.machinefmstatetime,
    ks.StateTimeInSeconds,
    SUM(ks.statetimeinseconds) OVER (
        PARTITION BY ks.machineid, ks.calendarid, ks.machinestateid 
        ORDER BY ks.machinefmstatetime
    ) StateTimeSecTotal
FROM 
    -- 这里替换为你的实际表名，比如 ks 对应的源表
    your_source_table ks
-- 可按需添加WHERE过滤条件

核心逻辑拆解 #

• LEAD()窗口函数：针对单台机器单日的数据，按状态发生时间倒序排列，获取当前状态的上一个前置状态，帮你清晰追踪状态切换的路径。
• SUM()窗口函数：按机器、日期、状态维度分组，累计计算该机器当天同一状态下的总耗时，无需额外分组聚合就能直接得到单状态的总占用时长。
• 分区规则：通过machineid+calendarid锁定单台机器单日的统计范围，避免跨机器或跨日期的数据干扰，保证统计精度。

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