SQL状态累计耗时统计问题：基于日期排序计算各状态时长

不需要用WHILE循环，用窗口函数LEAD()就能高效实现需求——SQL是集合式操作，比逐行循环的性能好得多，尤其数据量大的时候。

核心思路 #

每条状态记录的持续时间，等于当前记录的日期到下一条状态记录的日期的时间差。我们可以用LEAD()窗口函数获取每条记录的下一条日期，计算差值后按状态累加，最后转成时:分:秒格式。

完整SQL示例（以SQL Server为例） #

WITH state_durations AS (
    SELECT
        [state],
        -- 计算当前状态到下一次状态切换的毫秒数
        DATEDIFF(MILLISECOND, [date], LEAD([date]) OVER (ORDER BY [date] ASC)) AS duration_ms
    FROM tempo
)
SELECT
    [state],
    -- 将总毫秒数转换为 时:分:秒 格式（补前导零保证格式统一）
    CONCAT(
        RIGHT('0' + CAST(SUM(duration_ms) / 3600000 AS VARCHAR), 2), ':',
        RIGHT('0' + CAST((SUM(duration_ms) % 3600000) / 60000 AS VARCHAR), 2), ':',
        RIGHT('0' + CAST((SUM(duration_ms) % 60000) / 1000 AS VARCHAR), 2)
    ) AS [总耗时（时:分:秒）]
FROM state_durations
-- 过滤最后一条无后续状态的记录（如果要统计到当前时间，可去掉此条件，修改LEAD的默认值）
WHERE duration_ms IS NOT NULL
GROUP BY [state]
ORDER BY [state];

关键说明 #

1. LEAD()函数的作用：LEAD([date]) OVER (ORDER BY [date] ASC) 会按日期升序，为每条记录获取下一条记录的日期，这样就能得到当前状态的结束时间。
2. 处理最后一条记录：如果最后一个状态还未切换（没有后续记录），duration_ms会是NULL。如果需要统计这个状态到当前时间的耗时，把LEAD([date])改成LEAD([date], 1, GETDATE())，同时去掉WHERE duration_ms IS NOT NULL。
3. 适配其他数据库：
   • MySQL：用TIMESTAMPDIFF(SECOND, [date], next_date)计算秒数，再转换格式；
   • PostgreSQL：用EXTRACT(EPOCH FROM (next_date - [date]))获取秒数，再进行时分秒转换。
4. 性能优势：窗口函数是数据库原生优化的操作，比WHILE循环逐行计算快很多，数据量越大优势越明显。

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