[新機能] Amazon Redshift 階層データを簡単にクエリできる Recursive CTE（Common Table Expression）がサポートされました

データアナリティクス事業本部コンサルティングチームの石川です。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
      └ ggchild3

child2階層より下のレコードを取得する

同じテーブルの中に親子関係があるレコードをテストデータとして作成します。レコードは、親（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に「再帰」という手続型言語の手法が加わることに違和感を感じ、直感的に理解できない方もいるかも知れませんが、使い勝手の良い機能です。すでに階層化されたデータに対するクエリは便利ですが、再帰的にクエリが実行されているので、ネストが深いクエリ、ツリー追加・削除を伴う更新系クエリを実行する場合は、グラフデータベースなどのデータベースで管理することをおすすめします。