PostgreSQL分组统计查询如何新增合计行计算生成创意总数

方案1：使用GROUP BY ROLLUP（PostgreSQL原生分组汇总语法，性能最优） #

这是PostgreSQL专门用于分组后追加合计行的原生语法，只需要扫描一次表，执行效率更高：

SELECT 
  COALESCE(c.idea_sector, 'Total') AS idea_sector,
  c.idea_industry,
  COUNT(*) AS "No.of Ideas generated"
FROM hackathon2k21.consolidated_report c
GROUP BY ROLLUP (idea_sector, idea_industry)
-- 过滤掉不需要的单sector层级汇总，只保留原分组结果和全量合计行
HAVING GROUPING(idea_sector) = 1 OR (GROUPING(idea_sector) = 0 AND GROUPING(idea_industry) = 0)
ORDER BY 
  -- 保证合计行固定排在最后
  GROUPING(idea_sector),
  idea_sector,
  idea_industry;

逻辑说明：

• ROLLUP会自动生成多级汇总结果，两个分组字段的场景下会生成「sector+industry分组、单sector汇总、全量汇总」三类结果，用HAVING过滤掉不需要的中间层级即可
• GROUPING()函数返回1代表当前行该字段是汇总产生的空值，返回0代表是原始数据的分组值
• COALESCE将合计行的idea_sector空值替换为Total，匹配预期的展示效果

方案2：使用UNION ALL手动拼接合计行（逻辑更直观） #

如果不熟悉ROLLUP语法，可以手动拼接合计行，可读性更高：

-- 原分组统计逻辑
SELECT 
  c.idea_sector,
  c.idea_industry,
  COUNT(*) AS "No.of Ideas generated"
FROM hackathon2k21.consolidated_report c
GROUP BY idea_sector, idea_industry

UNION ALL

-- 手动追加合计行
SELECT 
  'Total' AS idea_sector,
  NULL AS idea_industry,
  COUNT(*) AS "No.of Ideas generated"
FROM hackathon2k21.consolidated_report c

ORDER BY 
  -- 保证合计行固定排在最后
  CASE WHEN idea_sector = 'Total' THEN 1 ELSE 0 END,
  idea_sector,
  idea_industry;

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