如何为包含RATIO_TO_REPORT的Oracle查询添加总计？

问题场景 #

原查询按category分组统计，使用RATIO_TO_REPORT计算各分组占比，尝试用ROLLUP添加总计行时，总计行被纳入百分比计算逻辑，导致总计行的百分比显示错误（如示例中总计行的百分比都为50%），需求是让总计行仅展示数值总和，且不影响分组行的占比计算。

原查询代码：

select category, sum(selected_widgets) sum_selected_widgets, to_char(round(RATIO_TO_REPORT(sum(selected_widgets)) over (),4)*100,'999d99') || '%' percentage_selected, 
       count(*) widget_count, to_char(round(RATIO_TO_REPORT(count(*)) over (),4)*100,'999d99') || '%' percentage_all_widgets
from
(
  select widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0) selected_widgets, min(category) category
  from widget
  inner join widget_detail wd on wd.widget_id = widget_id and wd.widget_seq = widget_seq and category is not null
  where widget_dx_date between '2018    ' and '20219999'
  group by widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0)
)iq
group by category
order by 1;

添加ROLLUP后的错误结果：

0   6886       3.20%    77871      5.32%
1   40689     18.91%    256538    17.54%
2   8898       4.13%    43615      2.98%
3   9817       4.56%    52809      3.61%
4   5087       2.36%    24683      1.69%
7   27397     12.73%    169669    11.60%
8   4451       2.07%    24148      1.65%
9   4371       2.03%    82084      5.61%
    107596    50.00%    731417    50.00%

解决方案 #

核心思路是先完成分组及占比计算（基于分组数据的总和），再单独计算总计行并通过UNION ALL合并结果，避免总计行干扰占比计算逻辑。

修改后的查询代码：

-- 第一步：计算分组数据及正确占比
select 
    category,
    sum_selected_widgets,
    percentage_selected,
    widget_count,
    percentage_all_widgets
from (
    select 
        category,
        sum(selected_widgets) sum_selected_widgets,
        to_char(round(RATIO_TO_REPORT(sum(selected_widgets)) over (),4)*100,'999d99') || '%' percentage_selected,
        count(*) widget_count,
        to_char(round(RATIO_TO_REPORT(count(*)) over (),4)*100,'999d99') || '%' percentage_all_widgets,
        1 as sort_order -- 用于排序，让分组行在总计行前面
    from (
        select 
            widget_id, 
            widget_seq, 
            decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0) selected_widgets, 
            min(category) category
        from widget
        inner join widget_detail wd 
            on wd.widget_id = widget.widget_id -- 修正原查询连接条件的歧义
            and wd.widget_seq = widget.widget_seq 
            and category is not null
        where widget_dx_date between '2018    ' and '20219999'
        group by widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0)
    ) iq
    group by category
) grouped_data
union all
-- 第二步：计算总计行
select 
    null as category,
    sum(sum_selected_widgets) as sum_selected_widgets,
    '100.00%' as percentage_selected, -- 总计占比可设为100%，或留空根据需求调整
    sum(widget_count) as widget_count,
    '100.00%' as percentage_all_widgets,
    2 as sort_order
from (
    select 
        category,
        sum(selected_widgets) sum_selected_widgets,
        count(*) widget_count
    from (
        select 
            widget_id, 
            widget_seq, 
            decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0) selected_widgets, 
            min(category) category
        from widget
        inner join widget_detail wd 
            on wd.widget_id = widget.widget_id
            and wd.widget_seq = widget.widget_seq 
            and category is not null
        where widget_dx_date between '2018    ' and '20219999'
        group by widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0)
    ) iq
    group by category
) grouped_data
order by sort_order, category;

关键说明 #

1. 占比计算独立：分组行的RATIO_TO_REPORT仅基于分组数据的总和，不受后续添加的总计行影响，确保占比准确。
2. 总计行单独计算：通过子查询先获取分组的聚合结果，再求和得到总计，避免重复计算底层数据（也可通过CTE优化，减少代码冗余）。
3. 排序控制：添加sort_order字段，确保分组行始终显示在总计行之前，category排序保持原逻辑。
4. 连接条件修正：原查询中wd.widget_id = widget_id存在歧义，修改为wd.widget_id = widget.widget_id明确关联字段，避免潜在错误。

若希望总计行的百分比字段留空而非显示100%，只需将对应字段替换为null即可。

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