如何在SQL Server中编写查询计算事件间时间差（支持非连续事件场景）

我明白你现在的需求：要给每个服务启动事件（EventDisplayNumber=6005）匹配它之前最近的服务停止事件（EventDisplayNumber=6006），同时处理没有对应停止事件的情况，返回NULL值。下面是针对这个需求的具体解决方案：

解决方案SQL代码 #

WITH RankedEvents AS (
    SELECT 
        ComputerName,
        Time,
        EventDisplayNumber,
        -- 按机器分组、时间升序，抓取当前启动事件之前最近的停止事件时间
        LAG(CASE WHEN EventDisplayNumber = 6006 THEN Time END) OVER (
            PARTITION BY ComputerName 
            ORDER BY Time ASC
        ) AS PreviousStopTime
    FROM YourTableName -- 替换成你的实际表名
)
SELECT 
    ComputerName,
    PreviousStopTime AS [Event Stopped],
    Time AS [Event Started],
    -- 仅当存在对应停止事件时计算分钟差，否则返回NULL
    CASE 
        WHEN PreviousStopTime IS NOT NULL 
        THEN DATEDIFF(MINUTE, PreviousStopTime, Time) 
        ELSE NULL 
    END AS DifferenceMinutes
FROM RankedEvents
WHERE EventDisplayNumber = 6005 -- 只筛选启动事件进行匹配
ORDER BY Time ASC;

代码逻辑解释 #

1. CTE RankedEvents：
   • 用LAG()窗口函数按ComputerName分组、时间升序排序，专门为每个事件抓取之前最近的6006停止事件的时间。如果当前启动事件之前没有任何停止事件，PreviousStopTime会自动返回NULL。
2. 主查询：
   • 只筛选启动事件（6005），因为我们的目标是为每个启动事件匹配对应的停止记录。
   • 用DATEDIFF(MINUTE, ...)计算停止到启动的分钟差，当没有对应停止事件时直接返回NULL，完全符合你的需求。
   • 最后按时间升序排序，和你期望的结果顺序一致。

验证结果 #

针对你提供的示例数据，执行上述查询后会得到和你期望完全一致的结果：

注意事项 #

• 记得把代码中的YourTableName替换成你实际使用的数据表名称。
• 如果你的表中存在其他类型的事件（非6005/6006），这个查询依然有效，因为LAG()只会识别并抓取6006类型的停止事件，其他事件会被自动忽略。

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