Oracle SQL查询：获取近24小时每小时记录数，无记录时显示0

要解决“显示近24小时所有小时（含无记录的小时，计为0）”的问题，核心思路是先生成一个包含所有目标时间区间的完整列表，再和你的交易日志表做左连接统计数量。这样即使某个区间没有记录，也会在结果中显示并计为0。

情况1：按每小时统计（匹配你描述的需求） #

下面是针对Oracle数据库的修改后SQL：

WITH hourly_times AS (
  -- 生成近24小时内的每个整点时间（从当前整点往前推24小时）
  SELECT TRUNC(SYSDATE, 'HH24') - LEVEL + 1 AS hour_start
  FROM dual
  CONNECT BY LEVEL <= 24
)
SELECT 
  ht.hour_start AS date1,
  COUNT(t.systemdate) AS txncount  -- 用COUNT(字段)而非COUNT(*)，避免统计左连接后的NULL行
FROM hourly_times ht
LEFT JOIN transactionlog t
  ON TRUNC(t.systemdate, 'HH24') = ht.hour_start
GROUP BY ht.hour_start
ORDER BY ht.hour_start DESC;

情况2：按每10分钟统计（匹配你原SQL的分组逻辑） #

如果你的实际需求是按10分钟间隔统计（原SQL是这么分组的），可以用这个版本：

WITH ten_min_intervals AS (
  -- 生成近24小时内的所有10分钟起始区间（共24*6=144个）
  SELECT TRUNC(SYSDATE, 'HH24') - (LEVEL - 1)*10/24/60 AS interval_start
  FROM dual
  CONNECT BY LEVEL <= 24*6
)
SELECT 
  tmi.interval_start AS date1,
  COUNT(t.systemdate) AS txncount
FROM ten_min_intervals tmi
LEFT JOIN transactionlog t
  ON TRUNC(t.systemdate, 'HH24') + (TRUNC(TO_CHAR(t.systemdate, 'MI')/10)*10)/24/60 = tmi.interval_start
GROUP BY tmi.interval_start
ORDER BY tmi.interval_start DESC;

关键说明 #

• 时间序列生成：用CONNECT BY生成连续的时间区间，确保每个需要的小时/10分钟都被包含，不管有没有交易记录。
• 左连接的作用：LEFT JOIN会保留左侧时间序列的所有行，即使右侧交易日志没有匹配的记录，此时t.systemdate为NULL。
• COUNT的正确用法：用COUNT(t.systemdate)而不是COUNT(*)，因为COUNT(*)会统计所有行（包括NULL行），而COUNT(字段)会忽略NULL值，从而得到0的正确统计结果。

如果需要严格从当前时间往前推24小时（而非从当前整点开始），可以调整时间序列的生成逻辑，比如把TRUNC(SYSDATE, 'HH24')换成SYSDATE - 24/24，再按小时/10分钟递增生成区间。

内容的提问来源于stack exchange，提问作者Sana.91