如何生成含无订单时段的每周指定日每小时订单统计指标？

我懂你的需求啦——你需要统计一周内指定某天（比如示例里的周三）每一小时的订单创建数量，哪怕某个小时没有订单生成，也要把该时段的count显示为0，而不是直接跳过这个时段。

当前SQL的问题 #

你现在的查询是从订单表中筛选出周三的记录，再按小时分组统计，但如果某个小时没有订单，这个分组就不会出现在结果里，所以会缺失像02:00、04:00这样的时段。

解决方案：生成完整小时序列+左连接 #

要搞定这个问题，核心思路是先生成一个包含一天24小时的完整时间序列，再和你的订单统计结果做左连接，这样就能保证所有小时都被展示，无订单的时段用COALESCE把null转为0。

完整的SQL代码如下：

SELECT 
  COALESCE(order_counts.count, 0) AS count,
  hours.hour AS "time"
FROM 
  -- 生成00:00到23:00的完整小时序列
  generate_series('00:00:00'::time, '23:00:00'::time, '1 hour'::interval) AS hours(hour)
LEFT JOIN 
  -- 原订单统计逻辑封装为子查询
  (
    SELECT 
      COUNT(id) AS count,
      DATE_TRUNC('hour', created_at AT TIME ZONE 'US/Pacific')::time AS "time"
    FROM "order"
    -- 先转换时区再判断星期几，避免时区导致的日期偏差
    WHERE TO_CHAR(created_at AT TIME ZONE 'US/Pacific', 'day') LIKE '%wed%'
    GROUP BY DATE_TRUNC('hour', created_at AT TIME ZONE 'US/Pacific')::time
  ) AS order_counts ON hours.hour = order_counts."time"
ORDER BY hours.hour;

关键细节说明 #

• generate_series：这是PostgreSQL里生成连续序列的工具函数，这里用它生成了一天24小时的所有时间点，确保每个时段都有对应的行。
• LEFT JOIN：保证即使订单统计子查询中没有某个小时的记录，小时序列里的该行依然会被保留，对应的count会是null。
• COALESCE：把null值替换为0，完美贴合你需要的无订单时段显示0的要求。
• 时区修正：原SQL里直接对created_at判断星期几，可能因为时区转换导致日期出错，所以先把created_at转换为US/Pacific时区后再判断星期几，确保统计的是该时区下的周三订单。

执行后得到的结果 #

count | time
------+------------
6     | 00:00:00
3     | 01:00:00
0     | 02:00:00
4     | 03:00:00
0     | 04:00:00
5     | 05:00:00
...
-- 后续会包含06:00到23:00的所有时段，无订单则显示0

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