Menu

PostgreSQL CTE:WITH 句と WITH RECURSIVE の使い方

共通テーブル式 (CTE) は WITH 句でサブクエリに名前を付け、メインのクエリからテーブルのように読めるようにします。WITH RECURSIVE なら自分自身を参照して木やグラフをたどれます。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

共通テーブル式 (CTE) は、メインの文の前にある WITH 句で名前を付けたサブクエリです: WITH name AS (SELECT ...) SELECT ... FROM name。メインのクエリは CTE をテーブルのように読むので、入れ子のサブクエリが上から下へ読める手順になります。

CTE totals が顧客ごとに注文を合計し、メインのクエリが 100 を超える顧客を残します。300 の cy と 180 の ana です。CTE はそれが属する文の中にしか存在しません。

複数の CTE をつなげる

CTE はカンマで区切ります。それぞれの CTE は、前に定義した CTE を読めます。

average は totals を読み、メインのクエリは両方を読みます。顧客ごとの合計は 180、70、300 で平均は約 183 なので、それを超えるのは cy だけです。

WITH RECURSIVE で木構造をたどる

WITH RECURSIVE を使うと、CTE が自分自身を参照できます。UNION ALL でつないだ2つの部分からなります。

  • 開始の行を返すアンカークエリ
  • CTE をテーブルと結合して次の階層を探す再帰クエリ

PostgreSQL は前のステップが生成した行に対して再帰部分を実行し、ステップが行を返さなくなると止まります。

各ステップは、前のステップの行に直属する人を探します。Mia は深さ 3、パス Rosa > Omar > Ivo > Mia になります。木全体ではなく1人のマネージャーの下にいる全員を出すには、アンカーの WHERE を id = 2 に変えます。

社員から上へ、トップまでたどるには、結合条件を s.id = c.manager_id に入れ替えます。

再帰 CTE の無限ループを止める

再帰 CTE は、ステップが何も返さなくなったときにしか終わりません。ループするデータ (A が B の部下で、B が A の部下) では終わりません。WHERE c.depth < 20 のような深さの上限を付けるか、CYCLE 句 (PostgreSQL 14 以降) を使います。CYCLE は最初に繰り返した行に印を付け、その先をたどるのをやめます。

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM staff WHERE id = 5
  UNION ALL
  SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;

再帰 CTE で行を生成する

再帰部分にテーブルは必要ありません。次の例は、各行の値を次へ渡して 1 から 5 まで数えます。

単純な数値や日付の連番なら generate_series のほうが短く書けます。

データを変更する CTE で行を移す

CTE は RETURNING 句付きの INSERT、UPDATE、DELETE にでき、メインの文はその返った行を読みます。次の例は、古い注文を1つの文でアーカイブ用のテーブルへ移します。

注文 1 と 3 は削除とアーカイブへの挿入が一緒に行われるので、失敗しても両方のテーブルは元のままです。文のすべての部分は同じスナップショットを見ます。メインのクエリには DELETE による orders の変化は見えず、見えるのは RETURNING が渡すものだけです (RETURNING を参照)。

MATERIALIZED でインライン展開を制御する

PostgreSQL 12 以降、メインのクエリが1回だけ参照する副作用のない CTE はインライン展開されます。プランナーは外側のクエリと一緒に最適化するので、CTE の外に書いた WHERE でもインデックスを使えます。2回以上参照される CTE は1回だけ計算され、結果が使い回されます。どちらの動きも強制できます。

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;

WITH recent AS NOT MATERIALIZED (...)

MATERIALIZED は CTE を単独で1回計算し、外側の WHERE を中に持ち込みません。インライン展開した実行計画のほうが遅かったときに使います。NOT MATERIALIZED は、CTE が複数回参照されてもインライン展開を求めます。再帰 CTE とデータを変更する CTE は、常に別に計算されます。

よくある質問

PostgreSQL で CTE はサブクエリより速いですか?

たいていはどちらも同じです。PostgreSQL 12 以降、1回だけ参照され副作用のない CTE はメインのクエリにインライン展開されるので、同等のサブクエリと同じ実行計画になります。バージョン 12 より前はすべての CTE が別に計算され、インデックスが使えなくなることがありました。

UPDATE や DELETE で CTE を使えますか?

使えます。WITH は INSERT、UPDATE、DELETE、MERGE の前に書けます。CTE そのものを RETURNING 付きの INSERT、UPDATE、DELETE にすることもでき、メインの文はその行を読めます。

再帰 CTE の無限ループを止めるには?

再帰部分に、いずれ行を返さなくなる条件を付けます。たとえば深さの上限 (WHERE depth < 10) です。自分自身に戻るループがありうるデータには、CYCLE 句 (PostgreSQL 14 以降) を使うと、繰り返した行に印を付けてそこで止まります。

1つの CTE を複数のクエリで使えますか?

使えません。CTE は書かれた文の中にしか存在しません。複数の文でクエリを使い回すには、ビューか一時テーブルを作ります。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める