WITH句と再帰クエリーの話
asai本記事は旧ブログ記事を再編集したものです。
WITH句
少し前から、WITH句というものが使えるようになっている。
Oracleだと9iから使えるのか。割と昔からあるじゃない。
WITHを使うと、長ったらしいサブクエリを何回も書かなくてよくなる。これは便利。
CREATE VIEWをSELECT命令内でやってしまうようなもの。
具体例を示しながら解説してみる。
SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a
といったSELECT命令をサブクエリで何回も使いたいとする。
ビューを定義してしまえば、話は簡単。
CREATE VIEW vfoobar AS
SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a
vfoobarという名前のビューを定義した。
vfoobarをテーブルのように使用できるから、長ったらしいfooとbarをa列で結合して…といったSELECT命令をvfoobarという名前だけで使用できるようになる。
SELECT * FROM vfoobar
WHERE a = (SELECT MIN(a) FROM vfoobar)
OR a = (SELECT MAX(a) FROM vfoobar)
ほらね。
vfoobarのところを、SELECT foo.a, bar.b FROM foo INNER…というSELECT命令に脳内で置換してみてください。
なんだかよくわからない、複雑なクエリになってしまうことがわかる。
WITH句を使えば、こういったことを、ビューを作らないで一気にやってしまうことができる。
WITH foobar AS (
SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a
)
SELECT * FROM foobar
WHERE a = (SELECT MIN(a) FROM foobar)
OR a = (SELECT MAX(a) FROM foobar)
なるほど。やっぱりこれは便利。
WITH RECURSIVE
便利なWITH句ではあるが、本来は「再帰クエリー」というものを想定して作られたものらしい。再帰クエリーとはいったいどういったものなのであろうか。
と偉そうに書いてみたが、再帰という概念は説明するのがけっこう難しい。自分の中に自分があるような感じ。プログラミングの場合、関数の再帰呼び出し、ということをよく耳にするかと思う。関数の再帰呼び出しなら、ある関数の内部処理で、自分自身を「再帰的に」呼び出す、というもの。
よく考えずに、関数の再帰呼び出しを行うと、無限ループに陥ることになる。なぜかというと、自分が自分を呼び出して、さらに呼び出された自分も自分を呼び出すので…
通常の関数は、引数をスタックに積む。無限ループといっても、スタックが足りなくなった時点で異常終了する。また、よく考えられた再帰呼び出しは、ちゃんといつかは終了するようにして作成する。
さて、SQLに戻って再帰クエリーである。
ビューを作成する際に、SELECT命令を記述することになるのだが、そのSELECT命令に定義中のビューを使用することはできない。
なぜなら、そのビューはまだ作成されていないから。
CREATE VIEWでviewxxを作成中に、ビューのSELECT命令でviewxxを使うことはできない。
CREATE VIEW vfoo AS
SELECT * FROM vfoo
まぁ、そりゃそうだよね。再帰関数呼び出しで、無限ループに陥ってしまうことと同じ問題である。
試しに、上記のCREATE VIEWをOracleでやってみたら、エラーになった。
SQLエラー: ORA-01731: 循環ビュー定義が検出されました
01731. 00000 - "circular view definition encountered"
へー、「循環ビュー」っていうのか。
ビューでは、エラーになるものの、WITH句では、これが許されているのである。
マジか？無限ループにならないのか？
やってみよう。
WITH vfoo AS (
SELECT a FROM vfoo
)
SELECT * FROM vfoo
ORA-32039: 再帰的WITH句には列別名リストが必要です
32039. 00000 - "recursive WITH clause must have column alias list"
えー、そうなの。めんどくさー
WITH vfoo(a) AS (
SELECT a FROM vfoo
)
SELECT * FROM vfoo
ORA-32040: 再帰的WITH句にはUNION ALL操作を使用する必要があります
32040. 00000 - "recursive WITH clause must use a UNION ALL operation"
また、エラーになってしまった。
UNION ALLが必要？
WITH vfoo(a) AS (
SELECT a FROM vfoo
UNION ALL
SELECT a FROM vfoo
)
SELECT * FROM vfoo
ORA-32043: 再帰的WITH句には初期化ブランチが必要です
32043. 00000 - "recursive WITH clause needs an initialization branch"
ふえーん。初期化ブランチとはいったい。
要は、再帰呼び出しで無限ループに陥らないように、ちゃんと中のクエリを定義しなさい、ということ。WITHの中で、自分自身を使っても循環ビューのエラーにはならない。
再帰クエリーにする場合は、列指定が必要。再帰クエリーの中には、自分自身を含めてよいが、UNION ALLと初期化ブランチが必要。というところまでわかった。
再帰処理とツリー構造
再帰処理は、ツリー構造のデータ構造を扱う際に、よく使用される。XMLの要素はツリー構造になっている。各要素をくまなく回ってみていく際に、再帰処理を使うと簡単に書けたりする。
SQLでは表形式のデータを扱うことが多いので、再帰処理はあまり得意ではない。SELECT命令にしても、ループ処理をするための制御命令を便利にしただけという印象。
最近拡張された、WITHによって再帰クエリが可能になった、というわけである。
表形式のデータでもツリー構造にすることができる。簡単に実装する方法に、親へのポインタを持つ方法がある。C言語ならポインタとなるが、テーブルなら行番号を持たせれば良いであろう。テーブルを作成するとしたら、以下のようになる。
CREATE TABLE TREE_DATA (
NODE_NO INTEGER NOT NULL PRIMARY KEY,
PARENT_NO INTEGER,
NODE_DATA INTEGER
);
INSERT INTO TREE_DATA VALUES(1, NULL, 100);
INSERT INTO TREE_DATA VALUES(2, 1, 200);
INSERT INTO TREE_DATA VALUES(3, 1, 300);
INSERT INTO TREE_DATA VALUES(4, 2, 100);
図にしたら以下のような感じ。
1
┣ 2
┃ ┗ 4
┗ 3
NODE_NO=1の行には親が存在しない。従って、PARENT_NOの列はNULLとなっている。
NODE_NO=1の行には、子のノードとして、NODE_NO=2と3の二つがぶら下がっている。PARENT_NOの列がどちらも1になっている。
NODE_NO=2の行には、子ノードNODE_NO=4がある。
NODE_NO=3と4の行には、子ノードが存在しない。
PARENT_NOがNULLである行は、子ノードを持たない、いわゆるルートノードである。これは以下のようなSELECT命令で取得できる。
SELECT * FROM TREE_DATA WHERE PARENT_NO IS NULL
NODE_NO PARENT_NO NODE_DATA
-----------------------------
1 NULL 100
ルートノードは、NODE_NO=1の行が一つだけということがわかる。
ノード1の子ノードを列挙しなさい、と言われたら、以下のSELECT命令で取得できる。
SELECT * FROM TREE_DATA WHERE PARENT_NO = 1
NODE_NO PARENT_NO NODE_DATA
-----------------------------
2 1 200
3 1 300
NODE_NO=2と3の二つの子ノードがある。
さらに、ノード2の子ノードを列挙するなら以下とすればOK
SELECT * FROM TREE_DATA WHERE PARENT_NO = 2
NODE_NO PARENT_NO NODE_DATA
-----------------------------
4 2 100
そんなに難しくない。
ノード3なら以下。
SELECT * FROM TREE_DATA WHERE PARENT_NO = 3
NODE_NO PARENT_NO NODE_DATA
-----------------------------
ノード3には子ノードが存在しないので、結果は空となる。
これらのSELECT命令を順に実行できれば、ツリー構造を「舐めた」と言えるのであるが、PARENT_NO=1とか、2とかの条件はツリー構造の図から人力で拾ってきた。ここをSELECT命令でなんとか書きたいっていうのが再帰クエリの主な目的。
WITHを使うと「できるようになっちゃう」のだが、この書き方がちょっと慣れが必要。
まず、WITH内のSELECT命令に、UNION ALLが必要。それに、初期化ブランチというものが必要であることは、前でも述べた。
初期化ブランチというのは、初期化を行うためのクエリで、「ツリー構造を舐める」クエリの場合、ルートノードを見つけるというのが初期化ブランチになる。ブランチ、と呼んでいるが、UNION ALLで繋げたクエリのどちらか、と考えるとスッキリするかも知れない。
初期化ブランチでは、再帰呼び出しをしてはいけない。再帰呼び出しをすると無限ループになるからね。
前記事でのWITH再帰クエリをもう一度見てみよう。
WITH vfoo(a) AS (
SELECT a FROM vfoo
UNION ALL
SELECT a FROM vfoo
)
SELECT * FROM vfoo
ORA-32043: 再帰的WITH句には初期化ブランチが必要です
エラーとなってしまうのは、UNION ALLで繋げた両方のクエリで再帰呼び出しになってしまっているから。以下のように変更して実行してみると、エラーの様子が変化する。
WITH vfoo(a) AS (
SELECT a FROM foo WHERE a = 'A' -- 再帰しない
UNION ALL
SELECT a FROM vfoo
)
SELECT * FROM vfoo
ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました
しかし、これでもエラーになってしまっている。「サイクルが検出」というのは、無限ループに陥ってますよ、と言うこと。UNION ALLで繋げた後の方のクエリで再帰している。
ここの再帰の様子を変更すれば、実行可能な再帰クエリとなる。TREE_DATAの例でやってみよう。
WITH td(NNO, PNO, NDATA) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
UNION ALLの前のクエリは、初期化ブランチである。切り出してよく見てみよう。
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
PARENT_NOがNULLである、ルートノードを取り出すためのクエリである。再帰となっていないため、単独で実行できる。
以下は、UNION ALLの後のクエリである。
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
tdは、WITHで定義したインラインビューである。自分を再帰的に呼び出している部分でもある。そのtdとTREE_DATAを結合している。TD2はTREE_DATAに付けた別名である。
tdを定義中の一部分であるため、取り出して単独で実行することはできない。
tdの定義で、列名を指定している。そのため、tdは以下の列を持っているようになる。
NNO
PNO
NDATA
仮想的な表、tdとTREE_DATAテーブルを結合している。tdの元は、TREE_DATAテーブルなので、自己結合しているようなものだが、単純に一つの同じテーブルを横に並べただけではなく、再帰呼び出しで得られた結果のテーブルを結合している感じになる。
結合条件が、td.NNO = TD2.PARENT_NOとなっていることが最大のポイントなわけである。
なんとなくわかってきたと思うが、まだしっかり把握できたわけではないと思うので、再帰処理を順を追ってみていくことにしよう。
再帰の1回目
WITH句の外で、tdを参照する。その最初の参照で、どうなるかというと、初期化ブランチだけの実行結果が一時表に格納される。初期化ブランチは以下のようになっていた。
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
NNO PNO NDATA
-----------------
1 NULL 100
NODE_NO=1だけの行が一時表に格納される。初期化だからなんとなく納得頂けるのではないかと思う。
再帰の2回目
で、この一時表の結果をさらに、WITH内のクエリにかけるのである。この時、tdの内容が仮に、一時表のものとなる。
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
FROM td INNER JOIN TREE_DATA TD2 -- tdの内容は再帰の1回目の実行結果となる
ON td.NNO = TD2.PARENT_NO
NNO PNO NDATA td.NNO td.PNO td.NDATA
-----------------
1 NULL 100 -- 初期化ブランチで得られる
2 1 200 1 NULL 100
3 1 300 1 NULL 100
td.NNO、td.PNO、td.NDATAは、参考情報である。クエリで取得していないため、戻されることはない。
UNION ALLの後のクエリでPARENT_NO=1となっている行が結合条件に引っかかり、NNO=2と3の二つの行が増えている。一時表には、増えた分の行だけが追加されていくことになる。UNION ALLだからと言って、重複して結果が戻されることはない。
再帰の3回目
前回の呼び出しと、今回の呼び出しで、行が増えている限り、再帰呼び出しが継続する。3回目の再帰処理も行われる。
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
FROM td INNER JOIN TREE_DATA TD2 -- tdの内容は再帰の2回目の実行結果となる
ON td.NNO = TD2.PARENT_NO
NNO PNO NDATA td.NNO td.PNO td.NDATA
-----------------
1 NULL 100 -- 初期化ブランチで得られる
2 1 200 1 NULL 100 -- 2回目の実行で得られる
3 1 300 1 NULL 100 -- 2回目の実行で得られる
4 2 100 2 1 200
NNO=4の行が増えた。
増えたので、4回目の再帰も行われる。
再帰の4回目
SELECT NODE_NO, PARENT_NO, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
FROM td INNER JOIN TREE_DATA TD2 -- tdの内容は再帰の3回目の実行結果となる
ON td.NNO = TD2.PARENT_NO
NNO PNO NDATA td.NNO td.PNO td.NDATA
-----------------
1 NULL 100 -- 初期化ブランチで得られる
2 1 200 1 NULL 100 -- 2回目の実行で得られる
3 1 300 1 NULL 100 -- 2回目の実行で得られる
4 2 100 2 1 200 -- 3回目の実行で得られる
3回目でNNO=4の行が増えたが、PARENT_NO=4である行が存在しないので、結果として行が増えなかった。そのため、再帰呼び出しは、4回目で終了となる。
わかって頂けたであろうか。
これが、再帰クエリの基本である。
もう、「こういうパターンで再帰クエリは書くもの」と覚えてしまった方が良いかも知れない。
ポイントとしては、以下のようになる。
再帰クエリでは
1 WITH内には、UNION ALLで二つのSELECT命令を書く
2 二つのSELECT命令のうち、一つは初期化ブランチなので、再帰呼び出しをしてはいけない
3 二つのSELECT命令のうち、もう一つは再帰呼び出しをして良いが、呼び出し前の一時表結果と結合する
4 呼び出し前の一時表結果との結合条件は、親子関係を示す「PARENT_NO = NODE_NO」のようなものとなる
しかしながら、得られた結果にどう言った意味があるのか？
少々疑問ではないだろうか。
単に、全レコードを取得できただけ？それならば、SELECT * FROM TREE_DATAで十分では？
はい、このクエリではその通りです。
えー、じゃあ再帰クエリを使う意味って…
大丈夫、再帰の場合は、計算方法が異なる。再帰処理の特徴を使えば、再帰処理ならではの計算ができるようになる。
再帰クエリーならではの計算とは
なんとか再帰クエリを書いて、実行させることができたが、なんか「ありがたみ」がない。
なぜかといえば、再帰的にデータを検索しているが、検索するだけでなんの演算もしていないから。今回は再帰ならではの演算をさせてみよう。
ツリー構造には再帰、ということを前回も紹介したが「ツリー構造ならでは」の情報を計算させることにしてみる。表形式のデータは、行と列を指定すれば、一つのセルが決定して、その中に入っているデータを参照できる。
ツリー構造のデータでは、何階層目に位置するデータなのか、といった情報も結構重要であったりする。
1
┣ 2
┃ ┗ 4
┗ 3
例にしている、ツリー構造のデータは上のようになっていた。
ルートノードとなっている、1のデータは「最初の階層にある」と言って良いと思う。最初を0とするか1とするかの決め事があると思うが、ここでは、最初は0ということにしよう。1の子である、2と3のノードは、階層1になる。2の子である4のノードはさらに下の階層2。
TREE_DATAテーブルの行を見てみると、以下のようになっている。
SELECT * FROM TREE_DATA
NODE_NO PARENT_NO NODE_DATA 階層
---------------------------
1 NULL 100 -- 0
2 1 200 -- 1
3 1 300 -- 1
4 2 100 -- 2
階層をコメントで記載した。このような結果を計算したいのである。
普通のSELECT命令だけではこのような計算はできない。集計関数を使っても無理かも。ユーザ定義関数ならできるか。
そこで、WITH再帰クエリの登場となる。
初期化ブランチで検索できる行は、すべてルートノードと言って良い。なので、初期化ブランチに引っかかった行は、階層0として良い。
WITH句では、列を決定できるが、計算によって生成されるデータであっても良い。まずは、列LVLを作って、初期化ブランチで0を返してみよう。UNION ALL後の再帰クエリではとりあえず、NULLを戻しておく。
WITH td(NNO, PNO, NDATA, LVL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0 -- LVL 0を戻す
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, NULL -- とりあえずNULL
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
NNO PNO NDATA LVL
-------------------------
1 NULL 100 0
2 1 200 NULL
3 1 300 NULL
4 2 100 NULL
とりあえずは、ルートノードのLVLは0になった。コメントで示した階層に一つ近づいた。
では、次の段階へ進もう。
UNION ALLの後の再帰クエリで「とりあえずNULL」としたところを変更していけば、良いことはなんとなくわかるが、どうしたら良いものか。
TREE_DATAテーブルには、LVLの情報はないので、計算するしかない。tdは、一時テーブルなので、前回の再帰処理の結果がわかるはず。そうか、td.LVLを見ればOKかも？
WITH td(NNO, PNO, NDATA, LVL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL -- tdのLVLを戻してみる
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
NNO PNO NDATA LVL
-------------------------
1 NULL 100 0
2 1 200 0
3 1 300 0
4 2 100 0
おっと、全部0になってしまった。
そりゃ、そうか。どっかでインクリメントしないとね。
td.LVL + 1で戻せばOKだろうか。やってみよう。
WITH td(NNO, PNO, NDATA, LVL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1 -- これでどうだ
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
NNO PNO NDATA LVL
-------------------------
1 NULL 100 0
2 1 200 1
3 1 300 1
4 2 100 2
いいね。
NNO=1はルートノードなので0。
NNO=2と3は、1の子ノードなので、階層は1。OKです。
NNO=4は、1->2->4といった「パス」を経由することになるので、階層は2でOK。2回遷移した(矢印が二つ)。
前回、解説していった「再帰のn回目」みたいなことが、LVLで計算できた！っていうことなんです。
TREE_DATAとtdを結合することで、再帰の前後のデータの両方を参照することができる。というのが、再帰クエリの一番の特徴なのかな。
パスから値を計算する
見事に階層を計算することができた。
最後に、NODE_DATAの合計を計算する再帰クエリを紹介してみたいと思う。
WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
TD2.NODE_DATA + td.NDATA_TOTAL
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
NNO PNO NDATA LVL NDATA_TOTAL
-----------------------------------
1 NULL 100 0 100
2 1 200 1 300
3 1 300 1 400
4 2 100 2 400
ノード4のNDATA_TOTALに注目して欲しい。計算された値は400となっている。これは、以下の計算式で計算されたものである。
100 + 200 + 100
ノード1 -> ノード2 -> ノード4と再帰のパスを通ってくることになるのだが、その時のNDATAの値を順番に足し算していった結果がNDATA_TOTALとなる。
SUMは、グループの合計を計算する。仮にノード1,2,4と言うグループ分けができれば、SUM集計関数を使用してNDATA_TOTALを計算できなくもない。しかし、そんな変なグループ化ができないので、SUM集計関数では計算できないのである。おっと、OracleにはCONNECT BYみたいな文法があるんだっけか？これは要調査か。
再帰の循環
何も考えずに、自分自身を呼び出すと無限ループに陥る、といった話をしたかと思う。WITHの再帰クエリでは、再帰呼び出しできる場所が限定されているので、無限ループしてしまうことが少ないようになっている。
それでも、データの作り方によっては、「再帰の循環」が発生してしまうこともある。
ツリー構造では、ルートから子が生えていって、いずれは子を持たないノードになる。末端のノードは、リーフノードとも呼ばれる。
また、通常は、子ノードが祖先のノードに先祖返りしてしまうこともない。そのようになっていると、タイムリープが発生することになり、親子関係が時系列順に並ばなくなってしまう。なんか、難しい？図にしたら以下のようなことである。
1
┣ 2
┃ ┗ 4
┗ 3
┗ 1
3の子に1がある。3の親も1である。
親ノードへのポインタを持つツリー構造では、こう言ったデータを作成することはできない。上の図は無理やり描いただけ。
と思ったが、TREE_DATAテーブルでNODE_NOがプライマリキーになっていなければ可能か。TREE_DATAテーブルにもその行をプライマリキーを外して追加してみた。
SELECT * FROM TREE_DATA
NODE_NO PARENT_NO NODE_DATA
---------------------------
1 NULL 100
2 1 200
3 1 300
4 2 100
1 3 90
これを先ほどの再帰クエリにかけるとどうなるのか。やってみよう。
WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
TD2.NODE_DATA + td.NDATA_TOTAL
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td
ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました
エラーになりました。
無限ループになっちゃったわけである。
CYCLE
さて、これを回避する方法もちゃんとあります。
WITH句のオプションでCYCLEというものがある。これを使って循環を検出できるような列を書いておく。CYCLEに続けて、SET なになに、と式を書くがここはあまり重要ではなく、フラグの名前とフラグの値を0/1にします程度のこと。
WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
TD2.NODE_DATA + td.NDATA_TOTAL
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
CYCLE NNO SET LOOP_FLAG TO 1 DEFAULT 0 -- CYCLEで循環を検出させる
SELECT * FROM td
NNO PNO NDATA LVL NDATA_TOTAL LOOP_FLAG
---------------------------------------------
1 NULL 100 0 100 0
2 1 200 1 300 0
3 1 300 1 400 0
4 2 100 2 400 0
1 3 90 2 490 1
CYCLEに続けて、再帰の過程で記録しておくべき列を指定する。記録しておいた値が再度出現したら循環とみなし、再帰を停止する。複数であっても良いが、WITH句のパラメータにふくまれている仮想な列を指定する必要がある。
例で言うのなら、TREE_DATAテーブルのNODE_NOは、指定不可で、NNOならOK。
CYCLE 列名 の後のSET LOOP_FLAG TO 1 DEFAULT 0は、循環の検出に使用するフラグの名前と値。
WITHで定義される仮想表に自動的にこの列が追加される。面倒な指定だが省略することはできない。
フラグの値は、数値でなくてもよく、1文字の文字列でも可。SET LOOP_FLAG TO 'Y' DEFAULT 'N'とすることもできる。数値の場合でも桁数が1。
数値、文字のどちらの場合でも、ふたつ指定する値がどちらも同じであってはいけない。SET LOOP_FLAG TO 0 DEFAULT 0はダメ。違いがわからないもんね。
CYCLE指定する列は、例の場合は、NNOが適切。PNOでも循環を検出できるが、以下のように1段階、再帰が深いところまで進んで行われる。
WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
FROM TREE_DATA WHERE PARENT_NO IS NULL
UNION ALL
SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
TD2.NODE_DATA + td.NDATA_TOTAL
FROM td INNER JOIN TREE_DATA TD2
ON td.NNO = TD2.PARENT_NO
)
CYCLE PNO SET LOOP_FLAG TO 1 DEFAULT 0 -- PNOで循環検出
SELECT * FROM td
NNO PNO NDATA LVL NDATA_TOTAL LOOP_FLAG
---------------------------------------------
1 NULL 100 0 100 0
2 1 200 1 300 0
3 1 300 1 400 0
4 2 100 2 400 0
1 3 90 2 490 0
2 1 200 3 690 1
3 1 300 3 790 1
用語や方言について
WITH再帰クエリについて、解説してきた。すべて、Oracle11gで実行して検証している。SQL ServerでもWITHで再帰させることが可能ではあるものの、CYCLEが使えなかったりするので注意が必要。
PostgreSQLでは、WITH RECURSIVE …と再帰クエリとする場合は、RECURSIVEを明示しないとダメかも。
Oracle11gではRECURSIVE指定はできない。もーまた方言作っちゃって。まぁ、CONNECT BYよりはいいか。
初期化ブランチという用語も「Oracleならでは」なのかも。UNION ALLの前後のSELECT命令をなんと呼ぶかについては結構DBによってバラバラ？
SQL標準だと、「非再帰項」と「再帰項」となっている。非再帰項は最初の初期化の時だけしか評価されないので、名前としては初期化項の方がわかりやすいと思うが。「再帰を含まない初期化クエリ」とでもしておくか。
UNION ALLの前後にどちらを書いても良い、というのが非常に嫌な感じ。説明しにくいじゃないか。
投稿者プロフィール
最新の投稿
コンピューター関係の記事2025年4月26日アイキャッチ画像にSQLアイコンとJSONアイコンが欲しい
コンピューター関係の記事2025年4月23日AdSense審査に無事パス- コンピューター関係の記事2025年4月22日SSブログ復活
- コンピューター関係の記事2025年4月22日SSブログが無くなった余波は大きい
