SQL Server中统计机器超过5分钟的总停机时长

核心实现方案 #

要计算机器的总停机时长（定义为连续生产记录间隔超过5分钟的时间段总和），可以结合**窗口函数LEAD()**和DATEDIFF()来实现，具体步骤如下：

• 用LEAD()获取每条生产记录的下一条记录时间
• 计算两条记录的时间差，筛选出超过5分钟的间隔
• 累加符合条件的间隔时长，得到总停机时间

完整SQL代码 #

-- 请替换以下占位符为你的实际字段/值：
-- MachineId：机器标识字段名
-- '你的机器ID'：目标机器的具体标识
SELECT 
    SUM(DATEDIFF(MINUTE, curr_dt, next_dt)) AS TotalDowntimeMinutes
FROM (
    SELECT 
        DateTime AS curr_dt,
        -- 按时间升序，获取下一条生产记录的时间
        LEAD(DateTime) OVER (ORDER BY DateTime ASC) AS next_dt
    FROM AutomatedEquipment
    -- 过滤目标机器的生产记录
    WHERE MachineId = '你的机器ID'
    -- 可选：添加时间范围过滤，比如统计某月的停机时长
    -- AND DateTime BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
) AS IntervalData
-- 只统计间隔超过5分钟的停机时段
WHERE DATEDIFF(MINUTE, curr_dt, next_dt) > 5;

代码说明 #

• LEAD(DateTime) OVER (ORDER BY DateTime ASC)：窗口函数会按时间顺序，为每条记录匹配到下一条生产记录的时间，这是实现"行与下一行对比"的关键。
• DATEDIFF(MINUTE, curr_dt, next_dt)：计算两条记录的时间差（单位：分钟），完全适配你对时长统计的需求。如果需要转换为小时/秒，只需调整DATEDIFF的第一个参数（如HOUR/SECOND）。
• SUM()：累加所有超过5分钟的间隔时长，得到总停机分钟数。

修正你原有代码的问题 #

你原来的WHERE DateTime = ''存在两处错误：

• DateTime是日期时间类型，不能与空字符串做等值判断
• 必须添加机器标识过滤条件，确保只统计目标机器的生产记录

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