10-272 model句でPartitioned Outer Joinもどき

SQLパズル

AddTable
Val  GID
---  ----
 10  1029
 20  1029
 10  1131
 20  1131
 30  1131
 40  1131
 50  1131

GIDごとに、レコード数が10件になるように、
Valが0の行を補完する。

出力結果
Val  GID
---  ----
 10  1029
 20  1029
  0  1029
  0  1029
  0  1029
  0  1029
  0  1029
  0  1029
  0  1029
  0  1029
 10  1131
 20  1131
 30  1131
 40  1131
 50  1131
  0  1131
  0  1131
  0  1131
  0  1131
  0  1131

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

データ作成スクリプト

create table AddTable(Val,GID) as
select 10,1029 from dual union all
select 20,1029 from dual union all
select 10,1131 from dual union all
select 20,1131 from dual union all
select 30,1131 from dual union all
select 40,1131 from dual union all
select 50,1131 from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select nvl(b.Val,0) as Val,b.GID
  from table(sys.odciNumberList(1,2,3,4,5,6,7,8,9,10)) a
       Left Join
       (select Val,GID,
        Row_Number() over(partition by GID order by Val) as rn
          from AddTable) b
       partition by (b.GID)
       on (b.rn = a.Column_Value)
order by b.GID,a.Column_Value;

--■■■model句を使う方法(10g以降)■■■
select Val,GID
  from AddTable
 model
 partition by(GID)
 dimension by(Row_Number() over(partition by GID order by Val) as rn)
 measures(Val)
 rules(Val[for rn from 1 to 10 INCREMENT 1]
     = presentv(Val[cv()],Val[cv()],0));

--■■■SQL99構文を使う方法■■■
select nvl(c.Val,0) as Val,a.GID
from (select distinct GID from AddTable) a
      cross Join (select RowNum as Counter from dict where RowNum <=10) b
      Left Join (select Val,GID,
                 Row_Number() over(partition by GID order by Val) as rn
                   from AddTable) c
        on (a.GID = c.GID
       and  b.Counter = c.rn)
order by a.GID,b.Counter;

解説

ANSI構文でクロスジョインさせるか、
10g以降ならPartitioned Outer Joinやmodel句を使ってもいいでしょう。

nullへの対処は、原則としてnvl関数ですが、
model句であれば、presentv関数を使ってもいいです。

3-34 Partitioned Outer Join
10-109  ANSI構文でクロスジョイン

---