共通テーブル式（WITH）

CTEとは

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;

CTEを使う理由

CTEの連結

WITH last_30 AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
  SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;

CTEの再利用

WITH recent_users AS (
  SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders'  AS metric, COUNT(*) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users);

CTEのマテリアライズ（PostgreSQL ≤ 11）

-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...

-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...

データ変更CTE

WITH moved AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

DMLでのCTE

WITH cancelled AS (
  UPDATE orders SET status = 'cancelled'
  WHERE created_at < NOW() - INTERVAL '14 days'
    AND status = 'pending'
  RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;

実行順序

CTE・サブクエリ・ビューの比較

CTEの使い過ぎに注意

まとめ

理解度チェック