在SQL Server中查询机器最长开机时长的最优SQL语句

假设你的数据表名为MachineStatus，我们可以通过分组连续开机状态结合窗口函数来高效计算最长开机时长，以下是最优SQL语句：

DECLARE @StartDate DATETIME = '2024-01-01 00:00:00'; -- 指定时间范围开始
DECLARE @EndDate DATETIME = '2024-01-31 23:59:59';   -- 指定时间范围结束

WITH StatusGroups AS (
    SELECT
        [DateTime],
        [Machine On],
        -- 为连续的相同状态生成唯一分组ID
        SUM(CASE 
                WHEN LAG([Machine On]) OVER (ORDER BY [DateTime]) = [Machine On] THEN 0 
                ELSE 1 
            END) OVER (ORDER BY [DateTime]) AS StatusGroupId
    FROM MachineStatus
    WHERE [DateTime] BETWEEN @StartDate AND @EndDate
),
OnPeriods AS (
    SELECT
        StatusGroupId,
        MIN([DateTime]) AS StartTime,
        -- 取当前开机组结束后的下一个状态时间，若为最后一组则用时间范围结束时间
        COALESCE(
            LEAD(MIN([DateTime])) OVER (ORDER BY MIN([DateTime])),
            @EndDate
        ) AS EndTime
    FROM StatusGroups
    WHERE [Machine On] = 1 -- 仅筛选开机状态的分组
    GROUP BY StatusGroupId
)
SELECT
    MAX(DATEDIFF(MINUTE, StartTime, EndTime)) AS MaxOnDurationMinutes
FROM OnPeriods;

逻辑说明 #

1. StatusGroups CTE：
   • 使用LAG函数对比当前记录与上一条的开机状态，通过累加生成分组ID，确保连续开机的记录被归为同一组。
2. OnPeriods CTE：
   • 筛选出开机状态的分组，取每组的最早时间作为开机起始点；
   • 使用LEAD函数获取下一个状态的起始时间作为当前开机时段的结束点，若当前是时间范围内最后一个开机组，则用指定的@EndDate作为结束时间。
3. 最终计算：
   • 用DATEDIFF(MINUTE, ...)计算每个开机时段的分钟数，取最大值即为最长开机时长。

注意事项 #

• 替换@StartDate和@EndDate为你实际需要查询的时间范围；
• 若[Machine On]字段是BIT类型，SQL Server会自动处理与整数的比较逻辑，无需额外转换；
• 该语句仅扫描表一次（通过窗口函数），性能优于多次子查询，适合大数据量场景。

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