複雑なクエリの可読性を上げるためのCTEの使い方

例えば、いくつかの関連した処理を一気にやる大きなSQLクエリを書かなきゃいけないとする。サブクエリを何重にもネストして書くこともできるけど、そうするとスパゲッティコードみたいになっちゃう。まさにSQL迷路で、自分でも迷子になるレベル。

CTEはそんな時の救世主！CTEを使えば、複雑なクエリを論理的なパーツごとに分けて、それぞれに名前を付けて整理できる。だからクエリが分かりやすくてメンテしやすくなるんだ。

比較：サブクエリ vs CTE

一見どっちも同じことをしてるように見えるよね ― コースごとに成績をフィルタして、各学生の平均点を出してる。でもよく見ると、サブクエリ版はロジックがカッコの中に「隠れて」て、CTE版は外に出してfiltered_gradesって分かりやすい名前を付けてる。もし中間処理が2つじゃなくて10個あったらどう？

サブクエリ:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

10個の違いを探してみて。やっぱりCTEの方が読みやすさで圧勝！

CTEで複雑なクエリを段階的に分ける

CTEを使えば、クエリをステップごとに組み立てて、各段階の結果がめっちゃ分かりやすくなる。例えば、学生ごとのコース平均点リストを作って、さらに担当教師の情報も付けたい時は、タスクをいくつかのパートに分けてみよう。

例:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

これ、めっちゃ読みやすくない？1ヶ月後に見返しても、構造が一目瞭然だよ。

大きなレポートで複数CTEを使う

もっと複雑なレポートの例を見てみよう。例えば大学のデータベースがあって、成績優秀な学生とそのコース、担当教師のレポートを作りたいとする。流れはこんな感じ：

1. まず平均点が90超えの学生を探す。
2. 次に、その学生とコースを紐付ける。
3. 最後に、教師の情報を追加する。

複数CTEを使ったクエリ:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

このクエリのいいところは、タスクごとにロジックを独立したブロックに分けてること。誰がすごい成績を取ったか知りたい？CTEのhigh_achieversを見てみて。コースとの関係は？それはstudent_courses。教師は？全部teachersにある。こうやって書くと、保守や修正がめっちゃ楽になるよ。

複雑な計算を段階的に分ける

時々、クエリに複雑な計算やフィルタが必要なこともある。全部を1つの長いクエリに詰め込むんじゃなくて、いくつかのCTEに分けてみよう。

例:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

ここでは、まずcourse_statsでコースごとの統計を集めて、次にpopular_coursesで人気コースをフィルタして、最後にコーステーブルと結合してる。こうやって中間ステップを分けると、クエリの理解がめっちゃ楽になるよ。

CTEが本領発揮するのはどんな時？

CTEが特に便利なシーンをいくつか挙げるね：

1. 分析やレポート作成。例えば、グループごとに複雑な指標を計算したい時。
2. 階層構造の処理。カテゴリツリーや組織構造を作る再帰CTEとか。
3. データの再利用。同じ抽出結果をクエリのいろんな段階で使いたい時。

CTEでよくあるミス

もちろん、CTEも強力な分だけ落とし穴がある。

データの無駄なマテリアライズ。PostgreSQLだとCTEはデフォルトで「マテリアライズ」されて、一時的に結果が保存される。データ量が多いと遅くなることも。これを避けるにはインデックスを使ったり、必要最小限のカラムだけ選ぶようにしよう。

間違ったJOIN。複数CTEを使った複雑なクエリは最適化しづらくなることも。EXPLAINやEXPLAIN ANALYZEで必ずクエリをチェックしよう。

CTEの使いすぎ。CTEが長くてごちゃごちゃしてきたら、クエリをいくつかの独立した処理に分けるサインかも。