如何在Oracle透视表查询中添加行小计/总计及列总计？

问题背景 #

原SQL查询通过CUBE和GROUPING_ID实现了包含各status的user_type统计行、指定status的小计行（subtotal）及全局总计行（total）：

SELECT CASE GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END)
       WHEN 0
       THEN TO_CHAR(status)
       WHEN 2
       THEN 'subtotal'
       ELSE 'total'
       END AS status,
       COUNT(CASE user_type WHEN 1 THEN 1 END) AS "1",
       COUNT(CASE user_type WHEN 2 THEN 1 END) AS "2",
       COUNT(CASE user_type WHEN 3 THEN 1 END) AS "3",
       COUNT(CASE user_type WHEN 5 THEN 1 END) AS "5",
       COUNT(*) AS total
FROM   (Select STATUS,USER_TYPE
FROM TRANSACTIONS tr  join TRANSACTION_STATUS_CODES sc on sc.id = tr.user_type join
TRANSACTION_USER_TYPES ut on ut.id=tr.user_type  
WHERE tr.status!=1 AND Tr.Update_Date BETWEEN     TO_DATE('2022-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')     AND TO_DATE('2022-11-13 23:59:59', 'yyyy-mm-dd HH24:MI:SS'))
GROUP BY CUBE(status,CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END)
HAVING GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END) IN (0, 3)
OR     (   GROUPING_ID(status, CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END) = 2
       AND CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END = 1 );

改写为PIVOT查询后，虽然结构更清晰，但无法实现小计、总计功能，且作为子查询时外部无法识别动态生成的列：

SELECT p.*
FROM   (
  SELECT user_type,
         status 
  FROM   transactions
  WHERE  status !=1
  AND    Update_Date >= DATE '2022-01-01'
  AND    Update_Date <  DATE '2022-11-14' 
) PIVOT (
  COUNT(*)
  FOR user_type IN (1,2,3,5)
) p
ORDER BY status asc;

解答 #

能否不改动PIVOT整体架构实现需求？ #

不能。PIVOT的本质是先聚合再转置，输出的是按status分组后的固定列结果，没有保留生成小计、总计所需的分组标识信息；同时PIVOT生成的列（1、2、3、5）属于动态列，外部查询无法直接引用这些列进行二次聚合，因此无法在现有PIVOT架构下实现原需求的小计、总计功能。

最优实现方案 #

将分组标识、聚合逻辑与PIVOT结合，先通过CUBE完成基础聚合（包含各status、小计、总计的分组数据），再用PIVOT转置user_type列，既保留PIVOT的可读性，又实现原需求的统计功能。

完整SQL代码如下：

SELECT 
  CASE 
    WHEN grouping_id(status, group_flag) = 0 THEN TO_CHAR(status)
    WHEN grouping_id(status, group_flag) = 2 THEN 'subtotal'
    ELSE 'total'
  END AS status,
  "1", "2", "3", "5",
  ("1" + "2" + "3" + "5") AS total
FROM (
  -- 先聚合各分组的user_type计数，同时保留分组标识
  SELECT 
    status,
    group_flag,
    user_type,
    COUNT(*) AS cnt
  FROM (
    SELECT 
      status,
      user_type,
      -- 用于判断小计的分组标识：status在(2,4,5)为1，否则0
      CASE WHEN status IN (2, 4, 5) THEN 1 ELSE 0 END AS group_flag
    FROM transactions tr
    JOIN TRANSACTION_STATUS_CODES sc ON sc.id = tr.user_type
    JOIN TRANSACTION_USER_TYPES ut ON ut.id = tr.user_type
    WHERE tr.status != 1
      AND tr.Update_Date >= DATE '2022-01-01'
      AND tr.Update_Date < DATE '2022-11-14'
  ) t
  GROUP BY CUBE(status, group_flag, user_type)
  -- 过滤出需要的分组：单个status、小计（group_flag=1且status被聚合）、总计
  HAVING grouping_id(status, group_flag, user_type) IN (0, 3, 6)
    OR (grouping_id(status, group_flag, user_type) = 2 AND group_flag = 1)
) PIVOT (
  SUM(cnt) -- 对聚合后的cnt求和，得到各user_type的计数
  FOR user_type IN (1 AS "1", 2 AS "2", 3 AS "3", 5 AS "5")
)
ORDER BY 
  -- 排序：先单个status，再subtotal，最后total
  CASE 
    WHEN status = 'total' THEN 3
    WHEN status = 'subtotal' THEN 2
    ELSE 1
  END,
  status;

代码说明 #

1. 分组标识group_flag：在最内层子查询中定义，用于区分需要生成小计的status组。
2. CUBE聚合：对status、group_flag、user_type做CUBE聚合，生成单个status的统计、小计（group_flag=1的status组聚合）、全局总计的基础数据。
3. HAVING过滤：通过GROUPING_ID筛选出需要的行，避免多余的中间聚合结果。
4. PIVOT转置：将user_type的行数据转置为列，实现清晰的列展示。
5. 排序逻辑：确保结果按单个status→subtotal→total的顺序输出。

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