10-330 Siteごと,全Site,全Site(Japanは除く)で集計

SQLパズル

prodLogテーブル
Site    product  amount
------  -------  ------
Europe     1111      10
Europe     2222      20
Europe     2222      25
USA        1111      30
USA        3333      40
Japan      1111      50
Japan      2222      60

Siteごとのユニークなproduct数とamountの合計
全Siteのユニークなproduct数とamountの合計
全Site(Japanは除く)のユニークなproduct数とamountの合計
を求める。

出力結果
Site                 UniPro  Sum_Amo
-------------------  ------  -------
Europe                    2       55
Japan                     2      110
USA                       2       70
all sites                 3      235
all sites w/o Japan       3      125

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

データ作成スクリプト

create table prodLog(Site,product,amount) as
select 'Europe',1,10 from dual union all
select 'Europe',2,20 from dual union all
select 'Europe',2,25 from dual union all
select 'USA'   ,1,30 from dual union all
select 'USA'   ,3,40 from dual union all
select 'Japan' ,1,50 from dual union all
select 'Japan' ,2,60 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select *
  from prodLog
 model return updated rows
dimension by(cast(Site as varchar2(30)) as Site,RowNum as rn)
measures(product,amount,0 as UniPro,0 as Sum_Amo)
rules(
upsert all UniPro[any,0]= count(distinct product)[cv(),any],
upsert all Sum_Amo[any,0] = sum(amount)[cv(),any],
UniPro['all sites',0] = count(distinct product)[any,rn > 0],
UniPro['all sites w/o Japan',0] = count(distinct product)[Site!='Japan',rn > 0],
Sum_Amo['all sites',0] = sum(amount)[any,rn > 0],
Sum_Amo['all sites w/o Japan',0] = sum(amount)[Site!='Japan',rn > 0])
order by Site;

--■■■grouping setsを使う方法■■■
select case grouping_ID((decode(Site,'Japan',1)),(Site))
       when 2+1 then 'all sites'
       when 0+1 then 'all sites w/o Japan'
       else Site end as Site,
count(distinct PRODUCT) as UniPro,sum(AMOUNT) as Sum_Amo
  from prodLog
group by grouping sets ((),(decode(Site,'Japan',1)),(Site))
having decode(Site,'Japan',1) is null
order by Site;

解説

model句を使う方法では、
rules句の最初に、upsert allによる行の追加を行わないといけないようです。

---