如何结合GROUP BY ROLLUP与分析函数解决利润占父级百分比重复计算问题

问题根源 #

ROLLUP生成的汇总行（分组字段为NULL的行）会被默认纳入分析函数的分区计算范围，导致重复统计：比如你按category分区求和时，每个分类下的分类汇总行（year为NULL） 也会被算入该分类的分区总和，相当于在各年份利润之和的基础上又叠加了一次分类汇总值，最终结果自然偏高。

解决方案 #

用GROUPING()函数标识ROLLUP生成的不同汇总层级，计算分析函数时只纳入对应层级的行即可。GROUPING()返回1表示当前行是ROLLUP生成的汇总行，返回0表示是原始分组的明细行。

下面是计算「占父级百分比」的正确SQL：

with SalesX as (
    select 'Office Supplies' Category , 2014 Year,22593.42 Profit UNION all
    select 'Technology', 2014, 21492.83 UNION all
    select 'Furniture', 2014,   5457.73 UNION all
    select 'Office Supplies',   2015,   25099.53  UNION all
    select 'Technology',    2015,   33503.87  UNION all
    select 'Furniture', 2015,   50000.00  UNION all
    select 'Office Supplies',   2016,   35061.23  UNION all
    select 'Technology',    2016,   39773.99  UNION all
    select 'Furniture', 2016,   6959.95
) 
select 
    category, 
    year, 
    Profit,
    -- 父级总利润
    case 
        -- 单分类单年份行的父级是对应分类的汇总利润
        when grp_cat = 0 and grp_year = 0 then sum(if(grp_year=1,Profit,null)) over(partition by category)
        -- 单分类汇总行的父级是全量总利润
        when grp_cat = 0 and grp_year = 1 then sum(if(grp_cat=1,Profit,null)) over()
        -- 全量汇总行无父级，直接取自身值
        else Profit
    end as parent_profit,
    -- 占父级百分比
    concat(round(Profit / case 
        when grp_cat = 0 and grp_year = 0 then sum(if(grp_year=1,Profit,null)) over(partition by category)
        when grp_cat = 0 and grp_year = 1 then sum(if(grp_cat=1,Profit,null)) over()
        else Profit
    end * 100,2),'%') as parent_ratio
from (
SELECT
    category,
    year,
    SUM(profit) Profit,
    GROUPING(category) grp_cat,
    GROUPING(year) grp_year
FROM SalesX 
group by rollup(category, year) 
) _
order by category,year

逻辑说明 #

我们通过两个GROUPING返回值把行分为三个层级，按需匹配父级聚合逻辑：

• grp_cat=0、grp_year=0：具体分类+具体年份的明细聚合行，父级为所属分类的年度总利润
• grp_cat=0、grp_year=1：单个分类的年度总汇总行，父级为全品类全年度总利润
• grp_cat=1、grp_year=1：全量总汇总行，占比默认为100%

计算分析函数时通过if条件过滤掉非对应层级的行，完全避免了ROLLUP汇总行导致的重复计算问题。

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