如何计算数据表中各事件的起止时间差并批量返回结果？

你的问题核心是要匹配同一地点的开始事件（Event_Type=1）和对应结束事件（Event_Type=2），计算两者的时间差。之前的SQL只通过LAG按全局时间排序取上一条记录，没有按地点分组，也没区分事件类型，自然无法得到正确配对的时间差。

以下是两种可行的解决方案：

方法1：自连接配对起止事件 #

通过将表自身连接，把开始事件和同地点的结束事件关联起来，同时确保结束事件是该开始事件之后的第一个对应结束（避免一对多错误配对）：

SELECT 
    s.Event_Location,
    s.Event_Time AS 开始时间,
    DATEDIFF(e.Event_Time, s.Event_Time) AS 事件时长
FROM 
    event_table s
JOIN 
    event_table e ON s.Event_Location = e.Event_Location
WHERE 
    s.Event_Type = 1
    AND e.Event_Type = 2
    AND e.Event_Time > s.Event_Time
-- 确保每个开始事件匹配最近的结束事件
AND NOT EXISTS (
    SELECT 1 
    FROM event_table e2
    WHERE e2.Event_Location = s.Event_Location
      AND e2.Event_Type = 2
      AND e2.Event_Time > s.Event_Time
      AND e2.Event_Time < e.Event_Time
)

方法2：窗口函数定位对应结束事件 #

使用LEAD窗口函数，按地点分区、时间排序，直接获取每个开始事件之后的第一个结束事件时间：

SELECT 
    Event_Location AS 事件地点,
    Event_Time AS 开始时间,
    DATEDIFF(next_end_time, Event_Time) AS 事件时长
FROM (
    SELECT 
        *,
        -- 仅提取同地点下后续的结束事件时间
        LEAD(CASE WHEN Event_Type = 2 THEN Event_Time END) 
            OVER (PARTITION BY Event_Location ORDER BY Event_Time) AS next_end_time
    FROM event_table
) t
WHERE Event_Type = 1
  AND next_end_time IS NOT NULL -- 过滤无对应结束事件的开始记录

关键说明 #

• PARTITION BY Event_Location：确保只在同一地点内匹配事件，避免跨地点错误配对
• 两种方法都通过Event_Type筛选起止事件，保证计算的是有效配对的时间差
• 若你的数据库中DATEDIFF函数参数顺序不同（比如部分数据库是DATEDIFF(时间单位, 开始时间, 结束时间)），请根据实际情况调整参数顺序

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