如何用SQL计算多组时间区间总时长（避免使用UDF）

原始数据 #

核心思路 #

利用窗口函数LEAD()为每个start记录匹配对应的end时间，再通过时间差函数计算单对时长，最后按ID求和并格式化结果，全程无需UDF。

MySQL实现代码 #

WITH start_end_pairs AS (
    SELECT
        ID,
        DATETIME AS start_time,
        -- 取当前start的下一条记录（对应end）的时间
        LEAD(DATETIME) OVER (PARTITION BY ID ORDER BY DATETIME) AS end_time
    FROM your_table
    WHERE ACTION = 'start'
)
SELECT
    ID,
    -- 将总秒数转为HH:MM:SS格式
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, start_time, end_time))) AS TOTAL_TIME
FROM start_end_pairs
WHERE end_time IS NOT NULL -- 过滤无对应end的异常start
GROUP BY ID;

SQL Server实现代码 #

WITH start_end_pairs AS (
    SELECT
        ID,
        DATETIME AS start_time,
        LEAD(DATETIME) OVER (PARTITION BY ID ORDER BY DATETIME) AS end_time
    FROM your_table
    WHERE ACTION = 'start'
)
SELECT
    ID,
    -- 转换总秒数为HH:MM:SS格式
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, start_time, end_time)), 0), 108) AS TOTAL_TIME
FROM start_end_pairs
WHERE end_time IS NOT NULL
GROUP BY ID;

结果验证 #

执行后将得到预期结果：

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