查询SQL Server中Machine_Status表机器连续运行的最长时长

问题分析 #

你的原始SQL语句仅计算了所有记录中任意两行的最大时间差，完全未考虑Running状态的连续性，因此得到的是最早记录（1/10/2023 08:20:10）与最晚记录（1/10/2023 08:29:55）的时间差585秒，这显然不是你需要的连续运行时长。

要解决这个问题，核心是识别连续的Running=1状态组，并计算每个组从开始运行到状态变为停止的时长，最终取最大值。

正确SQL语句 #

方法一：基于分组窗口函数 #

WITH StatusGroups AS (
    SELECT 
        *,
        -- 生成连续状态分组ID：当前状态与前一条不同时，分组ID递增
        SUM(CASE WHEN Running = LAG(Running) OVER (ORDER BY Timestamp) THEN 0 ELSE 1 END) 
        OVER (ORDER BY Timestamp) AS GroupId
    FROM Machine_Status
),
RunPeriods AS (
    SELECT 
        GroupId,
        MIN(Timestamp) AS RunStart,
        -- 获取当前运行组之后第一个停止状态的时间作为运行结束时间
        (SELECT MIN(Timestamp) FROM Machine_Status WHERE Timestamp > MAX(sg.Timestamp) AND Running = 0) AS RunEnd
    FROM StatusGroups sg
    WHERE Running = 1
    GROUP BY GroupId
)
SELECT MAX(DATEDIFF(SECOND, RunStart, RunEnd)) AS MaxRunningDurationSeconds
FROM RunPeriods;

方法二：基于状态变更点 #

WITH StatusTransitions AS (
    SELECT 
        Timestamp,
        Running,
        LAG(Running) OVER (ORDER BY Timestamp) AS PreviousRunning
    FROM Machine_Status
),
RunIntervals AS (
    -- 提取所有从运行到停止的时段
    SELECT 
        (SELECT MAX(Timestamp) FROM Machine_Status WHERE Timestamp < st.Timestamp AND Running = 1) AS RunStart,
        st.Timestamp AS RunEnd
    FROM StatusTransitions st
    WHERE st.Running = 0 AND st.PreviousRunning = 1
    
    -- 可选：处理最后一条记录仍为运行状态的情况
    UNION ALL
    SELECT 
        MAX(Timestamp) AS RunStart,
        GETDATE() AS RunEnd
    FROM Machine_Status
    WHERE Running = 1 
      AND NOT EXISTS (SELECT 1 FROM Machine_Status WHERE Timestamp > MAX(Timestamp))
)
SELECT MAX(DATEDIFF(SECOND, RunStart, RunEnd)) AS MaxRunningDurationSeconds
FROM RunIntervals;

结果验证 #

执行上述SQL后，会得到预期的205秒，对应第8至12行记录的连续运行时段（08:25:30到08:28:55）。

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