PostgreSQL单年分月SUM统计补零及多场景通用查询实现

原查询仅返回有消费记录的月份，是因为GROUP BY仅会聚合表中存在的记录，没有对应月份的消费数据就不会生成对应分组。解决思路是先生成完整的1-12月序列作为基础表，再和消费数据做左关联：

WITH full_months AS (
    -- 生成1-12月的完整月份序列
    SELECT generate_series(1, 12) AS month_num
)
SELECT
    fm.month_num AS month,
    COALESCE(SUM(it.price), 0) AS monthly_total
FROM full_months fm
LEFT JOIN item_table it
    ON EXTRACT(MONTH FROM it.date) = fm.month_num
    -- 固定2020年、用户1、分类3的过滤条件
    AND EXTRACT(YEAR FROM it.date) = 2020
    AND it.user_id = 1
    AND it.category_id = 3
GROUP BY fm.month_num
ORDER BY fm.month_num;

可以通过参数空值判断实现不同统计场景的适配，定义三个可传入参数：

• target_category_id：指定分类ID，传null代表统计全部分类
• start_date：统计起始日期，传null代表不限制起始时间
• end_date：统计结束日期，传null代表不限制结束时间

通用查询语句如下：

WITH full_months AS (
    -- 统计指定年份时用这个，生成1-12月序列
    SELECT generate_series(1, 12) AS month_num
    -- 统计全部时间时替换成下面的写法，生成表中所有存在的月份序列
    -- SELECT DISTINCT EXTRACT(MONTH FROM date) AS month_num FROM item_table ORDER BY month_num
)
SELECT
    fm.month_num AS month,
    COALESCE(SUM(it.price), 0) AS monthly_total
FROM full_months fm
LEFT JOIN item_table it
    ON EXTRACT(MONTH FROM it.date) = fm.month_num
    -- 可将user_id也改为参数实现多用户适配
    AND it.user_id = 1
WHERE
    -- 分类适配：传指定ID则过滤，传null则全选
    (:target_category_id IS NULL OR it.category_id = :target_category_id)
    -- 时间适配：传起止时间则过滤，都传null则全选
    AND (:start_date IS NULL OR :end_date IS NULL OR it.date BETWEEN :start_date AND :end_date)
GROUP BY fm.month_num
ORDER BY fm.month_num;

不同场景传参示例 #

• 全部时间+全部分类：target_category_id传null，start_date和end_date都传null
• 全部时间+指定分类：target_category_id传对应分类ID，start_date和end_date都传null
• 指定年份+全部分类：target_category_id传null，start_date传当年1月1日，end_date传当年12月31日

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