10-159 シェルソートのようなデータ交換

SQLパズル

tbl1
ID1  ID2
---  ----
  1   A
  2   B
  3   C
  4   NULL
  5   D
  6   A
  7   B
  8   NULL
  9   C
 10   D
 11   NULL
 12   A
 13   B
 14   NULL
 15   NULL

以下の仕様で、ソートして出力する。
ID2がnullでないレコードは、同じID2の中で、最小のID1が第1ソートキー
ID2がnullのレコードは、ID1が第1ソートキー
ID1が第2ソートキー

出力結果
ID1  ID2
---  ---
  1   A
  6   A
 12   A
  2   B
  7   B
 13   B
  3   C
  9   C
  4   NULL
  5   D
 10   D
  8   NULL
 11   NULL
 14   NULL
 15   NULL

データ作成スクリプト

create table tbl1(
ID1 number(2),
ID2 char(1));

insert into tbl1 values( 1,'A');
insert into tbl1 values( 2,'B');
insert into tbl1 values( 3,'C');
insert into tbl1 values( 4,NULL);
insert into tbl1 values( 5,'D');
insert into tbl1 values( 6,'A');
insert into tbl1 values( 7,'B');
insert into tbl1 values( 8,NULL);
insert into tbl1 values( 9,'C');
insert into tbl1 values(10,'D');
insert into tbl1 values(11,NULL);
insert into tbl1 values(12,'A');
insert into tbl1 values(13,'B');
insert into tbl1 values(14,NULL);
insert into tbl1 values(15,NULL);

SQL

--■■■分析関数を使う方法1■■■
select ID1,ID2
  from tbl1
order by nvl2(ID2,min(ID1) over(partition by ID2),ID1),ID1;

--■■■分析関数を使う方法2(10g以降)■■■
select ID1,ID2
  from (select ID1,ID2,
        nvl(ID2,Last_value(decode(Rank,1,ID2) ignore nulls) over(order by ID1)) as makeset
          from (select ID1,ID2,
                Row_Number() over(partition by ID2 order by ID1) as Rank
                  from tbl1))
order by min(ID1) over(partition by makeset),nvl2(ID2,0,1),ID1;

解説

分析関数を使う方法1は、
ID2がNULLかで場合分けしてます。

脳内ソートは、こんな感じです
最初の状態    第1ソートキーでソート後
ID1  ID2      ID1  ID2   ID1  ID2   ID1  ID2
---  ----     ---  ----  ---  ----  ---  ----
  1   A         1   A      6   A     12   A
  2   B         2   B      7   B     13   B
  3   C         3   C      9   C
  4   NULL      4   NULL
  5   D         5   D     10   D
  6   A         8   NULL
  7   B        11   NULL
  8   NULL     14   NULL
  9   C        15   NULL
 10   D
 11   NULL
 12   A
 13   B
 14   NULL
 15   NULL


分析関数を使う方法2は、シェルソートのデータ交換に近いですね

---

場合分けの資料

シェルソート
シェルソート

---