8-44 YYYYMM形式の補完(固定範囲)

SQLパズル

yyyymmTable
Code  yyyymm
----  ------
AAAA  200811
AAAA  200902
AAAA  200905
AAAA  200910
BBBB  200801
BBBB  200805
CCCC  200810
DDDD  200803
DDDD  200804
DDDD  200805

Codeごとに、
2008年11月から2009年02月までを補完しつつ出力する。
existFlagで存在の有無を表示する。

出力結果
Code  yyyymm  existFlag
----  ------  ---------
AAAA  200811  1
AAAA  200812  0
AAAA  200901  0
AAAA  200902  1
BBBB  200811  0
BBBB  200812  0
BBBB  200901  0
BBBB  200902  0
CCCC  200811  0
CCCC  200812  0
CCCC  200901  0
CCCC  200902  0
DDDD  200811  0
DDDD  200812  0
DDDD  200901  0
DDDD  200902  0

データ作成スクリプト

create table yyyymmTable(Code,yyyymm) as
select 'AAAA',200811 from dual union all
select 'AAAA',200902 from dual union all
select 'AAAA',200905 from dual union all
select 'AAAA',200910 from dual union all
select 'BBBB',200801 from dual union all
select 'BBBB',200805 from dual union all
select 'CCCC',200810 from dual union all
select 'DDDD',200803 from dual union all
select 'DDDD',200804 from dual union all
select 'DDDD',200805 from dual;

SQL

--■■■9iでも動く方法■■■
select b.Code,a.yyyymm,
nvl2(c.yyyymm,1,0) as existFlag
  from (select 200811 as yyyymm from dual union all
        select 200812 from dual union all
        select 200901 from dual union all
        select 200902 from dual) a cross Join
       (select distinct Code from yyyymmTable) b
          Left Join yyyymmTable c
            on (b.Code = c.Code
            and a.yyyymm = c.yyyymm)
order by b.Code,a.yyyymm;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.Code,a.yyyymm,
nvl2(b.yyyymm,1,0) as existFlag
from (select 200811 as yyyymm from dual union all
      select 200812 from dual union all
      select 200901 from dual union all
      select 200902 from dual) a
      Left Join yyyymmTable b
      partition by (b.Code)
      on (a.yyyymm = b.yyyymm)
order by b.Code,a.yyyymm;

--■■■model句を使う方法(10g以降)■■■
select Code,yyyymm,existFlag
  from yyyymmTable
 model RETURN UPDATED ROWS
 partition by (Code)
 dimension by (yyyymm)
 measures(0 as existFlag)
 rules(existFlag[for yyyymm in(200811,200812,200901,200902)]
     = presentv(existFlag[cv()],1,0))
order by Code,yyyymm;

解説

10gならmodel句を使うのもいいでしょう。

3-34 Partitioned Outer Join

---