如何在SQL中将时间段数据按列拆分并解决GROUP BY相关报错问题

报错根因 #

• 你将非聚合字段table2.date_of_transaction放在了SUM()聚合函数外部，该字段未出现在GROUP BY子句中，违反了SQL分组聚合的语法规则
• 原代码额外存在的问题：关联条件误写为table.id实际应为table1.id、第三营收列别名重复、交易日期未做月份截断可能导致匹配失败、时间维度实际为月份但别名标注为周

修正后查询代码 #

WITH time_frame AS
  (SELECT date_trunc('month',NOW()) AS mon),
  time_frame1 AS
  (SELECT date_trunc('month',NOW())-interval '1 month' AS mon),
  time_frame2 AS
  (SELECT date_trunc('month',NOW())-interval '2 month' AS mon)

SELECT 
  table1.company_name,
  SUM(CASE WHEN date_trunc('month', table2.date_of_transaction) = (SELECT mon FROM time_frame2) THEN table2.amount ELSE 0 END) AS "上上月营收",
  SUM(CASE WHEN date_trunc('month', table2.date_of_transaction) = (SELECT mon FROM time_frame1) THEN table2.amount ELSE 0 END) AS "上月营收",
  SUM(CASE WHEN date_trunc('month', table2.date_of_transaction) = (SELECT mon FROM time_frame) THEN table2.amount ELSE 0 END) AS "当月营收"
FROM table1
JOIN table2 ON table2.table1_id = table1.id
WHERE table1.company_joined >= '2019-04-20'
GROUP BY 1

实现说明 #

• 采用条件聚合逻辑，将日期判断放在SUM()内部，所有非分组字段都被聚合函数包裹，符合GROUP BY语法要求
• 对交易日期统一做月份截断处理，避免交易日期携带时分秒导致和统计月份匹配失败
• 修正了原代码的关联条件错误、别名重复问题，日期格式调整为数据库通用的YYYY-MM-DD标准格式
• 如果需要展示空值而不是0，可以将ELSE 0修改为ELSE NULL

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