PostgreSQL按小时分组并将缺失小时补0的实现方法

这个需求太常见了——要确保按小时分组的结果里，0到23点每小时都有记录，哪怕对应时段没有数据。核心思路很简单：先生成一个包含0-23所有小时的完整时间序列，再和你的业务数据做左连接，把缺失的统计值补为0。

下面针对主流数据库给出具体解决方案：

1. PostgreSQL 实现 #

PostgreSQL 自带 generate_series 函数，可以快速生成连续的小时序列：

WITH hours AS (
  SELECT generate_series(0,23) AS hourr
)
SELECT 
  COALESCE(COUNT(cr.created_date), 0) AS count,
  h.hourr
FROM hours h
LEFT JOIN client_requests cr
  ON EXTRACT(HOUR FROM cr.created_date) = h.hourr
  -- 把日期过滤条件放在JOIN里，避免左连接被转为内连接
  AND cr.created_date >= '2020-02-24 00:00:00' 
  AND cr.created_date < '2020-02-25 00:00:00' -- 用小于次日0点更严谨，覆盖毫秒级数据
GROUP BY h.hourr
ORDER BY h.hourr ASC;

2. MySQL 实现 #

MySQL 8.0+ 支持递归CTE，低版本可以手动枚举小时：

递归CTE方式（MySQL 8.0+） #

WITH RECURSIVE hours AS (
  SELECT 0 AS hourr
  UNION ALL
  SELECT hourr + 1 FROM hours WHERE hourr < 23
)
SELECT 
  COALESCE(COUNT(cr.created_date), 0) AS count,
  h.hourr
FROM hours h
LEFT JOIN client_requests cr
  ON HOUR(cr.created_date) = h.hourr
  AND cr.created_date >= '2020-02-24 00:00:00' 
  AND cr.created_date < '2020-02-25 00:00:00'
GROUP BY h.hourr
ORDER BY h.hourr ASC;

手动枚举方式（兼容所有MySQL版本） #

SELECT 
  COALESCE(COUNT(cr.created_date), 0) AS count,
  h.hourr
FROM (
  SELECT 0 AS hourr UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
  SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
  SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) h
LEFT JOIN client_requests cr
  ON HOUR(cr.created_date) = h.hourr
  AND cr.created_date >= '2020-02-24 00:00:00' 
  AND cr.created_date < '2020-02-25 00:00:00'
GROUP BY h.hourr
ORDER BY h.hourr ASC;

3. SQL Server 实现 #

可以用递归CTE或系统表生成小时序列：

递归CTE方式 #

WITH RECURSIVE hours AS (
  SELECT 0 AS hourr
  UNION ALL
  SELECT hourr + 1 FROM hours WHERE hourr < 23
)
SELECT 
  COALESCE(COUNT(cr.created_date), 0) AS count,
  h.hourr
FROM hours h
LEFT JOIN client_requests cr
  ON DATEPART(HOUR, cr.created_date) = h.hourr
  AND cr.created_date >= '2020-02-24 00:00:00' 
  AND cr.created_date < '2020-02-25 00:00:00'
GROUP BY h.hourr
ORDER BY h.hourr ASC;

关键注意点 #

• 日期过滤条件放JOIN子句：如果放在WHERE里，左连接后无匹配的记录会被过滤，失去补0效果。
• 用COALESCE处理NULL：左连接后无数据的小时，COUNT(cr.created_date) 会返回NULL，需要用COALESCE转为0。
• 日期范围严谨性：用< '次日0点'代替< '当日23:59:59'，可以覆盖毫秒级的时间数据（比如23:59:59.999）。

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