PostgreSQL按月份分组统计含缺失月份的正确实现方法

直接用生成的完整月份序列，和表数据按月份截断后关联即可，以下是修正后的查询：

SELECT 
    f.month_start AS period, 
    COUNT(e.id) AS count
FROM generate_series(
    date_trunc('month', now() - interval '1 year'), 
    date_trunc('month', now()), 
    '1 month'::interval
) AS f(month_start)
LEFT JOIN my_table AS e 
    ON date_trunc('month', e.created_at) = f.month_start
GROUP BY f.month_start
ORDER BY f.month_start DESC;

为什么之前的查询出问题？ #

• 第一个查询的关联条件e.created_at::date = f.date::date逻辑错误：generate_series按1 month生成的是每个月的第一天（比如2023-10-01），但表中created_at是具体的某一天（比如2023-10-15），两者日期完全相等的情况极少，导致几乎没有匹配，所以count全为0。
• 第二个查询没有生成完整的月份序列，只统计了表中有数据的月份，自然无法显示缺失数据的月份。

可选优化：更友好的日期格式 #

如果想让period显示为YYYY-MM的简洁格式，可以用to_char函数转换：

SELECT 
    to_char(f.month_start, 'YYYY-MM') AS period, 
    COUNT(e.id) AS count
FROM generate_series(
    date_trunc('month', now() - interval '1 year'), 
    date_trunc('month', now()), 
    '1 month'::interval
) AS f(month_start)
LEFT JOIN my_table AS e 
    ON date_trunc('month', e.created_at) = f.month_start
GROUP BY f.month_start
ORDER BY f.month_start DESC;

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