10-275 model句で集計行を追加

SQLパズル

amountT
ID  Prod
--  -------
 1  xprod_a
 1  xprod_a
 1  xprod_a
 1  xprod_b
 1  xprod_c
 1  xprod_d
 2  xprod_1
 2  xprod_2
 2  xprod_2
 3  xprod_x
 3  xprod_x
 3  xprod_y
 3  xprod_z

IDごと、Prodごとの行数を求め、行数の累計を求める。
そして、IDごとの集計行として、行数の総合計と、行数の累計の総合計を求める。

出力結果
  ID  Prod     cnt  runSum
----  -------  ---  ------
   1  xprod_a    3       3
   1  xprod_b    1       4
   1  xprod_c    1       5
   1  xprod_d    1       6
null  TOTAL      6      18
   2  xprod_1    1       1
   2  xprod_2    2       3
null  TOTAL      3       4
   3  xprod_x    2       2
   3  xprod_y    1       3
   3  xprod_z    1       4
null  TOTAL      4       9

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

データ作成スクリプト

create table amountT(ID,Prod) as
select 1,'xprod_a' from dual union all
select 1,'xprod_a' from dual union all
select 1,'xprod_a' from dual union all
select 1,'xprod_b' from dual union all
select 1,'xprod_c' from dual union all
select 1,'xprod_d' from dual union all
select 2,'xprod_1' from dual union all
select 2,'xprod_2' from dual union all
select 2,'xprod_2' from dual union all
select 3,'xprod_x' from dual union all
select 3,'xprod_x' from dual union all
select 3,'xprod_y' from dual union all
select 3,'xprod_z' from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select decode(isTotal,0,copyID) as ID,Prod,cnt,runSum
  from amountT
group by ID,Prod
 model
partition by(ID as copyID)
dimension by(Prod)
measures(count(*) as cnt,
         sum(count(*)) over(partition by ID order by Prod) as runSum,
         0 as isTotal)
rules(cnt    ['TOTAL'] = sum(cnt)   [any],
      runSum ['TOTAL'] = sum(runSum)[any],
      isTotal['TOTAL'] = 1)
order by copyID,isTotal,Prod;

--■■■RollUpを使う方法■■■
select decode(gp,0,copyID) as ID,
decode(gp,0,Prod,'TOTAL') as Prod,cnt,runSum
from (select grouping(Prod) as gp,copyID,Prod,
      sum(cnt) as cnt,sum(runSum) as runSum
      from (select ID as copyID,Prod,count(*) as cnt,
            sum(count(*)) over(partition by ID order by Prod) as runSum
              from amountT
            group by ID,Prod)
      group by copyID,rollup(Prod))
order by copyID,gp;

解説

SumKey1とSumKey2で集計し、
SumKey1でも集計しますので、
group by SumKey1,rollup(SumKey2)
で集計できます。

SumKey1とSumKey2とSumKey3で集計し、
SumKey1でも集計といった場合は、
group by grouping sets((SumKey1),(SumKey1,SumKey2,SumKey3))
で集計できます。

しかし、model句を使ったほうが分かりやすいでしょう。
model句08 having句の次にmodel句が評価される

10-277 model句とgrouping sets

---