10-171 grouping setsに式を指定

SQLパズル

kingakuTable
CD      Kingaku
------  -------
101011        1
101012        2
102011        4
201011        8
202022       16

CDの先頭2バイトごと
CDの先頭4バイトごと
CDごと
でKingakuの合計を求め、以下の形式で出力する。

出力結果
CD      Kingaku
------  -------
100000        7  ← 1+2+4
101000        3  ← 1+2
101011        1
101012        2
102000        4
102011        4
200000       24  ← 8+16
201000        8
201011        8
202000       16
202022       16

データ作成スクリプト

create table kingakuTable as
select '101011' as CD, 1 as kingaku from dual
union select '101012', 2 from dual
union select '102011', 4 from dual
union select '201011', 8 from dual
union select '202022',16 from dual;

SQL

--■■■インラインビューを使わない方法■■■
select coalesce(CD,RPad(substr(CD,1,2),6,'0'),RPad(substr(CD,1,4),6,'0')) as CD,
sum(kingaku) as kingaku
  from kingakuTable
group by grouping sets(CD,RPad(substr(CD,1,2),6,'0'),RPad(substr(CD,1,4),6,'0'))
order by CD;

--■■■インラインビューを使う方法■■■
select coalesce(CD,gp2,gp4) as CD,
sum(kingaku) as kingaku
from (select RPad(substr(CD,1,2),6,'0') as gp2,
             RPad(substr(CD,1,4),6,'0') as gp4,
             CD,kingaku
        from kingakuTable)
group by grouping sets(CD,gp2,gp4)
order by CD;

--■■■grouping関数を使う方法■■■
select
case when grouping(CD) = 0 then CD
     when grouping(RPad(substr(CD,1,2),6,'0')) = 0 then RPad(substr(CD,1,2),6,'0')
     when grouping(RPad(substr(CD,1,4),6,'0')) = 0 then RPad(substr(CD,1,4),6,'0')
     end as CD,
sum(kingaku) as kingaku
  from kingakuTable
group by grouping sets(CD,RPad(substr(CD,1,2),6,'0'),RPad(substr(CD,1,4),6,'0'))
order by CD;

解説

10.1.0.2.0だと、インラインビューを使う方法しか正常に動作しないようです。
10.2.0.1.0だと、全て正常に動作しました。

3-29 grouping setsで総合計を取得

---