如何让SQL GROUP BY查询显示计数为0的终端-小时维度记录？

问题分析 #

你当前的查询存在两个关键问题：

1. 表名笔误：FROM airpot_terminals 应该是 FROM Flights（你的目标表是Flights）；
2. 仅返回存在航班数据的分组：WHERE子句过滤出前一天有航班的记录，GROUP BY只会生成有数据的terminal_id + hour组合，自然缺失计数为0的行。

要得到3个终端×24小时的完整72条记录，需要先生成所有可能的终端ID-小时组合，再左连接航班数据统计数量。

解决方案 #

核心思路是：生成所有终端ID与0-23小时的笛卡尔积，再左连接目标日期的航班数据，最终聚合统计（无匹配时计数为0）。

针对MySQL的实现

WITH hours AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 FROM hours WHERE hour < 23
),
terminals AS (
  SELECT DISTINCT terminal_id FROM Flights
)
SELECT 
  t.terminal_id,
  h.hour,
  COUNT(f.id) AS count
FROM terminals t
CROSS JOIN hours h
LEFT JOIN Flights f 
  ON f.terminal_id = t.terminal_id
  AND HOUR(f.departure_datetime) = h.hour
  AND DATE(f.departure_datetime) = CURRENT_DATE() - INTERVAL 1 DAY
GROUP BY t.terminal_id, h.hour
ORDER BY t.terminal_id, h.hour;

针对PostgreSQL的实现

WITH hours AS (
  SELECT generate_series(0,23) AS hour
),
terminals AS (
  SELECT DISTINCT terminal_id FROM Flights
)
SELECT 
  t.terminal_id,
  h.hour,
  COUNT(f.id) AS count
FROM terminals t
CROSS JOIN hours h
LEFT JOIN Flights f 
  ON f.terminal_id = t.terminal_id
  AND EXTRACT(HOUR FROM f.departure_datetime) = h.hour
  AND DATE(f.departure_datetime) = CURRENT_DATE - INTERVAL '1 day'
GROUP BY t.terminal_id, h.hour
ORDER BY t.terminal_id, h.hour;

关键说明

• 生成全量组合：通过CROSS JOIN将所有终端ID和0-23小时进行笛卡尔积，确保每个终端的每个小时都有基础记录；
• 左连接筛选：将日期、小时、终端ID的匹配条件放在ON子句而非WHERE，避免过滤掉无航班的组合；
• 正确计数：使用COUNT(f.id)而非COUNT(*)，因为COUNT(*)会把左连接后的NULL行统计为1，而COUNT(f.id)仅统计存在航班的行，无匹配时返回0。

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