PostgreSQL如何计算指定时间范围内工单各状态的持续时长

表结构约定 #

我们假设你的状态变更表名为 ticket_status_log，结构如下：

如果你的场景是多工单，只需在后续逻辑中增加ticket_id字段的分区和过滤条件即可。

实现逻辑 #

1. 先定义查询的时间区间参数
2. 补全查询区间开始前的最后一条状态记录，确保区间开头的时长可以被正确计算
3. 用窗口函数获取每条状态的下一次变更时间，生成每条状态的有效时间范围
4. 将每条状态的有效时间范围和查询区间做交集，计算交集的时长
5. 按状态分组汇总总时长，同时补全所有枚举状态，时长为0的状态也会输出

完整SQL代码 #

WITH
-- 1. 定义查询时间区间，可根据需要修改此处的值
query_range AS (
    SELECT
        '2021-11-22 10:00:00'::timestamp AS q_start,
        '2021-11-22 11:00:00'::timestamp AS q_end
),
-- 2. 定义所有状态枚举，用于最后补全0时长的状态
all_status AS (
    SELECT unnest(ARRAY['OPENED', 'CLOSED', 'RE-OPENED', 'FINISHED', 'REJECTED']) AS status
),
-- 3. 合并查询区间内的状态 + 区间开始前的最后一条状态
valid_status_log AS (
    -- 先取查询区间内的所有状态记录
    SELECT time, status
    FROM ticket_status_log, query_range
    WHERE time BETWEEN q_start AND q_end
    UNION ALL
    -- 再补区间开始前的最后一条状态记录
    SELECT time, status
    FROM ticket_status_log, query_range
    WHERE time < q_start
    ORDER BY time DESC
    LIMIT 1
),
-- 4. 给每条状态拼接下一次变更时间，生成状态生效的时间区间
status_with_next AS (
    SELECT
        status,
        time AS cur_time,
        lead(time, 1, (SELECT q_end FROM query_range)) OVER (ORDER BY time) AS next_time
    FROM valid_status_log
),
-- 5. 裁剪时间区间到查询范围内，计算每个状态段的有效时长
status_duration AS (
    SELECT
        status,
        -- 计算区间交集的时长，结束时间取next_time和查询结束的较小值，开始时间取cur_time和查询开始的较大值
        least(next_time, q_end) - greatest(cur_time, q_start) AS duration
    FROM status_with_next, query_range
    WHERE cur_time < q_end -- 过滤掉完全在查询区间外的状态段
)
-- 6. 汇总各状态总时长，补全所有枚举状态
SELECT
    s.status,
    COALESCE(to_char(SUM(d.duration), 'HH24:MI'), '00:00') AS duration
FROM all_status s
LEFT JOIN status_duration d ON s.status = d.status
GROUP BY s.status
ORDER BY s.status;

验证结果 #

使用你提供的示例数据和查询区间2021-11-22 10:00 - 2021-11-22 11:00执行上述SQL，输出结果和预期完全一致：

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