この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部コンサルティングチームの石川です。Recursive CTE（Common Table Expression）を使用して、組織図やマルチレベル製品などの階層データをクエリできるようになりました。早速、階層化データを用意して試してみます。
Recursive CTE（Common Table Expression）とは
WITH句は、より複雑な問い合わせで補助的に用いるインナービューを提供します。 これらの文は共通テーブル式(Common Table Expressions)またはCTEと呼ばれるものであり、１つの問い合わせのために存在する一時テーブルと考えることができます。これを再帰的に応用したのが、今回ご紹介する Recursive CTE（Common Table Expression）です。
下記のように親子関係（Tree構造）のデータがあったとします。（ナイーブツリーと言った方が分かる人にはわかるかもしれません。）
parent
├ child1
│ └ gchild1
│ ├ ggchild1
│ └ ggchild2
└ child2
├ gchild2
└ gchild3
└ ggchild3child2階層より下のレコードを取得する
同じテーブルの中に親子関係があるレコードをテストデータとして作成します。レコードは、親（parent）、子（child）、孫（gchild）、ひ孫（ggchild）のような階層化したデータです。
- 親（parent）は、親が存在しないので、parent_idは、
null - 子１（child1）の親は、親（parent）なので、parent_idは、
1
cmdb=# CREATE TABLE members(
id int,
name varchar(32),
parent_id int
);
CREATE TABLE
cmdb=# INSERT INTO members(id, name, parent_id)
VALUES(1,'parent', null),
(2,'child1', 1),
(3,'child2', 1),
(4,'gchild1', 2),
(6,'gchild2', 3),
(7,'gchild3', 3),
(8,'ggchild1', 4),
(9,'ggchild2', 4),
(11,'ggchild3', 7)
;
INSERT 0 9
cmdb=# SELECT * FROM members;
id | name | parent_id
----+----------+-----------
1 | parent |
2 | child1 | 1
3 | child2 | 1
4 | gchild1 | 2
6 | gchild2 | 3
7 | gchild3 | 3
8 | ggchild1 | 4
9 | ggchild2 | 4
11 | ggchild3 | 7
(9 rows)早速、WITH RECURSIVE句にクエリを記載します。一般的なWITH句に記載するクエリとの違いは、UNION ALLの下に再帰的にテーブルを結合するクエリを記載します。上記の階層データに対して、child2以下の階層のレコードを取得したい場合、Recursive CTE（Common Table Expression）を用いると簡単にレコードを取得できます。最後のSELECT句で、インナービュー名（cte）を参照すると、child2以下のすべてのレコードが参照できます。
cmdb=# WITH RECURSIVE cte(id, name, parent_id) AS (
SELECT id, name, parent_id FROM members WHERE name = 'child2'
UNION ALL
SELECT child.id, child.name, child.parent_id
FROM members AS child, cte
WHERE cte.id = child.parent_id
)
SELECT * FROM cte;
id | name | parent_id
----+----------+-----------
3 | child2 | 1
7 | gchild3 | 3
6 | gchild2 | 3
11 | ggchild3 | 7
(4 rows)実行プランを確認
上記のクエリは、３つのSELECT句があります。
- 5行目のSELECT句： 18行目から23行目までが再帰的にクエリを実行しているSELECT句です。
- 3行目のSELECT句： 18行目から23行目までが'child2'のレコードを取得するクエリです。
- 9行目のSELECT句： 18行目がその結果を表示するクエリです。
14行目以下が再帰的に繰り返される部分（XN Recursive Union）です。内部的には、再帰的にクエリが実行されているはずですが、階層の深さは実行に判定するため、実行プランには現れません。
cmdb=# EXPLAIN
WITH RECURSIVE cte(id, name, parent_id) AS (
SELECT id, name, parent_id FROM members WHERE name = 'child2'
UNION ALL
SELECT child.id, child.name, child.parent_id
FROM members AS child, cte
WHERE cte.id = child.parent_id
)
SELECT * FROM cte;
QUERY PLAN
-------------------------------------------------------------------------------------------------
XN CTE Scan on cte (cost=179.24..179.74 rows=201 width=74)
InitPlan
-> XN Recursive Union (cost=0.00..179.24 rows=201 width=19)
-> XN Subquery Scan "*SELECT* 1" (cost=0.00..0.12 rows=1 width=19)
-> XN Seq Scan on members (cost=0.00..0.11 rows=1 width=19)
Filter: ((name)::text = 'child2'::text)
-> XN Subquery Scan "*SELECT* 2" (cost=0.11..179.11 rows=200 width=19)
-> XN Hash Join DS_DIST_ALL_NONE (cost=0.11..177.11 rows=200 width=19)
Hash Cond: ("outer".id = "inner".parent_id)
-> XN CTE Scan on cte (cost=0.00..12.50 rows=5000 width=4)
-> XN Hash (cost=0.09..0.09 rows=9 width=19)
-> XN Seq Scan on members child (cost=0.00..0.09 rows=9 width=19)
(12 rows)まとめ
Recursive CTE（Common Table Expression）のサポートで、直感的に階層データをクエリできるようになりました。この機能は、PostgreSQL 8.4やMySQL 8.0(Lab版)で提供された機能です。宣言型言語のSQLに「再帰」という手続型言語の手法が加わることに違和感を感じ、直感的に理解できない方もいるかも知れませんが、使い勝手の良い機能です。すでに階層化されたデータに対するクエリは便利ですが、再帰的にクエリが実行されているので、ネストが深いクエリ、ツリー追加・削除を伴う更新系クエリを実行する場合は、グラフデータベースなどのデータベースで管理することをおすすめします。
2
