WITH 再帰クエリ SQLポケリ

2016年3月7日 2025年4月22日 asai

asai

前回は、WITH句と再帰クエリについて紹介した。今回は、その続きである。
再帰処理とツリー構造

再帰処理は、ツリー構造のデータ構造を扱う際に、よく使用される。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
NNO  PNO   NDATA
-----------------
1    NULL  100
2    1     200
3    1     300
4    2     100

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で十分では？
　はい、このクエリではその通りです。
　　えー、じゃあ再帰クエリを使う意味って...
大丈夫、再帰の場合は、計算方法が異なる。再帰処理の特徴を使えば、再帰処理ならではの計算ができるようになる。
まぁ、急ぐこともあるまい。次回に期待？して欲しい。