如何按小时桶分组统计订单并补全缺失时段的0值？

核心思路是生成覆盖订单时间范围的所有小时桶序列，再与订单表左连接统计，这样无订单的时段会自动显示0，无需创建临时表，用CTE（公共表表达式）即可实现。以下是主流数据库的具体实现：

PostgreSQL #

WITH hour_range AS (
    SELECT generate_series(
        date_trunc('hour', MIN(order_date)),
        date_trunc('hour', MAX(order_date)),
        '1 hour'::interval
    ) AS hour_bucket
    FROM orders
)
SELECT
    hr.hour_bucket AS mydate,
    COUNT(o.order_date) AS count
FROM hour_range hr
LEFT JOIN orders o ON date_trunc('hour', o.order_date) = hr.hour_bucket
GROUP BY hr.hour_bucket
ORDER BY hr.hour_bucket;

• 用generate_series直接生成从订单最早小时到最晚小时的连续序列
• 左连接后用COUNT(o.order_date)统计，NULL值会被忽略，无订单时段返回0

MySQL 8.0+ #

-- 先获取订单的时间边界
WITH time_bounds AS (
    SELECT 
        DATE_FORMAT(MIN(order_date), '%Y-%m-%d %H:00:00') AS start_hour,
        DATE_FORMAT(MAX(order_date), '%Y-%m-%d %H:00:00') AS end_hour
    FROM orders
),
-- 递归生成连续小时序列
hour_range AS (
    SELECT start_hour AS hour_bucket FROM time_bounds
    UNION ALL
    SELECT DATE_ADD(hour_bucket, INTERVAL 1 HOUR)
    FROM hour_range, time_bounds
    WHERE hour_bucket < end_hour
)
SELECT
    hr.hour_bucket AS mydate,
    COUNT(o.order_date) AS count
FROM hour_range hr
LEFT JOIN orders o ON DATE_FORMAT(o.order_date, '%Y-%m-%d %H:00:00') = hr.hour_bucket
GROUP BY hr.hour_bucket
ORDER BY hr.hour_bucket;

• 利用递归CTE生成连续小时，需先单独获取订单的时间边界以适配MySQL递归语法限制

SQL Server #

WITH hour_range AS (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, MIN(order_date)), 0) AS hour_bucket
    FROM orders
    UNION ALL
    SELECT DATEADD(HOUR, 1, hour_bucket)
    FROM hour_range
    WHERE hour_bucket < DATEADD(HOUR, DATEDIFF(HOUR, 0, MAX(order_date)), 0)
)
SELECT
    hr.hour_bucket AS mydate,
    COUNT(o.order_date) AS count
FROM hour_range hr
LEFT JOIN orders o ON DATEADD(HOUR, DATEDIFF(HOUR, 0, o.order_date), 0) = hr.hour_bucket
GROUP BY hr.hour_bucket
ORDER BY hr.hour_bucket
OPTION (MAXRECURSION 0); -- 时间范围超过100小时需添加此选项

• 递归CTE生成小时序列，DATEADD+DATEDIFF组合用于将时间截断到小时维度

Oracle #

WITH time_bounds AS (
    SELECT 
        TRUNC(MIN(order_date), 'HH24') AS start_hour,
        TRUNC(MAX(order_date), 'HH24') AS end_hour
    FROM orders
),
hour_range AS (
    SELECT start_hour + (LEVEL - 1)/24 AS hour_bucket
    FROM time_bounds
    CONNECT BY LEVEL <= (end_hour - start_hour)*24 + 1
)
SELECT
    hr.hour_bucket AS mydate,
    COUNT(o.order_date) AS count
FROM hour_range hr
LEFT JOIN orders o ON TRUNC(o.order_date, 'HH24') = hr.hour_bucket
GROUP BY hr.hour_bucket
ORDER BY hr.hour_bucket;

• 用CONNECT BY层级查询生成连续小时，通过TRUNC函数将时间截断到小时

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