読者です 読者をやめる 読者になる 読者になる
無料で使えるシステムトレードフレームワーク「Jiji」 をリリースしました!

・OANDA Trade APIを利用した、オープンソースのシステムトレードフレームワークです。
・自分だけの取引アルゴリズムで、誰でも、いますぐ、かんたんに、自動取引を開始できます。

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

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.


まぁ、この戦略だと

  • 辿る階層をいくつにするのか?
  • 循環している場合、配下要素数が少なくても(規定数までは探索を行なうので)無駄に時間がかかるのでは?

とかいう懸念はあるわけですが。とはいえ、安全装置としてこういう仕組みは入れておいた方が良さそうですね。