Oracle按15分钟间隔分组统计，需包含无数据时段0值

问题描述 #

现有Oracle查询可按15分钟间隔统计客户呼叫数据，但无数据的时段不会返回记录（即计数为0的行缺失），需要补全这些时段的统计结果。

当前查询语句 #

SELECT 
    TO_CHAR(TRUNC(time_stamp)
        + FLOOR(TO_NUMBER(TO_CHAR(time_stamp, 'SSSSS'))/900)/96, 'YYYY-MM-DD HH24:MI:SS') time_start,
    COUNT (CUSTOMERS) Customer_Calls
FROM CUSTOMERS
WHERE time_stamp >= to_date('2023-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY
    TRUNC(time_stamp) + FLOOR(TO_NUMBER(TO_CHAR(time_stamp, 'SSSSS'))/900)/96;

当前输出（仅含有数据时段） #

2023-03-23 00:30:00 1
2023-03-23 00:45:00 1
2023-03-23 01:45:00 1
2023-03-23 03:45:00 1

期望输出（含所有15分钟间隔，无数据时段计数为0） #

2023-03-23 00:00:00 0
2023-03-23 00:15:00 0
2023-03-23 00:30:00 1
2023-03-23 00:45:00 1
2023-03-23 01:00:00 0
2023-03-23 01:15:00 0
2023-03-23 01:30:00 0
2023-03-23 01:45:00 1
...

解决方案 #

核心思路：先生成指定时间范围内所有15分钟间隔的时间序列，再左连接原表的统计结果，从而补全无数据时段的0值记录。

方法1：使用递归CTE生成时间序列 #

WITH time_intervals AS (
    -- 定义起始时间
    SELECT TO_DATE('2023-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS interval_start
    FROM DUAL
    UNION ALL
    -- 递归生成后续15分钟间隔的时间点
    SELECT interval_start + INTERVAL '15' MINUTE
    FROM time_intervals
    -- 定义结束时间（这里设为当天最后一个15分钟时段）
    WHERE interval_start < TO_DATE('2023-03-23 23:45:00', 'YYYY-MM-DD HH24:MI:SS')
)
SELECT
    TO_CHAR(ti.interval_start, 'YYYY-MM-DD HH24:MI:SS') AS time_start,
    COUNT(c.CUSTOMERS) AS Customer_Calls
FROM time_intervals ti
-- 左连接原表，匹配对应15分钟时段的呼叫数据
LEFT JOIN CUSTOMERS c
    ON TRUNC(c.time_stamp) + FLOOR(TO_NUMBER(TO_CHAR(c.time_stamp, 'SSSSS'))/900)/96 = ti.interval_start
GROUP BY ti.interval_start
-- 按时间排序确保结果顺序正确
ORDER BY ti.interval_start;

方法2：使用CONNECT BY生成时间序列（更简洁） #

WITH time_intervals AS (
    -- 生成从起始时间开始的96个15分钟间隔（对应1天的所有时段）
    SELECT TO_DATE('2023-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
           + (LEVEL - 1) * INTERVAL '15' MINUTE AS interval_start
    FROM DUAL
    CONNECT BY LEVEL <= 96 -- 24小时*60分钟/15分钟 = 96个时段
)
SELECT
    TO_CHAR(ti.interval_start, 'YYYY-MM-DD HH24:MI:SS') AS time_start,
    COUNT(c.CUSTOMERS) AS Customer_Calls
FROM time_intervals ti
LEFT JOIN CUSTOMERS c
    ON TRUNC(c.time_stamp) + FLOOR(TO_NUMBER(TO_CHAR(c.time_stamp, 'SSSSS'))/900)/96 = ti.interval_start
GROUP BY ti.interval_start
ORDER BY ti.interval_start;

说明 #

• 若需统计跨多天的数据，只需调整起始时间和结束时间（或LEVEL的数量，比如跨N天则设LEVEL <= 96*N）。
• COUNT(c.CUSTOMERS)会自动处理无匹配数据的情况，返回0（因为左连接无匹配时c.CUSTOMERS为NULL，COUNT(NULL)结果为0）。

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