PostgreSQL按日期统计：如何展示无数据日期的0值统计结果

要实现包含无数据日期且统计值为0的结果，核心是先生成目标时间段的连续日期序列，再与业务表左连接，具体操作如下：

1. 生成连续日期序列 #

用PostgreSQL的generate_series函数生成你需要统计的所有日期，比如要统计2024年1月1日到1月10日的日期：

SELECT generate_series('2024-01-01'::date, '2024-01-10'::date, '1 day'::interval) AS day_date

2. 左连接业务表并统计 #

将生成的日期序列作为主表，左连接你的public.surmise表，关联条件匹配日期；用COUNT(具体字段)而非COUNT(*)，因为COUNT(*)会把左连接产生的NULL行也算入，而COUNT(字段)只会统计非NULL的有效数据，无数据时自然返回0。

完整SQL示例：

WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::date,  -- 替换为你的统计起始日期
        '2024-01-10'::date,  -- 替换为你的统计结束日期
        '1 day'::interval
    ) AS day_date
)
SELECT 
    TO_CHAR(d.day_date, 'FMMonth DD') AS "Interval",
    COUNT(s.created_at) AS "Number"
FROM date_range d
LEFT JOIN public.surmise s
    ON DATE_TRUNC('day', TO_TIMESTAMP(s.created_at)) = d.day_date
GROUP BY d.day_date
ORDER BY d.day_date;

关键细节说明 #

• 如果你的created_at本身就是timestamp类型，无需to_timestamp转换，直接用DATE(s.created_at) = d.day_date作为关联条件更高效。
• TO_CHAR函数的FMMonth用来去除月份名后的默认空格，确保格式和你期望的一致（比如January 1而非January 1）。
• 替换date_range中的起止日期为你实际需要统计的时间段即可。

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