PostgreSQL 13 min read

PostgreSQLのWITH句 ― CTEの書き方とWITH RECURSIVEで階層をたどる

WITH 句は、SELECT の前に名前付きの副問い合わせを定義する構文です。共通テーブル式（CTE: Common Table Expression）とも呼びます。副問い合わせを入れ子にする代わりに、上から順に読める形で書けます。この記事の出力は PostgreSQL 17.6 で採りました。

例には、次の社員表 emp を使います。boss_id が上司の id です。

 id |       name        | boss_id |   dept    | salary
----+-------------------+---------+-----------+--------
  1 | President Sato    |         | Executive |  10000
  2 | VP Tanaka         |       1 | Sales     |   6000
  3 | VP Suzuki         |       1 | Dev       |   6500
  4 | Manager Takahashi |       2 | Sales     |   4500
  5 | Manager Watanabe  |       3 | Dev       |   4800
  6 | Staff Ito         |       4 | Sales     |   3200
  7 | Staff Yamamoto    |       4 | Sales     |   3000
  8 | Staff Nakamura    |       5 | Dev       |   3500
  9 | Staff Kobayashi   |       5 | Dev       |   3300
 10 | Intern Kato       |       5 | Dev       |   2000

まず見る（結論）

  • 書き方は WITH 名前 AS (SELECT ...) SELECT ... FROM 名前。複数あるならカンマでつなぐ
  • CTE はその1文の中でしか使えない。次の文から参照すると relation does not exist
  • 自分自身を参照する（階層をたどる）なら WITH RECURSIVE。付け忘れるとエラー
  • PostgreSQL 12 以降、1回しか参照しない CTE は本体の SQL に展開されて最適化される。展開させたくなければ AS MATERIALIZED

基本の書き方

部署ごとの平均給与を CTE で求め、部署の平均より給与が高い人を出します。

WITH dept_avg AS (
    SELECT dept, avg(salary) AS avg_sal
      FROM emp
     GROUP BY dept
)
SELECT e.id, e.name, e.dept, e.salary, round(d.avg_sal, 2) AS dept_avg_sal
  FROM emp e
  JOIN dept_avg d ON e.dept = d.dept
 WHERE e.salary > d.avg_sal
 ORDER BY e.dept, e.salary DESC;
 id |       name        | dept  | salary | dept_avg_sal
----+-------------------+-------+--------+--------------
  3 | VP Suzuki         | Dev   |   6500 |      4020.00
  5 | Manager Watanabe  | Dev   |   4800 |      4020.00
  2 | VP Tanaka         | Sales |   6000 |      4175.00
  4 | Manager Takahashi | Sales |   4500 |      4175.00
(4 rows)

CTE を2つ以上使うときは、WITH を1回だけ書き、カンマで区切ります。後の CTE から前の CTE を参照することもできます。

WITH dept_avg AS (
    SELECT dept, avg(salary) AS avg_sal FROM emp GROUP BY dept
),
company_avg AS (
    SELECT avg(salary) AS all_avg FROM emp
)
SELECT e.name, e.salary, round(d.avg_sal, 2) AS dept_avg, round(c.all_avg, 2) AS company_avg
  FROM emp e
  JOIN dept_avg d ON e.dept = d.dept
 CROSS JOIN company_avg c
 WHERE e.salary > c.all_avg
 ORDER BY e.salary DESC;

CTE は、それを定義した1文の中でしか使えません。

WITH x AS (SELECT * FROM emp WHERE id = 1) SELECT * FROM x;   -- 1行返る
SELECT * FROM x;
ERROR:  relation "x" does not exist
LINE 1: SELECT * FROM x;
                      ^

複数の文で同じ結果を使い回したいなら、一時表（CREATE TEMP TABLE ... AS）かビューにします。

WITH RECURSIVE で階層をたどる

社長（id=1）から下の全員を、階層の深さ（level）付きで出します。UNION ALL の前が起点、後ろが「1段下をたどる」部分です。後ろの部分が CTE 自身（emp_tree）を参照しています。

WITH RECURSIVE emp_tree AS (
    SELECT id, name, boss_id, 1 AS level
      FROM emp
     WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.boss_id, t.level + 1
      FROM emp e
      JOIN emp_tree t ON e.boss_id = t.id
)
SELECT level, id, name, boss_id
  FROM emp_tree
 ORDER BY level, id;
 level | id |       name        | boss_id
-------+----+-------------------+---------
     1 |  1 | President Sato    |
     2 |  2 | VP Tanaka         |       1
     2 |  3 | VP Suzuki         |       1
     3 |  4 | Manager Takahashi |       2
     3 |  5 | Manager Watanabe  |       3
     4 |  6 | Staff Ito         |       4
     4 |  7 | Staff Yamamoto    |       4
     4 |  8 | Staff Nakamura    |       5
     4 |  9 | Staff Kobayashi   |       5
     4 | 10 | Intern Kato       |       5
(10 rows)

新しい行が1件も見つからなくなった時点で、たどるのを終えます。データに循環（A の上司が B、B の上司が A）があると終わらなくなります。循環がありうる表では WHERE t.level < 20 のように上限を付けます。

RECURSIVE を付け忘れると、自分自身を参照した部分でエラーになります。

ERROR:  relation "emp_tree" does not exist
LINE 8:     JOIN emp_tree t ON e.boss_id = t.id
                 ^
DETAIL:  There is a WITH item named "emp_tree", but it cannot be referenced from this part of the query.
HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.

CTE は遅いのか（MATERIALIZED）

PostgreSQL 11 までは、CTE は必ず先に全件を計算してから使われていました。12 以降では、副作用が無く1回しか参照されない CTE を本体の SQL に展開してから最適化します。

EXPLAIN WITH x AS (SELECT * FROM emp) SELECT * FROM x WHERE id = 1;
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using emp_pkey on emp  (cost=0.15..8.17 rows=1 width=76)
   Index Cond: (id = 1)
(2 rows)

外側の WHERE id = 1 が中に入り、主キーの索引が使われています。AS MATERIALIZED を付けると、先に全件を読んでから絞る形になります。

EXPLAIN WITH x AS MATERIALIZED (SELECT * FROM emp) SELECT * FROM x WHERE id = 1;
                          QUERY PLAN
---------------------------------------------------------------
 CTE Scan on x  (cost=17.80..35.35 rows=4 width=76)
   Filter: (id = 1)
   CTE x
     ->  Seq Scan on emp  (cost=0.00..17.80 rows=780 width=76)
(4 rows)

重い計算を1回だけにしたいときは MATERIALIZED、条件を中に入れて索引を使わせたいときは何も付けないか NOT MATERIALIZED にします。2回以上参照する CTE は、何も付けなければ MATERIALIZED と同じ扱いです。

DELETE の結果を INSERT に渡す

CTE の中には、RETURNING を付けた INSERT・UPDATE・DELETE も書けます。削除した行を履歴表に移す処理を1文で書けます。

WITH d AS (
    DELETE FROM emp WHERE id = 10 RETURNING *
)
INSERT INTO emp_archive SELECT * FROM d;
INSERT 0 1

この後、emp は 9件、emp_archive は 1件（id=10 の行）になりました。1文なので、途中で失敗すれば削除も挿入も取り消されます。

RELATED PostgreSQL