10-277 model句とgrouping sets

SQLパズル

PJTable
ID  dept  code1   code2  cost
--  ----  ------  -----  ----
 1  1111  200904  22222   300
 2  1111  200905  22222   200
 3  1111  200905  88888   600
 4  1111  200905  88888  -200
 5  2222  200904  66666   300
 6  2222  200905  66666   200
 7  2222  200905  77777   600
 8  2222  200905  77777  -200

dept,code1ごとのcostの集計行を追加する。
集計行のIDとcode2は、null
集計行のIsSumは、yes
とする。

dept,code1ごとのIDも、sumIDとして求める。

出力結果
  ID  dept  code1   code2  cost  sumID  IsSum
----  ----  ------  -----  ----  -----  -----
   1  1111  200904  22222   300      1  no
null  1111  200904  null    300      1  yes
   2  1111  200905  22222   200      2  no
   3  1111  200905  88888   600      2  no
   4  1111  200905  88888  -200      2  no
null  1111  200905  null    600      2  yes
   5  2222  200904  66666   300      3  no
null  2222  200904  null    300      3  yes
   6  2222  200905  66666   200      4  no
   7  2222  200905  77777   600      4  no
   8  2222  200905  77777  -200      4  no
null  2222  200905  null    600      4  yes

データ作成スクリプト

create table PJTable(ID,dept,code1,code2,cost) as
select 1,'1111','200904','22222', 300 from dual union
select 2,'1111','200905','22222', 200 from dual union
select 3,'1111','200905','88888', 600 from dual union
select 4,'1111','200905','88888',-200 from dual union
select 5,'2222','200904','66666', 300 from dual union
select 6,'2222','200905','66666', 200 from dual union
select 7,'2222','200905','77777', 600 from dual union
select 8,'2222','200905','77777',-200 from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select id,dept,code1,code2,cost,sumID,IsSum
  from PJTable
 model
 partition by(dept,code1,
              dense_rank() over(order by dept,code1) as sumID)
 dimension by(ID)
 measures(code2,Cost,cast('no' as varchar2(3)) as IsSum)
 rules(cost [null] = sum(cost)[any],
       IsSum[null] = 'yes')
order by dept,code1,ID;

--■■■grouping setsを使う方法■■■
select ID,dept,code1,code2,sum(cost) as cost,
dense_rank() over(order by dept,code1) as sumID,
decode(grouping(ID),1,'yes','no') as IsSum
  from PJTable
group by grouping sets((ID,dept,code1,code2),
                          (dept,code1))
order by dept,code1,ID;

--■■■複合列指定のrollupを使う方法■■■
select ID,dept,code1,code2,sum(cost) as cost,
dense_rank() over(order by dept,code1) as sumID,
decode(grouping(ID),1,'yes','no') as IsSum
  from PJTable
group by dept,code1,rollup((ID,code2))
order by dept,code1,ID;

解説

grouping setsは、縦に並べて記述すると分かりやすいのです。
group by grouping sets((ID,dept,code1,code2),
                          (dept,code1))

複合列指定のrollupを使ってもいいです。
10-301 rollupで複合列(Composite Columns)指定

model句で、位置参照によるupsertを使うのも有力でしょう。
10-275 model句で集計行を追加