SAP HANA环境下按PG分组按日期求和并新增总计行的SQL实现问题

原有SQL问题说明 #

1. 聚合逻辑颠倒：将SUM放在CASE判断内部，且先按PG+EMANAGER_DATE分组后二次聚合取MAX，属于不必要的嵌套写法，容易出现统计错误。
2. 未处理空PG值：没有把为空的PG替换为要求的?。
3. 缺少总计行逻辑：没有生成最后一行的汇总记录。

适配SAP HANA的正确SQL（推荐写法，用GROUPING SETS实现） #

SELECT 
  CASE WHEN GROUPING(PG) = 1 THEN 'total' ELSE IFNULL(PG, '?') END AS PG,
  SUM(CASE WHEN EMANAGER_DATE = '2021-09' THEN NET_VALUE_USD ELSE 0 END) AS "2021-09",
  SUM(CASE WHEN EMANAGER_DATE = '2021-10' THEN NET_VALUE_USD ELSE 0 END) AS "2021-10",
  SUM(CASE WHEN EMANAGER_DATE = '2021-11' THEN NET_VALUE_USD ELSE 0 END) AS "2021-11"
FROM table1
GROUP BY GROUPING SETS ((PG), ())
ORDER BY GROUPING(PG), PG

逻辑说明

• 用IFNULL(PG, '?')把空值的PG统一替换为?输出。
• 行转列直接把CASE放在SUM内部，按PG单次聚合即可得到每个PG对应月份的金额总和，不需要嵌套CTE。
• GROUP BY GROUPING SETS ((PG), ())会同时生成按PG分组的明细行、和不分组的总计行，GROUPING(PG)=1时即为总计行，此时PG字段输出total。
• 最后的ORDER BY可以保证未知PG、正常PG、总计行的顺序符合预期。

兼容版写法（用UNION ALL实现，逻辑更直观） #

如果不习惯用GROUPING SETS，可以选这个写法，结果完全一致：

-- 各PG分组统计
SELECT 
  IFNULL(PG, '?') AS PG,
  SUM(CASE WHEN EMANAGER_DATE = '2021-09' THEN NET_VALUE_USD ELSE 0 END) AS "2021-09",
  SUM(CASE WHEN EMANAGER_DATE = '2021-10' THEN NET_VALUE_USD ELSE 0 END) AS "2021-10",
  SUM(CASE WHEN EMANAGER_DATE = '2021-11' THEN NET_VALUE_USD ELSE 0 END) AS "2021-11"
FROM table1
GROUP BY PG
UNION ALL
-- 总计行
SELECT 
  'total' AS PG,
  SUM(CASE WHEN EMANAGER_DATE = '2021-09' THEN NET_VALUE_USD ELSE 0 END) AS "2021-09",
  SUM(CASE WHEN EMANAGER_DATE = '2021-10' THEN NET_VALUE_USD ELSE 0 END) AS "2021-10",
  SUM(CASE WHEN EMANAGER_DATE = '2021-11' THEN NET_VALUE_USD ELSE 0 END) AS "2021-11"
FROM table1
ORDER BY PG = 'total', PG

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