複数のCTEを組み合わせる

このレッスンでは、複数のCTEを組み合わせて使う方法を学び、多段階の集計処理を読みやすく書けるようになることを目標にします。「SQL 複数CTE 使い方」で調べている方向けの内容です。

WITH 名前1 AS (...), 名前2 AS (...)のように、カンマで区切ると複数のCTE(共通テーブル式)を同時に定義できます。段階を分けて計算したい複雑な集計を、読みやすく分割して書けます。

サンプルコードでは、class_avgでクラスごとの平均点を計算し、その結果を使ってabove_avgで平均を上回る生徒を探しています。1つ目のCTEの結果を、2つ目のCTEの中でそのまま参照できる点がポイントです。

初心者がよくつまずくのが、CTE同士の依存関係を意識せずに書いてしまうことです。後で定義したCTEは、前に定義したCTEを参照できますが、その逆はできません。上から順番に「積み木を積むように」処理を組み立てていく感覚を持つと理解しやすくなります。

「まずクラスの平均点を計算し、次にそれを上回る生徒を探す」といった多段階の処理も、それぞれのステップに名前をつけて順番に書けるため、あとから読んだときの理解しやすさが大きく向上します。

CREATE TABLE students (id INTEGER, name TEXT, class_id INTEGER, score INTEGER);
INSERT INTO students VALUES (1,'たろう',1,88), (2,'はなこ',1,95), (3,'じろう',2,60);

WITH class_avg AS (
  SELECT class_id, AVG(score) AS avg_score FROM students GROUP BY class_id
),
above_avg AS (
  SELECT s.name FROM students s
  JOIN class_avg c ON s.class_id = c.class_id
  WHERE s.score > c.avg_score
)
SELECT * FROM above_avg;

OUTPUT / 実行結果

💡 初回実行時はSQLエンジンの読み込みに数秒かかります。

🎯

ここまでできればゴール達成!
複数のWITH句を組み合わせた、段階的なデータ加工ができるようになります。