Oracle SQL分钟级统计补全无计数时段实现方法

直接对业务表做分组聚合，只会返回存在匹配记录的分钟点位，要实现无数据分钟计数显示为0的全天逐分钟统计，核心思路是先构造目标日期全天00:00-23:59共1440个连续分钟的时间序列，再左关联业务表的聚合结果，对无匹配的点位补0即可。

可直接运行的SQL代码 #

WITH all_minutes AS (
    -- 生成指定日期全天所有分钟点位
    SELECT 
        TRUNC(TO_TIMESTAMP_TZ('06/07/2022 00:00 +02:00','DD/MM/YYYY HH24:MI TZH:TZM'), 'DD') + (LEVEL - 1)/1440 AS minute_point
    FROM dual
    CONNECT BY LEVEL <= 1440
),
audit_minute_stats AS (
    -- 按业务逻辑统计有数据的分钟计数
    SELECT 
        COUNT(*) AS record_cnt, 
        TRUNC(creation_date, 'MI') AS minute_point
    FROM "AUDIT"
    WHERE MODULE_NAME = 'PERSONAL_PLUGIN'
      AND CREATION_DATE >= TRUNC(TO_TIMESTAMP_TZ('06/07/2022 00:00 +02:00','DD/MM/YYYY HH24:MI TZH:TZM'), 'DD')
      AND CREATION_DATE < TRUNC(TO_TIMESTAMP_TZ('06/07/2022 00:00 +02:00','DD/MM/YYYY HH24:MI TZH:TZM'), 'DD') + 1
    GROUP BY TRUNC(creation_date, 'MI')
)
-- 左关联补全0值
SELECT 
    NVL(s.record_cnt, 0) AS record_count,
    m.minute_point
FROM all_minutes m
LEFT JOIN audit_minute_stats s ON m.minute_point = s.minute_point
ORDER BY m.minute_point ASC;

逻辑说明 #

• all_minutes公用表达式通过Oracle层级查询CONNECT BY生成连续时间序列，1天共1440分钟，固定返回1440行逐分钟时间点，不会出现时间断层
• 业务统计部分将时间范围限定在目标日期全天，避免扫描无关分区/数据，查询效率更高
• 最终通过左连接关联全量分钟和业务统计结果，用NVL函数将无匹配记录的分钟计数统一置为0，输出结果完全覆盖全天所有分钟粒度，满足分钟级数据比对校验需求
• 切换统计日期时，仅需修改代码中两处基准时间的日期字符串即可，其余逻辑无需调整

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