10-207 自己結合でpartitioned outer join

SQLパズル

CodeValTable
day1      Code  Val
--------  ----  ---
20070101  a01    10
20070101  a03     5
20070101  a04     4
20070101  a05    23
20070101  a06     3
20070205  a01    25
20070205  a02    32
20070205  a03    42
20070205  a04    33
20070205  a05    52
20070205  a06    11
20070321  a01    98
20070321  a03    76
20070321  a05    54
20070426  a02    32
20070426  a04    10

day1ごとに、存在しないコードのValを0として補完する。
さらにDay1ごとに、Codeの昇順で連番を振る。

出力結果
day1      Code  Val  RNK
--------  ----  ---  ---
20070101  a01    10    1
20070101  a02     0    2
20070101  a03     5    3
20070101  a04     4    4
20070101  a05    23    5
20070101  a06     3    6
20070205  a01    25    1
20070205  a02    32    2
20070205  a03    42    3
20070205  a04    33    4
20070205  a05    52    5
20070205  a06    11    6
20070321  a01    98    1
20070321  a02     0    2
20070321  a03    76    3
20070321  a04     0    4
20070321  a05    54    5
20070321  a06     0    6
20070426  a01     0    1
20070426  a02    32    2
20070426  a03     0    3
20070426  a04    10    4
20070426  a05     0    5
20070426  a06     0    6

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

データ作成スクリプト

create table CodeValTable(day1,Code,Val) as
select '20070101','a01',10 from dual union
select '20070101','a03', 5 from dual union
select '20070101','a04', 4 from dual union
select '20070101','a05',23 from dual union
select '20070101','a06', 3 from dual union
select '20070205','a01',25 from dual union
select '20070205','a02',32 from dual union
select '20070205','a03',42 from dual union
select '20070205','a04',33 from dual union
select '20070205','a05',52 from dual union
select '20070205','a06',11 from dual union
select '20070321','a01',98 from dual union
select '20070321','a03',76 from dual union
select '20070321','a05',54 from dual union
select '20070426','a02',32 from dual union
select '20070426','a04',10 from dual;

SQL

--■■■SQL99構文を使う方法■■■
select a.day1,b.Code,nvl(c.Val,0) as Val,
Row_Number() over(partition by a.day1 order by b.Code) as RNK
  from (select distinct day1 from CodeValTable) a
cross join (select distinct Code from CodeValTable) b
 Left Join CodeValTable c
   on (c.day1 = a.day1
   and c.Code = b.Code)
order by a.day1,b.Code;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.day1,a.Code,nvl(b.Val,0) as Val,
Row_Number() over(partition by b.day1 order by a.Code) as RNK
  from (select distinct Code from CodeValTable) a
  Left Join CodeValTable b
  partition by (b.day1)
    on (a.Code=b.Code)
order by b.day1,a.Code;

--■■■model句を使う方法(10g以降)■■■
select day1,code,Val,Row_Number() over(partition by day1 order by code) as RNK
from (select day1,code,Val
        from CodeValTable
       model
       partition by (day1)
       dimension by (code)
       measures(Val)
       rules(
       Val[for code like 'a0%' from 1 to 6 INCREMENT 1] = nvl(Val[cv()],0)))
order by day1,code;

解説

10g以降なら、Partitioned Outer Joinを使ってもいいでしょう。
3-34 Partitioned Outer Join

model句を使う方法は、最大数が6固定になっています。

---