10-233 移動可能な経路ごとに連番付与

SQLパズル

GoodsTable
No1  No2
---  ---
 1     4
 3     4
 2     5
 6     7
 5     9
 4     7
 8     9
12    13
13    15
12    16
20    25
35    30
40    42
42    49
45    49
50    51
51    53
53    52

No1とNo2は、関連があり、
No2とNo1も、関連がある。
といった形で、関連ごとに連番を付与し、
以下の形で出力する。

出力結果
GID  NO
---  --
001   1
001   3
001   4
001   6
001   7
002   2
002   5
002   8
002   9
003  12
003  13
003  15
003  16
004  20
004  25
005  30
005  35
006  40
006  42
006  45
006  49
007  50
007  51
007  52
007  53

データ作成スクリプト

create table GoodsTable(No1,No2) as
select  1, 4 from dual union all
select  3, 4 from dual union all
select  2, 5 from dual union all
select  6, 7 from dual union all
select  5, 9 from dual union all
select  4, 7 from dual union all
select  8, 9 from dual union all
select 12,13 from dual union all
select 13,15 from dual union all
select 12,16 from dual union all
select 20,25 from dual union all
select 35,30 from dual union all
select 40,42 from dual union all
select 42,49 from dual union all
select 45,49 from dual union all
select 50,51 from dual union all
select 51,53 from dual union all
select 53,52 from dual;

SQL

col GID for a10

--■■■connect_by_rootを使う方法1(10g以降)■■■
select distinct
to_char(dense_rank() over(order by a.GID),'fm009') as GID,
case b.Counter when 1 then a.RootNo1
               when 2 then a.RootNo2
               end as No
from (select RootNo1,RootNo2,
      min(UKey) over(partition by RootNo1,RootNo2) as GID
      from (select UKey,connect_by_root No1 as RootNo1,
                        connect_by_root No2 as RootNo2
              from (select No1,No2,Row_Number() over(order by No1,No2) as UKey from GoodsTable)
            connect by nocycle prior No1 in(No1,No2)
                            or prior No2 in(No1,No2))) a,
    (select 1 as Counter from dual
     union all select 2 from dual) b
order by GID,No;

--■■■connect_by_rootを使う方法2(10g以降)■■■
select distinct
to_char(dense_rank() over(order by a.GID),'fm009') as GID,
case b.Counter when 1 then a.RootNo1
               when 2 then a.RootNo2
               end as No
from (select RootNo1,RootNo2,LV,
      min(UKey) over(partition by RootNo1,RootNo2) as GID
      from (select connect_by_root No1 as RootNo1,
                   connect_by_root No2 as RootNo2,
                   Level as LV,UKey
              from (select No1,No2,Row_Number() over(order by No1,No2) as UKey from GoodsTable)
            connect by nocycle prior No1 in(No1,No2)
                            or prior No2 in(No1,No2))) a,
    (select 1 as Counter from dual
     union all select 2 from dual) b
where a.LV = 1
order by GID,No;

--■■■connect_by_rootを使う方法3(10g以降)■■■
select distinct
to_char(dense_rank() over(order by a.GID),'fm009') as GID,
case b.Counter when 1 then a.RootNo1
               when 2 then a.RootNo2
               end as No
from (select RootNo1,RootNo2,min(UKey) as GID
      from (select UKey,connect_by_root No1 as RootNo1,
                        connect_by_root No2 as RootNo2
              from (select No1,No2,Row_Number() over(order by No1,No2) as UKey from GoodsTable)
            connect by nocycle prior No1 in(No1,No2)
                            or prior No2 in(No1,No2))
      group by RootNo1,RootNo2) a,
    (select 1 as Counter from dual
     union all select 2 from dual) b
order by GID,No;

解説

一旦、Row_Number関数でユニークキーを付与して、
その大小関係のdenseなrankを連番としています。
なお、RowIDで大小関係を定義してもいいです。

8-26 碁石の固まりを出力

グラフ理論

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
方法1のクエリは、
distinctが指定されていて、
partition by句とselect句が一致しているので、
group byを使った、方法3のクエリに書き換え可能です。

---