PostgreSQL按交易分类分组生成各月度金额汇总列的实现方法

核心是通过条件聚合实现单表一次扫描完成所有月份的统计，无需多次查询单月数据再做关联拼接，执行效率更高。

具体改动步骤 #

• 放宽时间筛选范围
  原查询仅将时间限制在1月区间，要覆盖所有统计月份，首先需要把WHERE条件里的时间范围扩大到你需要统计的全部时间跨度，原有的分类排除规则可以保留不变。
• 改造聚合逻辑
  在SUM求和函数内部，通过CASE WHEN判断交易所属的年月，仅累加匹配目标月份的金额，其余场景计为0，即可为每个月份生成独立的统计列。

参考实现代码 #

以统计2021年全年12个月份的交易数据为例，完整查询如下：

SELECT 
    category,
    -- 1月总金额
    SUM(CASE WHEN date_trunc('month', date) = '2021-01-01' THEN amount ELSE 0 END) AS january_total,
    -- 2月总金额
    SUM(CASE WHEN date_trunc('month', date) = '2021-02-01' THEN amount ELSE 0 END) AS february_total,
    -- 3月总金额
    SUM(CASE WHEN date_trunc('month', date) = '2021-03-01' THEN amount ELSE 0 END) AS march_total,
    -- 后续4-12月按照相同规则依次补充列即可
    SUM(CASE WHEN date_trunc('month', date) = '2021-12-01' THEN amount ELSE 0 END) AS december_total
FROM transactions
WHERE 
    -- 保留原有的分类排除规则
    category NOT IN ('Transfer', 'Payment', 'Deposit', 'Income')
    -- 调整为全量统计时间范围，替换为实际需要的统计区间
    AND date >= '2021-01-01' 
    AND date < '2022-01-01'
GROUP BY category
-- 排序规则可根据需求调整
ORDER BY january_total ASC

注意事项 #

• 跨多年统计时，不要仅用extract(month from date)判断月份，否则不同年份的同月数据会被错误合并到同一列，使用date_trunc('month', date)匹配完整年月值可以规避这个问题
• 若统计的月份范围不固定，不想手动编写每个月的CASE WHEN规则，可以通过动态SQL先查询时间范围内所有不重复的年月值，自动拼接生成包含所有月份列的查询语句执行，减少手动维护的成本
• 该写法无需安装额外的数据库扩展，兼容所有支持CASE WHEN语法的数据库，相比专用的行转列工具，单表一次扫描完成全量统计的性能表现更优

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