PostgreSQL中计算用户DONE到下一个IN_TRANSIT状态的空闲时间总和

现有如下数据表：

（注：原数据最后一条记录id重复为7，此处修正为8以保证id唯一性）

需求：计算每个用户的空闲时间总和，即该用户DONE状态记录与下一条IN_TRANSIT状态记录之间的时间间隔之和。

预期结果：

使用窗口函数LEAD()定位每条DONE记录后的下一条IN_TRANSIT记录，再计算时间差并求和。以下是MySQL环境下的实现代码：

WITH user_records AS (
    SELECT 
        user_id,
        created_at,
        status,
        -- 按用户分组、时间排序，获取当前记录之后的下一条IN_TRANSIT记录时间
        LEAD(CASE WHEN status = 'IN_TRANSIT' THEN created_at END) 
            OVER (PARTITION BY user_id ORDER BY created_at) AS next_transit_time
    FROM your_table_name
)
SELECT 
    user_id,
    -- 将总秒数转换为HH:MM:SS格式
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, created_at, next_transit_time))) AS idle_time
FROM user_records
WHERE status = 'DONE' AND next_transit_time IS NOT NULL
GROUP BY user_id;

逻辑说明 #

1. 通过LEAD()窗口函数，为每条记录匹配同用户后续第一条IN_TRANSIT状态的时间；
2. 筛选出状态为DONE且存在后续IN_TRANSIT记录的行，计算每条记录的时间间隔（秒数）；
3. 按用户分组求和时间间隔，再转换为HH:MM:SS格式输出。

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