WITHを使った一時テーブルの作成

一時テーブルって、途中の結果を保存してあとで使いたいときにめっちゃ便利なんだよね。プログラミングで言うと、同じ長いコードを何回もコピペする代わりに、一回変数に入れて使い回す感じ。SQLの世界だと、こういう「変数」っぽい役割を一時テーブルが担ってる。

でもCTEを使うと、さらに簡単に一時テーブルを作れるんだ。余計な手間がいらない：

• 削除の心配なし。

  CTEはクエリ実行中だけ生きてる。SQLが終わった瞬間に、まるで見えないアシスタントみたいに消えてくれる。跡形も残らない。

• コードがわかりやすくなる。

  CTEを作るのは、物理的な一時テーブルを作って消すよりずっと簡単。全部その場で完結してて、見通しがいい。

• 「一回だけ使う」タスクに最適。

  データを一時的に処理したいだけなら、CTEがぴったり。速いし、クリーンだし、副作用もない。

一時テーブルとしてのCTE構文

まずはCTEの構文をおさらいしよう：

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

ここでWITHは一時的な「テーブル式」を作ってくれる。これはクエリ全体で使えるけど、クエリが終わったら消える。見た目はテーブルだけど、実体は一時的なものだよ。

例：優秀な学生を数えてみよう

CTEを使って、一時テーブルを作り、平均点（grade）が90を超えてる学生を探してみよう。そのリストを出力するよ。

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

ここでやってること：

• WITHで一時テーブルhigh_achieversを作ってる。
• CTEの中で、学生ごと（student_id）にgradesをグループ化して、平均点を計算してる。
• SELECT *で、一時テーブルhigh_achieversを普通のテーブルみたいに参照してる。

CTEと一時テーブルの比較

たまに「CTEとCREATE TEMP TABLEで作る一時テーブルって何が違うの？」って疑問が出てくるよね。

同じデータを従来の一時テーブル（CREATE TEMP TABLE）でやるとこうなる：

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- テーブルを削除するのを忘れずに！

CTEで同じことをやると：

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

CTEを使うときと一時テーブルを使うとき

CTEは、サクッと中間データを作ってすぐ使いたいときに最適。削除の心配もいらないし、SQLコードの中で全部見えるから、外でこっそり作った一時テーブルみたいに迷子にならない。クエリがシンプルで読みやすくなるし、複数の処理を順番にやるときも便利。しかもCTEはネストしたり組み合わせたりできて、複雑なロジックも組める（これはまた後で話すね）。

こういう構成は、クエリが一回きりで、その中だけでデータが必要なときにバッチリ。逆に、結果を何回も使いたいとか、セッション中ずっと中間データを残したいなら、一時テーブルのほうが信頼できる。特にデータ量が多くてパフォーマンスが大事なときは、物理的な一時テーブルのほうが安定して速い。

要は使い分け。CTEはローカルなデータ処理にサクッと使えるスマートなツール。一時テーブルは、もっと長く使う・複雑なシナリオ向けの頼れるやつ。

例：データの集約

例えば、enrollmentsテーブルがあって、どの学生がどのコースに登録してるか記録されてるとする。各コースに何人の学生がいるか知りたい。ただし、6人以上のコースだけを対象にしたい。

CTEでやるとこう：

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

複数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
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

このクエリにはCTEが2つある：

1. high_achieversで優秀な学生を探す。
2. student_coursesで学生とコースを紐付ける。

結果は、平均点が高い学生とそのコースのリストになるよ。

CTEを使うときのよくあるミス

データが多すぎる。 CTEはPostgreSQLのメモリ上に展開される。もしCTEの結果がめっちゃ大きいと、クエリが遅くなったり、メモリ制限を超えたりすることがある。

使いすぎ。 普通のサブクエリで十分な場面でCTEを使うと、逆にややこしくなることも。

インデックス忘れ。 CTEで大きなテーブルからデータを取るとき、インデックスがないとクエリが遅くなる。