10-319 Partitioned Outer Joinで全体の最小から最大まで補完

SQLパズル

LPOテーブル
ID  dayC         Val
--  ----------  ----
あ  2010-03-01   100
あ  2010-04-01   200
あ  2010-06-01   600
あ  2010-07-01  3000
い  2010-04-01   200
い  2010-06-01   300
い  2010-07-01   900
う  2010-04-01   700
う  2010-05-01  2000

LPOテーブルのdayCを1ヶ月単位で、全体の最小から最大まで補完し、
なおかつ、IDごとでdayCの昇順でValの累計を求める。

出力結果
ID  DAYC       Val  rumSum
--  --------  ----  ------
あ  10-03-01   100     100
あ  10-04-01   200     300
あ  10-05-01     0     300
あ  10-06-01   600     900
あ  10-07-01  3000    3900
い  10-03-01     0       0
い  10-04-01   200     200
い  10-05-01     0     200
い  10-06-01   300     500
い  10-07-01   900    1400
う  10-03-01     0       0
う  10-04-01   700     700
う  10-05-01  2000    2700
う  10-06-01     0    2700
う  10-07-01     0    2700

データ作成スクリプト

create table LPO(ID,dayC,Val) as
select 'あ',date '2010-03-01', 100 from dual union all
select 'あ',date '2010-04-01', 200 from dual union all
select 'あ',date '2010-06-01', 600 from dual union all
select 'あ',date '2010-07-01',3000 from dual union all
select 'い',date '2010-04-01', 200 from dual union all
select 'い',date '2010-06-01', 300 from dual union all
select 'い',date '2010-07-01', 900 from dual union all
select 'う',date '2010-04-01', 700 from dual union all
select 'う',date '2010-05-01',2000 from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select c.ID,add_months(a.minV,b.cnter) as dayC,
nvl(c.val,0) as val,sum(nvl(c.val,0)) over(partition by c.ID order by b.cnter) as rumSum
from (select min(dayC) as minV,max(dayC) as maxV from LPO) a
      Join
     (select RowNum-1 as cnter from dict) b
        on add_months(a.minV,b.cnter) <= a.maxV
      Left Join LPO c partition by(c.ID)
        on add_months(a.minV,b.cnter) = c.dayC
order by ID,dayC;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,currV,maxV,Val,rumSum,willOut) as(
select distinct ID,
cast(add_months(min(dayC) over(),-1) as date),
cast(max(DayC) over() as date),0,0,0
  from LPO
union all
select a.ID,
cast(add_months(a.currV,1) as date),
cast(a.maxV as date),
nvl(b.Val,0),a.rumSum+nvl(b.Val,0),1
  from rec a Left Join LPO b
    on add_months(a.currV,1) = b.dayC
   and a.ID=b.ID
 where a.currV < a.maxV)
select ID,currV,Val,rumSum from rec where willOut=1
order by ID,currV;

--■■■Partitioned Outer Joinを使わない方法■■■
select a.ID,add_months(b.minV,c.cnter) as dayC,
nvl(d.val,0) as val,
sum(nvl(d.val,0)) over(partition by a.ID order by c.cnter) as rumSum
  from (select distinct ID from LPO) a
 cross Join (select min(dayC) as minV,max(dayC) as maxV from LPO) b
       Join (select RowNum-1 as cnter from dict) c
         on add_months(b.minV,c.cnter) <= b.maxV
  Left Join LPO d
    on a.ID = d.ID
   and add_months(b.minV,c.cnter) = d.DAYC;

解説

Partitioned Outer Joinの使いどころでしょうねぇ

3-34 Partitioned Outer Join

---