WITH 再帰クエリ SQLポケリ：AABlog：So-netブログ

	ブログをはじめるログイン

SQL WITH句再帰クエリー REC.. ｜WITH 再帰クエリその2 ブログトップ

WITH 再帰クエリ SQLポケリ　[SQLポケリ] [編集]

前回は、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で十分では？

　はい、このクエリではその通りです。

　　えー、じゃあ再帰クエリを使う意味って...

大丈夫、再帰の場合は、計算方法が異なる。再帰処理の特徴を使えば、再帰処理ならではの計算ができるようになる。

まぁ、急ぐこともあるまい。次回に期待？して欲しい。

関連記事

WITH 再帰クエリその２
WITH 再帰クエリー