10-156 総合計に対する比率と総合計を取得

SQLパズル

myTable
ID  sal
--  ---
 1   50
 2  100
 3  150
 4  600

総合計に対する比率(小数3位を切り捨て)と
総合計を取得する。

出力結果
   ID  sal  percentage
-----  ---  ----------
    1   50        5.55
    2  100       11.11
    3  150       16.66
    4  600       66.66
total  900      100.00

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table myTable(ID,sal) as
select 1, 50 from dual union all
select 2,100 from dual union all
select 3,150 from dual union all
select 4,600 from dual;

SQL

col ID for a8

--■■■rollupを使う方法1■■■
select case when grouping(ID) = 1 then 'total'
            else to_char(ID) end as ID,
sum(sal) as sal,
to_char(trunc(100 * Ratio_To_Report(sum(sal))
                    over(partition by grouping(ID))
           ,2),'999.99') as percentage
from myTable
group by rollup(ID)
order by ID;

--■■■rollupを使う方法2■■■
select case when grouping(ID) = 1 then 'total'
            else to_char(ID) end as ID,
sum(sal) as sal,
to_char(trunc(200 * Ratio_To_Report(sum(sal)) over()
           ,2),'999.99') as percentage
from myTable
group by rollup(ID)
order by ID;

--■■■model句を使う方法(10g以降)■■■
select ID,sal,percentage
  from myTable
 model
 dimension by(to_char(ID) as ID)
 measures(sal,to_char(trunc(100 * Ratio_To_Report(sal) over()
                         ,2),'999.99') as percentage)
rules(sal['total'] = sum(sal)[any],
      percentage['total'] = ' 100.00');

解説

grouping関数の値で、分割してます。

■■■■■■■■■■■■■■■■■■■■■■■■

数学やSQLなどでは、
100*A
と
A*100
は、同じ意味ですが
100*Aと記述したほうが分かりやすいことが多いです。

100 * Ratio_To_Report(sum(sal)) over(partition by grouping(ID))
のほうが
Ratio_To_Report(sum(sal)) over(partition by grouping(ID)) * 100
より分かりやすいということです。

4-11 総合計に対する比率を取得

---

マニュアル(RATIO_TO_REPORT関数)
マニュアル(RATIO_TO_REPORT関数)(英語)

---