データ準備のためのシンプルなCTE：例とリアルケース

データ準備のためのシンプルなCTE：例とリアルケース

もうCTEの基本はマスターしたっぽいし、WITHもほぼ自動で書けるようになってるよね？今日はもうちょい深掘りして、リアルな場面でCTEをどう使うか見ていこう。たとえばレポートや複雑なSQLクエリを作るとき、まずは材料を分解してから、美味しい分析「スープ」を煮込む感じだよ。

CTEは中間ステップにめっちゃ便利：フィルタ、カウント、集計、平均値計算とか、データ準備に必要なこと全部できる。複雑なクエリも、わかりやすいロジックのブロックに分けて、それぞれが一つのことだけやるようにできる。必要なレコードを選ぶ、平均を出す、最終的なSELECTのためにデータを整える、みたいな感じ。これでコードが読みやすくなるし、同じ処理を何回も書かなくていいし、いらないなら一時テーブルも使わなくてOK。

CTEのやり方は、レポート用のデータ準備や複雑なフィルタ、データの「お掃除」にも超役立つ。つまりCTEはただのテクニックじゃなくて、ロジックを一歩ずつ組み立てるための戦略になるんだ。何が起きてるかコントロールしやすいしね。

準備OK？じゃあ、例を見ていこう！

CTEでデータをフィルタする

CTEは、でかいテーブルから「欲しいデータだけ抜き出す」のに最高。ネストしたクエリをゴリゴリ書く代わりに、まずフィルタして、そのステップに名前をつけて、あとは普通のテーブルみたいに使える。

たとえば、studentsテーブルがあって、学生の成績が入ってるとする：

studentsテーブル

たとえば、成績が85より上の人だけ選びたいとき。CTEなら超シンプルにできる：

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

結果：

ここが便利！

必要な行だけ先に選んで、そのステップにexcellent_studentsって名前をつけた。これを使って、他のテーブルとJOINしたり、さらにフィルタしたり、平均点を計算したりできる。クエリが長くなっても、読みやすくて迷わないよ。

CTEでデータを集計する

次は、レコード数を数えたり、平均値を出したいとき。たとえば、enrollmentsテーブルがあって、どの学生がどのコースに登録してるか入ってるとする。

enrollmentsテーブル

各コースに何人の学生が登録してるか知りたい。

クエリ例：

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

結果：

ここがポイント：

• course_idごとにグループ化して、各コースの学生数をカウントした。
• course_enrollmentsテーブルにこの情報が入ってるから、さらに分析に使える。

レポート用データの準備

複数ステップのデータ処理が必要なレポートを作りたいとき、CTEは本当に便利。ロジックをわかりやすいブロックに分けて、一時テーブルを作らなくてもOK。たとえば、gradesテーブル（成績）とstudentsテーブル（学生情報）があって、平均点が80より上の学生だけのレポートを作りたいとする。

gradesテーブル

studentsテーブル

ネストしたクエリを無理に書かなくても、ステップごとに組み立てられる：

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

最初のステップ（avg_grades）で、各学生の平均点を計算して、80より上の人だけ残す。次のステップ（students_with_grades）で、studentsテーブルとJOINして名前も取ってくる。最後のSELECTで、レポートにそのまま貼れるキレイなテーブルができる。全部計算済み、フィルタ済み、見やすい！

結果：

こういうやり方がCTEの強み！ロジックと構造に集中できて、一時テーブルの作成や削除みたいな余計なことを気にしなくていい。

複雑なメトリクスの計算

たまに、1つのクエリでいろんなデータを組み合わせたいこともある。たとえば、各コースについて：

1. 学生数
2. コースごとの平均点

を計算したいとき。

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

やりがちなミス

CTEを使うとき、うっかりやりがちなミスもある。

まず一つ目は、無駄にCTEを作りすぎること。PostgreSQLだと、CTEが多すぎると一時テーブルとして保存されて、1回しか使わないのにクエリが遅くなることがある。

二つ目は、フィルタの順番ミス。フィルタを変なタイミングでかけたり、違うステップで違う条件にしたりすると、思ったのと違う結果になることがある。大事なデータを早めに消しちゃうことも。

だからCTEは、データを何回も変換するような場面で使うのがベスト。そういうときこそ、CTEの良さが活きて、キレイでわかりやすくて効率的なコードが書けるよ！