PostgreSQL：如何计算各状态的累计耗时

要解决这个问题，核心是先处理连续重复的冗余状态记录，再为每个状态匹配对应的结束时间，最后汇总计算总耗时。以下是具体实现步骤和SQL语句：

关键思路 #

1. 过滤冗余状态：连续相同的状态记录属于重复信息，只保留状态发生变化的节点（包括第一条记录）。
2. 匹配结束时间：对去重后的记录，用窗口函数获取下一个状态的时间戳作为当前状态的结束时间；对于最后一个无后续状态的记录，用当前时间作为结束时间（可按需替换为固定时间）。
3. 汇总总耗时：计算每个状态时间段的时长，再按状态分组求和得到总耗时。

完整SQL查询 #

-- 第一步：过滤连续重复的冗余状态
WITH deduplicated_states AS (
    SELECT 
        state,
        dt
    FROM (
        SELECT 
            state,
            dt,
            -- 获取前一条记录的状态
            LAG(state) OVER (ORDER BY dt) AS prev_state
        FROM states
    ) s
    -- 仅保留状态变化的记录（或第一条记录）
    WHERE prev_state IS NULL OR prev_state != state
),
-- 第二步：为每个状态匹配结束时间
state_time_ranges AS (
    SELECT 
        state,
        dt AS start_time,
        -- 获取下一个状态的时间，最后一个状态用当前时间兜底
        LEAD(dt, 1, CURRENT_TIMESTAMP) OVER (ORDER BY dt) AS end_time
    FROM deduplicated_states
)
-- 第三步：计算每个状态的总耗时（以小时为单位）
SELECT 
    state,
    -- 将时间差转换为小时数并求和
    SUM(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS total_hours
FROM state_time_ranges
GROUP BY state
ORDER BY state;

示例结果 #

针对题目中的测试数据，若当前时间为2023-02-21T06:00:00，执行查询后会得到如下结果：

自定义调整 #

• 若不需要用当前时间作为最后状态的结束时间，可将LEAD(dt, 1, CURRENT_TIMESTAMP)改为LEAD(dt)，此时最后一个状态的end_time会为NULL，可通过COALESCE或过滤条件处理。
• 如需以分钟/秒为单位，只需将/ 3600改为/ 60（分钟）或直接保留秒数。

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