共通テーブル式 (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 は書かれた文の中にしか存在しません。複数の文でクエリを使い回すには、ビューか一時テーブルを作ります。