如何使用generate_series()按日期和类别统计指定范围的帖子数量

你的原始SQL存在两个核心问题，导致结果不符合预期：

• LEFT JOIN posts 后添加的 WHERE date BETWEEN ... 条件会过滤掉所有未匹配到帖子的行（这类行的date字段为NULL，不满足WHERE的范围判断）；
• 关联posts表时未将生成的日期day与帖子的date字段绑定，导致统计的是该分类所有日期的帖子总数，而非对应单日的数量。

正确的SQL语句 #

SELECT 
    day::date, 
    c.code, 
    COUNT(p.id) AS count
FROM generate_series('2022-01-01'::date, '2022-01-05'::date, '1 DAY') AS day
CROSS JOIN categories c
LEFT JOIN posts p 
    ON p.category_id = c.id 
    AND p.date = day::date -- 关联当天该分类的帖子
GROUP BY day::date, c.code
ORDER BY day::date, c.code;

逻辑说明 #

1. 用generate_series生成目标日期范围内的所有日期，再与categories表做交叉连接，得到「所有日期+所有分类」的完整组合；
2. 通过LEFT JOIN关联posts表时，同时指定category_id匹配和date = day，确保只统计当天该分类的帖子；
3. 无需额外的WHERE条件，避免过滤掉无匹配的行；
4. COUNT(p.id)会自动对未匹配到帖子的行返回0，符合预期统计需求。

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