Oracle SQL如何实现按天按小时统计未关闭活跃工单数量

完整实现代码 #

WITH time_range AS (
    -- 计算需要覆盖的时间范围：最早工单创建整点到最晚工单关闭整点+1小时
    SELECT TRUNC(MIN(start_date), 'HH') min_hour,
           TRUNC(MAX(repair_date), 'HH') + INTERVAL '1' HOUR max_hour
    FROM tickets -- 替换为你的实际表名
),
hour_series AS (
    -- 生成连续无缺口的整点小时序列
    SELECT min_hour + (LEVEL - 1) * INTERVAL '1' HOUR AS hour_start
    FROM time_range
    CONNECT BY min_hour + (LEVEL - 1) * INTERVAL '1' HOUR < max_hour
)
SELECT 
    EXTRACT(MONTH FROM h.hour_start) AS month,
    EXTRACT(DAY FROM h.hour_start) AS day,
    EXTRACT(HOUR FROM h.hour_start) AS hour,
    COUNT(t.ticket_num) AS "#active_tix"
FROM hour_series h
LEFT JOIN tickets t -- 替换为你的实际表名
    -- 匹配规则：工单创建时间早于当前时段结束，且关闭时间晚于等于当前时段开始
    ON t.start_date < h.hour_start + INTERVAL '1' HOUR
    AND t.repair_date >= h.hour_start
GROUP BY 
    h.hour_start, 
    EXTRACT(MONTH FROM h.hour_start), 
    EXTRACT(DAY FROM h.hour_start), 
    EXTRACT(HOUR FROM h.hour_start)
ORDER BY h.hour_start;

说明 #

1. 请将代码中的tickets替换为你实际的表名
2. 时间范围默认自动适配你的数据集的最早工单创建时间到最晚工单关闭时间，若需要自定义统计范围，可直接修改time_rangeCTE中的min_hour和max_hour为固定值，示例如下：

SELECT 
    TO_DATE('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') min_hour,
    TO_DATE('2021-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') max_hour
FROM dual

3. 统计逻辑完全匹配需求：覆盖所有时段，无活跃工单的时段自动输出0，计数规则和样例输出完全一致。

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