如何用SQL计算同一id下event_type A事件间的总间隔时间？

需求说明 #

计算同一id下所有event_type = 'A'事件之间的总间隔时间：

• 若id下A事件数量≥2，累加**前一个A事件的end_time到后一个A事件的start_time**的时间差总和
• 若id下A事件数量为0或1，间隔时间返回0

示例数据 #

id    start_time         end_time          event_type
1     00:00:00.00000     00:00:00.04300    A
1     00:00:00.04300     00:00:00.08600    B
1     00:00:00.08600     00:00:00.12900    C
1     00:00:00.12900     00:00:00.13200    A
2     00:00:00.00000     00:00:00.05900    B
2     00:00:00.05900     00:00:00.06900    A

期望结果 #

id    interval_time
1     86
2     0

原SQL的问题 #

你尝试的SQL错误地累加了非A事件的时长，没有聚焦在A事件之间的间隔，也未处理A事件数量≤1的场景，因此无法得到正确结果。

解决方案 #

使用窗口函数LAG定位前一个A事件的结束时间，再按ID累加间隔，最后处理无A/单个A的情况：

WITH a_events AS (
    SELECT 
        id,
        start_time,
        end_time,
        -- 获取同一ID下前一个A事件的结束时间
        LAG(end_time) OVER (PARTITION BY id ORDER BY start_time) AS prev_a_end
    FROM my_table
    WHERE event_type = 'A'
),
interval_sums AS (
    SELECT 
        id,
        -- 累加当前A与前一个A的间隔（仅存在前一个A时计算）
        SUM(TIME_DIFF(start_time, prev_a_end, MILLISECOND)) AS total_interval
    FROM a_events
    GROUP BY id
)
-- 关联所有唯一ID，将无A/单个A的情况转为0
SELECT 
    t.id,
    COALESCE(isum.total_interval, 0) AS interval_time
FROM (SELECT DISTINCT id FROM my_table) t
LEFT JOIN interval_sums isum ON t.id = isum.id;

逻辑说明 #

1. a_events CTE：筛选所有A事件，用LAG窗口函数按时间顺序获取每个A事件的前一个同ID A事件的结束时间。
2. interval_sums CTE：按ID分组，累加每个A事件与前一个A的时间差（单个A事件时，prev_a_end为null，SUM结果也为null）。
3. 最终查询：从原表取出所有唯一ID，左关联间隔计算结果，用COALESCE将null（无A/单个A）转为0，确保所有ID都有结果。

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