4-23 model句でnullの行を詰める

SQLパズル

modelFullJoin
ID  data1  data2  data3  data4
--  -----  -----  -----  -----
 1  AAAA     100  null    null
 1  null    null  aaaa     100
 2  BBBB     100  null    null
 2  CCCC     200  null    null
 2  DDDD     300  null    null
 2  null    null  bbbb     600
 3  EEEE     250  null    null
 3  null    null  cccc     250
 3  null    null  dddd     500
 3  FFFF     500  null    null
 4  GGGG     150  null    null
 4  null    null  eeee     150
 4  HHHH     300  null    null
 4  null    null  ffff     300
 4  null    null  gggg     400
 4  IIII     400  null    null
 4  null    null  hhhh     100
 4  null    null  iiii     600
 4  JJJJ     700  null    null

IDごとでdata1とdata2のペアがnullの行を上に詰め、かつ、
IDごとでdata3とdata4のペアがnullの行を上に詰める。

最後に、IDごとでdata1,data3の昇順で連番を付与する。

出力結果
ID  rn  data1  data2  data3  data4
--  --  -----  -----  -----  -----
 1   1  AAAA     100  aaaa     100
 2   1  BBBB     100  bbbb     600
 2   2  CCCC     200  null    null
 2   3  DDDD     300  null    null
 3   1  EEEE     250  cccc     250
 3   2  FFFF     500  dddd     500
 4   1  GGGG     150  eeee     150
 4   2  HHHH     300  ffff     300
 4   3  IIII     400  gggg     400
 4   4  JJJJ     700  hhhh     100
 4   5  null    null  iiii     600

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

データ作成スクリプト

create table modelFullJoin(id,data1,data2,data3,data4) as
select 1,'AAAA', 100,  null,null from dual union all
select 1,  null,null,'aaaa', 100 from dual union all
select 2,'BBBB', 100,  null,null from dual union all
select 2,'CCCC', 200,  null,null from dual union all
select 2,'DDDD', 300,  null,null from dual union all
select 2,  null,null,'bbbb', 600 from dual union all
select 3,'EEEE', 250,  null,null from dual union all
select 3,  null,null,'cccc', 250 from dual union all
select 3,  null,null,'dddd', 500 from dual union all
select 3,'FFFF', 500,  null,null from dual union all
select 4,'GGGG', 150,  null,null from dual union all
select 4,  null,null,'eeee', 150 from dual union all
select 4,'HHHH', 300,  null,null from dual union all
select 4,  null,null,'ffff', 300 from dual union all
select 4,  null,null,'gggg', 400 from dual union all
select 4,'IIII', 400,  null,null from dual union all
select 4,  null,null,'hhhh', 100 from dual union all
select 4,  null,null,'iiii', 600 from dual union all
select 4,'JJJJ', 700,  null,null from dual;

SQL

col data1 for a5
col data3 for a5
col newData1 for a5
col newData3 for a5
col id for 99
col rn1 for 99
col rn2 for 99

--■■■model句を使う方法(10g以降)■■■
select id,Row_Number() over(partition by id order by newData1,newData3) as rn,
newData1,newData2,newData3,newData4
from (select id,newData1,newData2,newData3,newData4
        from modelFullJoin
       model
      partition by(id)
      dimension by(Row_Number() over(partition by id order by data1,data2) as rn1,
                   Row_Number() over(partition by id order by data3,data4) as rn2)
      measures(data1,data2,data3,data4,
               cast(null as varchar2(5)) as newData1,
               to_number(null) as newData2,
               cast(null as varchar2(5)) as newData3,
               to_number(null) as newData4)
      rules(
      newData1[any,any]=max(data1)[cv(rn1),any],
      newData2[any,any]=max(data2)[cv(rn1),any],
      newData3[any,any]=max(data3)[any,cv(rn1)],
      newData4[any,any]=max(data4)[any,cv(rn1)]))
where newData1 is not null
   or newData3 is not null
order by id,rn;

--■■■完全外部結合を使う方法■■■
select nvl(a.id,b.id) as ID,
Row_Number() over(partition by nvl(a.id,b.id)
order by nvl(a.rn,b.rn)) as rn,
data1,data2,data3,data4
from (select id,data1,data2,
      Row_Number() over(partition by id order by data1) as rn
        from modelFullJoin
       where data1 is not null) a
      full join
      (select id,data3,data4,
       Row_Number() over(partition by id order by data3) as rn
         from modelFullJoin
        where data3 is not null) b
       on a.id=b.id
      and a.rn=b.rn
order by ID,rn;

解説

model句の使いどころかもしれませんねぇ

10-290 model句によるユニークアクセス

---