SQL按小时分组统计时如何为无数据的缺失小时填充0？

实现方案 #

核心逻辑 #

先生成统计日期当日的完整24小时整点时间序列，再将该序列与原有统计结果做左关联，通过COALESCE函数将未匹配到数据的小时的计数值替换为0。

适配PostgreSQL的修改后语句（原语句使用的date_trunc为PostgreSQL原生函数） #

WITH all_hours AS (
    -- 生成2021-09-19当日全部24个整点的时间序列
    SELECT generate_series(
        '2021-09-19 00:00:00'::timestamp,
        '2021-09-19 23:00:00'::timestamp,
        '1 hour'::interval
    ) AS h
),
original_count AS (
    -- 原有统计逻辑保持不变
    SELECT 
        date_trunc('hour', s.fill_instant) h,
        count(*) c
    FROM sms s
    LEFT JOIN station s2 ON s.station_id = s2.station_id
    WHERE 
        s2.address LIKE '%arizona%'
        AND s.fill_date = '2021-09-19'
    GROUP BY date_trunc('hour', s.fill_instant)
)
-- 关联得到补0后的完整统计结果
SELECT
    a.h,
    COALESCE(o.c, 0) c
FROM all_hours a
LEFT JOIN original_count o ON a.h = o.h
ORDER BY a.h ASC;

适配MySQL 8.0+的修改后语句 #

如果使用MySQL，将时间序列生成部分替换为递归CTE即可：

WITH RECURSIVE all_hours AS (
    SELECT '2021-09-19 00:00:00' AS h
    UNION ALL
    SELECT DATE_ADD(h, INTERVAL 1 HOUR) 
    FROM all_hours 
    WHERE h < '2021-09-19 23:00:00'
),
original_count AS (
    SELECT 
        DATE_FORMAT(s.fill_instant, '%Y-%m-%d %H:00:00') h,
        count(*) c
    FROM sms s
    LEFT JOIN station s2 ON s.station_id = s2.station_id
    WHERE 
        s2.address LIKE '%arizona%'
        AND DATE(s.fill_date) = '2021-09-19'
    GROUP BY DATE_FORMAT(s.fill_instant, '%Y-%m-%d %H:00:00')
)
SELECT
    a.h,
    COALESCE(o.c, 0) c
FROM all_hours a
LEFT JOIN original_count o ON a.h = o.h
ORDER BY a.h ASC;

说明 #

1. 预期结果中的2021-09-19 24:00:00实际等价于2021-09-20 00:00:00，不属于当日统计范围，若确实需要保留该条，可将时间序列的结束值调整为2021-09-20 00:00:00。
2. 原有语句中s.fill_date between '2021-09-19' and '2021-09-19'等价于s.fill_date = '2021-09-19'，已做简化，不影响原有逻辑。

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