如何为特定条件下的SQL透视表添加行总计与分组汇总行

原查询语句 #

select pivot_table.*
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.User_Type between 1 and 5
  And    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')
) t
pivot(
  count(user_type)
  FOR user_type IN (1,2,3,5) 
)  pivot_table;

原查询结果 #

期望结果 #

尝试过的错误写法 #

曾尝试在子查询中添加统计字段，再修改透视逻辑，但未达预期：

-- 子查询修改部分
Select STATUS,USER_TYPE,
       count(user_type) as records,
       sum(user_type) over (partition by status) as total

-- 透视部分修改
pivot ( sum (records) for user_type in (1,2,3,5)) pivot_table

正确解决方案 #

通过先完成基础透视，再用UNION ALL拼接小计行和总计行，同时计算每行的total列：

WITH pivot_data AS (
  select 
    status,
    "1", "2", "3", "5",
    "1" + "2" + "3" + "5" 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.User_Type between 1 and 5
    And    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')
  ) t
  pivot(
    count(user_type)
    FOR user_type IN (1 as "1",2 as "2",3 as "3",5 as "5") 
  )
)
-- 原始状态行
SELECT TO_CHAR(status) as status, "1", "2", "3", "5", total FROM pivot_data
UNION ALL
-- 状态2,4,5的小计行
SELECT 'sum of statuses 2,4,5' as status,
       SUM("1"), SUM("2"), SUM("3"), SUM("5"), SUM(total)
FROM pivot_data WHERE status IN (2,4,5)
UNION ALL
-- 所有状态的总计行
SELECT 'sum of all statuses' as status,
       SUM("1"), SUM("2"), SUM("3"), SUM("5"), SUM(total)
FROM pivot_data
ORDER BY 
  CASE status 
    WHEN '2' THEN 1
    WHEN '4' THEN 2
    WHEN '5' THEN 3
    WHEN '3' THEN 4
    WHEN '0' THEN 5
    WHEN 'sum of statuses 2,4,5' THEN 6
    WHEN 'sum of all statuses' THEN 7
  END;

逻辑说明 #

1. CTEpivot_data：先完成基础透视，同时计算每行的total（各user_type计数之和）。
2. UNION ALL拼接：
   • 第一部分：输出原始的各状态行；
   • 第二部分：筛选状态2、4、5，聚合得到小计；
   • 第三部分：聚合所有行得到总计；
3. ORDER BY排序：通过CASE语句指定行的顺序，保证结果和期望一致。

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