如何在Oracle PIVOT透视表中使用ROLLUP/CUBE实现行汇总加总行

解决方案 #

你可以通过ROLLUP对PIVOT后的结果分组生成汇总行，同时新增字段计算单用户的总数量和总重量，修改后的查询如下：

SELECT 
    typ,
    -- 可取消下方注释，将汇总行的用户名显示为「总计」
    -- NVL(user_name, '总计') AS user_name,
    user_name,
    "'Alcohol'_PC",
    "'Alcohol'_HM",
    "'Food'_PC",
    "'Food'_HM",
    "'NotFood'_PC",
    "'NotFood'_HM",
    "'Cigarette'_PC",
    "'Cigarette'_HM",
    -- 统计单用户总数量
    NVL("'Alcohol'_PC",0) + NVL("'Food'_PC",0) + NVL("'NotFood'_PC",0) + NVL("'Cigarette'_PC",0) AS sum_pc,
    -- 统计单用户总重量
    NVL("'Alcohol'_HM",0) + NVL("'Food'_HM",0) + NVL("'NotFood'_HM",0) + NVL("'Cigarette'_HM",0) AS sum_weight
FROM (
    SELECT
        typ,
        user_name,
        sort,
        count_pc,
        weight    
    FROM stat 
    WHERE typ=1 AND dat >= trunc(sysdate)
)
PIVOT (
    SUM(to_number(count_pc)) AS pc, 
    SUM(to_number(round(weight,0))) AS hm
    FOR sort IN ('Alcohol','Food','NotFood' ,'Cigarette' )
)
-- ROLLUP按typ、user_name生成层级汇总
GROUP BY ROLLUP(typ, user_name), "'Alcohol'_PC", "'Alcohol'_HM", "'Food'_PC", "'Food'_HM", "'NotFood'_PC", "'NotFood'_HM", "'Cigarette'_PC", "'Cigarette'_HM"
ORDER BY 
    -- 控制明细行在前，汇总行在后
    GROUPING(user_name), 
    user_name ASC,
    GROUPING(typ)

补充说明

• 如果不需要单用户维度的汇总，只需要全局汇总行，可将ROLLUP(typ, user_name)改为ROLLUP((typ, user_name))，将两个字段作为整体聚合即可跳过中间层级的汇总
• GROUPING()函数返回值为1时代表当前行是对应字段的汇总行，返回0为明细行，可用来灵活控制排序和显示逻辑

内容的提问来源于stack exchange，提问作者Know-nothing