サブクエリと CTE(WITH句)

複雑なクエリを段階的に書くサブクエリとCTE。読みやすさと再利用性が鍵。

白峰 リリ

1つのSQLが長くなりすぎて読めないことあるよね?

神楽 モニカ 先生

そういう時はCTE?
(WITH句?
)を使うとスッキリするのよ。
段階的に処理を書けるから。

紅林 かえで

サブクエリ?
は SELECT 文の中に SELECT を入れ子にする書き方。
CTE は WITH 句で名前付きの一時結果を先に定義してから使う書き方。
CTE の方が読みやすい。

-- 各カテゴリ平均より売上が高い注文を抽出 (CTE版)
WITH category_avg AS (
  SELECT category, AVG(amount) AS avg_amount
  FROM orders
  GROUP BY category
)
SELECT o.order_id, o.category, o.amount, ca.avg_amount
FROM orders o
INNER JOIN category_avg ca ON o.category = ca.category
WHERE o.amount > ca.avg_amount
ORDER BY o.amount DESC;

藍沢 しずく

WITH の中身って、先に計算しておくものなんですかぁ?

紅林 かえで

そう、'category_avg' という名前で『カテゴリ別平均』を先に計算しておき、その後の本クエリで使う。 名前付きの一時テーブルみたいなイメージ。

白峰 リリ

サブクエリで書くとどうなる?

-- 同じ処理をサブクエリで書く例
SELECT order_id, category, amount
FROM orders o
WHERE amount > (
  SELECT AVG(amount)
  FROM orders
  WHERE category = o.category
);

紅林 かえで

これは相関サブクエリ?
と呼ばれて、外側の各行に対して内側のSELECTが評価される。 シンプルだけどパフォーマンスが悪くなりやすい。

神楽 モニカ 先生

CTE版の方が一回計算して JOIN するから、効率的なケースが多いのよ。
それに読みやすさも段違いね。

白峰 リリ

再帰CTEってのもあるって聞いたよ!

紅林 かえで

WITH RECURSIVE で組織図のような階層構造を辿れる。 ただDS検定★では深く問われないので、基本のCTEを押さえれば十分。

藍沢 しずく

CTE で何段階も書けたりするんですかぁ?

神楽 モニカ 先生

そうなのよ。 WITH a AS (...), b AS (...), c AS (...) と複数定義できるの。
後ろの CTE は前の CTE を参照できるから、複雑な処理も段階的に組み立てられるのよ。