PostgreSQL SQL查询如何填补messages表小时状态统计的日期空白？

你需要先生成覆盖统计周期的完整小时序列，再和所有可能的状态值做笛卡尔积得到全量组合，最后左关联原有统计结果补0即可，以下是适配PostgreSQL语法的修改后查询：

WITH all_hours AS (
    -- 生成过去1天的所有整点小时序列
    SELECT generate_series(
        date_trunc('hour', current_timestamp - interval '1 day'),
        date_trunc('hour', current_timestamp),
        interval '1 hour'
    ) AS hour_created
),
all_statuses AS (
    -- 枚举所有需要统计的状态值
    SELECT unnest(ARRAY['sent', 'queued', 'failed']) AS status
),
original_stats AS (
    -- 原有统计逻辑
    SELECT 
        date_trunc('hour', created_at) as hour_created, 
        count(created_at) as cnt, 
        status 
    FROM messages 
    WHERE created_at >= current_timestamp - interval '1 day' 
    GROUP BY status, hour_created
)
-- 全量组合左关联统计结果，空值补0
SELECT 
    a.hour_created,
    COALESCE(o.cnt, 0) AS count,
    s.status
FROM all_hours a
CROSS JOIN all_statuses s
LEFT JOIN original_stats o 
    ON a.hour_created = o.hour_created 
    AND s.status = o.status
ORDER BY a.hour_created, s.status;

逻辑说明 #

• all_hours 公共表表达式用generate_series生成过去24小时每个整点的时间戳，覆盖所有需要统计的时间区间，消除小时空白
• all_statuses 枚举所有需要统计的状态，确保每个小时都有三个状态的对应记录
• 用交叉连接得到「小时*状态」的全量组合，再左关联原有统计结果，通过COALESCE将没有匹配到的统计值转为0
• 输出结果自动按小时和状态排序，可直接用于图表绘制

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