Postgres：统计含无数据日期的每日指定状态条目数

嘿，我来帮你解决这个统计问题！要实现包含无数据日期的每日计数，核心是先生成覆盖目标时间区间的所有连续日期，再把原表的统计结果左连接到这个日期序列上——这样哪怕某天没有rejected的条目，也能显示0或者NULL。

完整查询示例（以PostgreSQL为例） #

假设你的表名叫your_table，要统计的时间范围是2023-01-01到2023-01-10，直接替换成你需要的起止日期就行：

-- 第一步：生成要统计的所有连续日期
WITH date_range AS (
    SELECT generate_series(
        TIMESTAMP WITH TIME ZONE '2023-01-01 00:00:00+00', -- 起始时间（UTC时区，可按需改）
        TIMESTAMP WITH TIME ZONE '2023-01-10 23:59:59+00', -- 结束时间
        INTERVAL '1 day'
    )::date AS stat_date
),
-- 第二步：统计每日rejected的条目数
daily_rejected AS (
    SELECT
        createdAt::date AS stat_date,
        COUNT(*) AS rejected_count
    FROM your_table
    WHERE
        state = 'rejected'
        AND createdAt BETWEEN TIMESTAMP WITH TIME ZONE '2023-01-01 00:00:00+00' AND TIMESTAMP WITH TIME ZONE '2023-01-10 23:59:59+00'
    GROUP BY createdAt::date
)
-- 第三步：左连接日期序列和统计结果，空日期填0
SELECT
    dr.stat_date,
    COALESCE(drj.rejected_count, 0) AS rejected_count -- 要NULL的话去掉COALESCE就行
FROM date_range dr
LEFT JOIN daily_rejected drj ON dr.stat_date = drj.stat_date
ORDER BY dr.stat_date;

关键细节拆解 #

• 生成连续日期：用generate_series生成你指定区间内的所有日期，这里用的是UTC时区，如果你需要按本地时区（比如东八区）统计，把时间戳改成'2023-01-01 00:00:00+08'就行，转成date后就是每日的日期。
• 统计实际数据：从原表筛选state='rejected'的记录，按日期分组计数，只保留目标区间内的数据。
• 填充空值：用LEFT JOIN把日期序列和统计结果关联，没有匹配到的日期会返回NULL，COALESCE可以把NULL换成0——如果你想要NULL的话，直接去掉这个函数就行。

时区踩坑提醒 #

如果你的业务是按本地时区日期统计（比如用户在上海，要统计当天0点到23点的数据，而不是UTC的0点），记得把createdAt转成对应时区后再取日期：

-- 转成东八区日期的写法
createdAt AT TIME ZONE 'Asia/Shanghai'::date AS stat_date

这样统计的就是本地时区的每日数据，不会出现跨时区的日期偏差。

针对你现有查询的排查方向 #

你说原有查询能返回时间序列但有问题，大概率是这几个原因：

• 没生成连续日期序列，直接分组导致空日期被过滤了；
• 时区转换错了，统计的日期和你预期的不符；
• 用了内连接而不是左连接，空日期没保留下来。

对照上面的示例调整一下，应该就能解决你的问题啦！

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