如何在T-SQL的LAG函数中使用Max(RowID)计算告警时长？

核心思路 #

同一个alarm_id的多条状态记录中，只需取最早的创建时间和最晚的清除时间计算差值，忽略中间状态变更。以下是两种高效的实现方式：

方法1：窗口函数筛选首尾记录 #

通过ROW_NUMBER()给每个告警的记录按时间排序，标记首行（创建）和末行（清除），再关联计算时间差：

WITH AlarmTimeline AS (
    SELECT 
        alarm_id,
        status_change_time,
        -- 按时间升序标记，1对应最早的创建记录
        ROW_NUMBER() OVER (PARTITION BY alarm_id ORDER BY status_change_time ASC) AS rn_asc,
        -- 按时间降序标记，1对应最晚的清除记录
        ROW_NUMBER() OVER (PARTITION BY alarm_id ORDER BY status_change_time DESC) AS rn_desc
    FROM 
        your_alarm_table -- 替换为你的表名
)
SELECT 
    a.alarm_id,
    -- 可替换时间单位：MINUTE/HOUR/DAY等
    DATEDIFF(SECOND, a.status_change_time, b.status_change_time) AS alarm_duration_seconds
FROM 
    AlarmTimeline a
JOIN 
    AlarmTimeline b ON a.alarm_id = b.alarm_id
WHERE 
    a.rn_asc = 1 
    AND b.rn_desc = 1;

优势： #

• 不受状态顺序影响，确保准确取到首尾记录
• 可扩展：如果需要验证首尾状态（比如首行必须是Created，末行必须是Cleared），只需在WHERE中添加状态过滤条件

方法2：直接分组聚合（适合明确末行是清除状态的场景） #

如果能确保每个告警的最后一条记录一定是清除状态，直接通过聚合函数取最小/最大时间计算：

SELECT 
    alarm_id,
    DATEDIFF(SECOND, MIN(status_change_time), MAX(status_change_time)) AS alarm_duration_seconds
FROM 
    your_alarm_table
GROUP BY 
    alarm_id;

补充：过滤未清除的告警 #

如果存在未清除的告警（无清除状态记录），可先筛选出已清除的告警再计算：

WITH ClearedAlarms AS (
    SELECT DISTINCT alarm_id
    FROM your_alarm_table
    WHERE status = 'Cleared' -- 替换为你的清除状态标识
)
SELECT 
    t.alarm_id,
    DATEDIFF(SECOND, MIN(t.status_change_time), MAX(t.status_change_time)) AS alarm_duration_seconds
FROM 
    your_alarm_table t
JOIN 
    ClearedAlarms c ON t.alarm_id = c.alarm_id
GROUP BY 
    t.alarm_id;

关于LAG函数的说明 #

LAG函数用于获取当前行的上一行数据，适合计算相邻状态的时间间隔，但不适合直接取首尾记录的差值。如果一定要用LAG，需要先将每个告警的首尾时间聚合到单行，反而增加复杂度，因此不推荐。

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