10-234 クロスジョインとPartitioned Outer Joinその1

SQLパズル

testTable
Code1  Code2  Val
-----  -----  ---
  111    123  AAA
  111    123  BBB
  111    123  CCC
  222    123  AAA
  222    123  DDD
  222    789  BBB
  333    789  CCC

Code1,Code2ごとで、必ず3行となるように、
Valをnullとして行を補完する。(Code1,Code2ごとの行の数は、最大でも3行)

出力結果
Code1  Code2  Val
-----  -----  ----
  111    123  AAA
  111    123  BBB
  111    123  CCC
  222    123  AAA
  222    123  DDD
  222    123  null
  222    789  BBB
  222    789  null
  222    789  null
  333    789  CCC
  333    789  null
  333    789  null

データ作成スクリプト

create table testTable(Code1,Code2,Val) as
select 111,123,'AAA' from dual union
select 111,123,'BBB' from dual union
select 111,123,'CCC' from dual union
select 222,123,'AAA' from dual union
select 222,123,'DDD' from dual union
select 222,789,'BBB' from dual union
select 333,789,'CCC' from dual;

SQL

col Val for a10

--■■■Partitioned Outer Joinを使う方法(onで結合)(10g以降)■■■
select b.Code1,b.Code2,b.Val
  from (select 1 as Counter from dual union all
        select 2 from dual union all
        select 3 from dual) a
  Left outer Join (select Code1,Code2,Val,
                   Row_Number() over(partition by Code1,Code2 order by Val) as Rn
                     from testTable) b
  partition by (b.Code1,b.Code2)
    on (a.Counter = b.Rn)
order by b.Code1,b.Code2,b.Val;

--■■■Partitioned Outer Joinを使う方法(usingで結合)(10g以降)■■■
select b.Code1,b.Code2,b.Val
  from (select 1 as JoinKey from dual union all
        select 2 from dual union all
        select 3 from dual) a
  Left outer Join (select Code1,Code2,Val,
                   Row_Number() over(partition by Code1,Code2 order by Val) as JoinKey
                     from testTable) b
  partition by (b.Code1,b.Code2)
    using(JoinKey)
order by b.Code1,b.Code2,b.Val;

--■■■クロスジョインを使う方法■■■
select code1,code2,Val
from (select b.code1,b.code2,decode(a.Counter,1,b.Val) as Val,
      Row_Number() over(partition by b.code1,b.code2 order by Counter) as Rn
        from (select 1 as Counter from dual union all
              select 2 from dual union all
              select 3 from dual) a,testTable b)
where rn <= 3
order by code1,code2,Val;

--■■■model句を使う方法(10g以降)■■■
select Code1,Code2,Val
  from testTable
 model
 partition by (Code1,Code2)
 dimension by (Row_Number() over(partition by Code1,Code2 order by Val) as Rn)
 measures(Val)
 rules(Val[for Rn from 1 to 3 INCREMENT 1] = Val[cv()])
order by Code1,Code2,Rn;

--下記では、ダメなことに注意
--model句でのpartition byとは別なのです。
select Code1,Code2,Val
  from testTable
 model
 partition by (Code1,Code2)
 dimension by (Row_Number() over(/*partition by Code1,Code2*/ order by Val) as Rn)
 measures(Val)
 rules(Val[for Rn from 1 to 3 INCREMENT 1] = Val[cv()])
order by Code1,Code2,Rn;

解説

usingを使う構文では、結合キーの列別名をJoinKeyとしています。
分かりやすい列別名だと自画自賛してみる。

10g以降なら、Partitioned Outer Joinが使えますが、
クロスジョインを使ってもいいでしょう。

3-34 Partitioned Outer Join

---