Oracle查询如何补全当日截至当前小时的缺失时段统计数据

实现逻辑 #

• 先构造当日0点到当前小时的完整小时维度序列，保证所有需要返回的时段都存在
• 将原业务统计结果按小时聚合后，和小时维度做左关联，无业务数据的时段指标补0
• 最后基于关联后的结果计算累计值，即可得到全时段的统计结果

修改后完整SQL #

with hours as (
    -- 生成当日0点至当前小时的所有小时序列，格式为两位字符串
    select lpad(level - 1, 2, '0') as HOUR
    from dual
    connect by level <= to_number(to_char(sysdate, 'hh24')) + 1
),
t1 as(
    -- 原有业务统计逻辑保持不变
    SELECT
    case when type = 'ABC' then sum(CNT) end START,
    case when type = 'CDE' then sum(CNT) end END,
    (to_char(date,'hh24')) HOUR, type activity
    from XYZ
    where date >= trunc(sysdate)
    group by 
    to_char(date,'hh24'),type
),
t2 as (
    -- 按小时聚合同小时内的多type统计数据
    select HOUR,
           nvl(sum(START),0) as START_HOUR,
           nvl(sum(END),0) as END_HOUR
    from t1
    group by HOUR
)
-- 左关联小时维度与业务统计结果，计算累计值
select 
    h.HOUR,
    sum(nvl(t2.START_HOUR,0)) over (order by h.HOUR) as START,
    sum(nvl(t2.END_HOUR,0)) over (order by h.HOUR) as END
from hours h
left join t2 on h.HOUR = t2.HOUR
order by h.HOUR;

注意事项 #

• 原SQL中用到的date是Oracle官方关键字，如果你表中的时间字段确实命名为date，使用时需要用双引号包裹，否则建议替换为实际的字段名避免语法报错
• 如果需要返回全天24小时的结果而非仅截至当前小时，只需将hours表达式中connect by level <= to_number(to_char(sysdate, 'hh24')) + 1改为connect by level <=24即可

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