再帰SQLの無限ループを指定階層辿ったら止めるようにする

昨日の続き。User's Forum for DB2 Japan - タイムスタンプを持つテーブルをyear,monthでgroupingしたも...を参考に、共通表式を使った再帰 SQLの無限ループを指定階層辿ったら止めるようにしてみます。戦略的には、

再帰して見つけたデータを格納する中間表(X表)に、再帰の深さを格納する「depth」を追加。
depthは最初は0として、配下データの探索の際に「親のdepth+1」を設定する。
その上で、配下の探索条件として「深さが規定値より小さい」という条件をつける。
- →これにより、一定の階層以下の配下データは中間表に追加されなくなり、それ配下のデータの探索も行なわれません。

とすればOK。昨日のSQLをベースにこの対応を入れると以下のようなSQLになります。

WITH X( from, to, depth ) AS ( 
    SELECT from, to, 0 FROM file_relation  
    WHERE from = 'a' 
  UNION ALL 
    SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation 
    WHERE file_relation.from = X.to AND X.depth < 10 
) SELECT from, to, depth FROM X"

無限ループするデータに対して

$ db2 "select * from file_relation"

FROM                 TO                  
-------------------- --------------------
a                    b                   
a                    c                   
a                    d                   
c                    e                   
c                    f                   
d                    g                   
f                    a                   
x                    y                   
x                    z                   

  9 record(s) selected.

実行。ちゃんと10階層で停止しています。あと、「このSQLだと無限ループになる場合があるよ」の警告も消えてます。

$ db2 "WITH X( from, to, depth ) AS ( SELECT from, to, 0 FROM file_relation  WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation WHERE file_relation.from = X.to AND X.depth < 10 ) SELECT from, to, depth FROM X"

FROM                 TO                   DEPTH      
-------------------- -------------------- -----------
a                    b                              0
a                    c                              0
a                    d                              0
c                    e                              1
c                    f                              1
d                    g                              1
f                    a                              2
a                    b                              3
a                    c                              3
a                    d                              3
c                    e                              4
c                    f                              4
d                    g                              4
f                    a                              5
a                    b                              6
a                    c                              6
a                    d                              6
c                    e                              7
c                    f                              7
d                    g                              7
f                    a                              8
a                    b                              9
a                    c                              9
a                    d                              9
c                    e                             10
c                    f                             10
d                    g                             10

  27 record(s) selected.

後は必要な属性をdistinctで取り出せばOK。

$ db2 "WITH X( from, to, depth ) AS ( SELECT from, to, 0 FROM file_relation  WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation WHERE file_relation.from = X.to AND X.depth < 10 ) SELECT DISTINCT to FROM X"

TO                  
--------------------
a                   
b                   
c                   
d                   
e                   
f                   
g                   

  7 record(s) selected.

まぁ、この戦略だと