SQL Server 2012中计算SQL记录时间差实现机器停机超时检测

• 原窗口函数未按Machine做分区隔离，当前LAG(a_Timestamp) OVER (ORDER BY a_Timestamp, Machine)的计算逻辑会把不同机器的日志混在一起取上一条时间戳，算出的时间差完全不是单台机器的连续日志间隔，结果存在系统性偏差
• 现有逻辑仅计算「两条已落库记录的相邻时间差」，没有覆盖停机检测的核心场景：机器发生故障后不会再产生新的状态记录，仅靠相邻记录的差值永远无法捕获“最后一条日志之后超过400分钟没有新数据”的情况——因为故障之后根本没有下一条记录可以用来计算时间差
• 原有过滤条件的位置靠前，会把有效日志从连续序列中过滤掉，导致时间间隔计算不连续，结果失真

实现思路 #

• 为每台机器做独立的日志序列分区，按时间正序排序，从根上避免跨机器计算的偏差
• 覆盖两类超时触发场景：
  • 相邻两条已落库的日志记录间隔差达到400分钟
  • 单台机器的最后一条落库日志，到当前数据库时间的差值达到400分钟（故障停机的最高发场景）
• 把400分钟的预警阈值抽成独立可调整的变量，后续修改预警规则无需改动核心计算逻辑
• 保留原有业务过滤规则：排除DownCode为0、98的无效记录，排除已发送过预警的记录，避免重复告警

优化后的SQL代码如下：

-- 定义预警阈值：400分钟
DECLARE @AlertThresholdMinutes INT = 400;

WITH MachineLogSequence AS (
    -- 按机器分区、按时间正序排，取每台机器上一条的日志时间
    SELECT
        a_Timestamp,
        Machine,
        DownCode,
        Emailed,
        -- 关键修改：按Machine分区，避免跨机器计算LAG值
        LAG(a_Timestamp) OVER (
            PARTITION BY Machine 
            ORDER BY a_Timestamp
        ) AS PreviousLogTime,
        -- 取每台机器最后一条日志的时间，单独计算到当前的差值
        MAX(a_Timestamp) OVER (PARTITION BY Machine) AS LastLogTime
    FROM dbo.TireroomDownTime
    -- 先把无效日志过滤掉，保证计算序列干净
    WHERE DownCode <> 0 
      AND DownCode <> 98
)
SELECT
    -- 计算两类超时的时长（秒）
    CASE
        -- 场景1：相邻两条日志间隔超时
        WHEN PreviousLogTime IS NOT NULL 
             AND DATEDIFF(MINUTE, PreviousLogTime, a_Timestamp) >= @AlertThresholdMinutes
        THEN DATEDIFF(SECOND, PreviousLogTime, a_Timestamp)
        -- 场景2：最后一条日志到当前时间超时
        WHEN a_Timestamp = LastLogTime
             AND DATEDIFF(MINUTE, a_Timestamp, GETDATE()) >= @AlertThresholdMinutes
        THEN DATEDIFF(SECOND, a_Timestamp, GETDATE())
    END AS Duration,
    Machine,
    a_Timestamp,
    DownCode
FROM MachineLogSequence
WHERE Emailed <> 1
  -- 匹配两类超时触发条件
  AND (
        (PreviousLogTime IS NOT NULL 
         AND DATEDIFF(MINUTE, PreviousLogTime, a_Timestamp) >= @AlertThresholdMinutes)
        OR
        (a_Timestamp = LastLogTime
         AND DATEDIFF(MINUTE, a_Timestamp, GETDATE()) >= @AlertThresholdMinutes)
  )

• 窗口函数新增PARTITION BY Machine逻辑，彻底修正原逻辑跨机器计算时间差的核心bug，让每台机器的日志序列互相独立
• 新增MAX(a_Timestamp) OVER (PARTITION BY Machine)计算每台机器的最后一条日志时间，专门计算最后一条日志到当前实时时间的间隔，补上“故障后无新记录”的检测盲区
• 把无效记录的过滤前置到CTE计算环节，保证参与时间差计算的日志序列是连续完整的，不会因为混入无效记录拉偏时间差计算结果
• 统一把阈值判断抽成可复用的变量，后续调整预警阈值时不需要修改核心计算逻辑，降低维护成本

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