HAVINGでサブクエリを使って集約データをフィルタリングする方法

たまに、ただデータをグループ化して結果をフィルタリングするだけじゃなくて、追加のロジックも考慮したい時があるよね。例えば、グループ内の学生の平均点を何か外部の基準と比べたいとか。そんな時に登場するのがHAVINGとサブクエリのコンボ！SQLクエリの中でより賢い判断ができる強力なツールだよ。

HAVINGを思い出そう

今回は、HAVINGと一緒に使うサブクエリにフォーカスして、集約値レベルでデータをフィルタリングする方法を見ていこう。なんで？WHEREは個々の行をフィルタリングできるけど、HAVINGはグループ化されたデータに使うんだよね。つまり、分析のレベルが違うってこと。これでできることが広がるよ！

サブクエリとHAVINGの組み合わせに入る前に、HAVINGって何か、WHEREとどう違うかをサクッと復習しよう。

• WHEREは、グループ化(GROUP BY)の前に行をフィルタリングする。
• HAVINGは、集約後、つまりデータがグループ化された後にフィルタリングする。

例えば、学生とその成績を分析するとしよう。WHEREを使えば、特定の最低点以下の学生を除外できる。でもHAVINGなら、グループごとの平均点や最大点でグループ自体を除外できるんだ。

データ例

こんな感じの学生テーブルがあるとする：

studentsテーブル：

HAVINGの使い方例（サブクエリなし）

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > 75;

結果：

「History」学部は平均点が75未満だから結果に入ってない。シンプルでしょ？じゃあ、ここにサブクエリの魔法をちょっと足してみよう。次の例では、全学部の平均と比べてフィルタリングできるよ。

HAVINGでサブクエリを使う

HAVINGでサブクエリを使うと、集約データのフィルタリングがめっちゃ柔軟になる！例えば、平均点や最大値を他の集計値と比べたりできる。つまり、「うちの結果、全体平均よりイケてる？」みたいなチェックができるんだ。

例：学部ごとの平均点でフィルタリング

例えば、他の学部より成績が良い学部、つまり学部の平均点が大学全体の平均より高い学部を探したいとしよう。

データはこんな感じ：

studentsテーブル：

まずは全学生の平均点を出してみよう：

SELECT AVG(grade) AS university_avg
FROM students;

次に、HAVINGでサブクエリを使う：

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

結果：

ここで何が起きてるかというと：

1. サブクエリ（SELECT AVG(grade) FROM students）が全体の平均点を計算する（この例だと77）。
2. メインクエリは学部ごとに学生をグループ化して平均点を出す。
3. HAVINGで学部の平均点が全体平均より高いものだけを残す。

WHEREとHAVINGの使い分け

違いをもっと分かりやすくするために、例えば平均点より高い点数の学生だけを選びたい場合を考えよう。これはWHEREだけでできる：

SELECT name, grade
FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

結果（さっきのテーブルの場合）：

でも、学部ごとの平均点が大学全体の平均より高い学部を見たい場合は、HAVINGが必須。なぜなら、行じゃなくてグループをフィルタリングするから：

SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > (SELECT AVG(grade) FROM students);

結果：

まとめ：

• WHEREはグループ化前の個々の行に使う。
• HAVINGは集約後のグループに使う。

例：複数の集約値でフィルタリング

もう一つ例を見てみよう。studentsテーブルに学生の成績と学部が入っているとする：

studentsテーブル：

今度は、こんな学部を探したい：

1. 学生の平均点が大学全体の平均より高い。
2. 学部内の最高点が90より高い。

こんなクエリになる：

SELECT department, AVG(grade) AS avg_grade, MAX(grade) AS max_grade
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND MAX(grade) > 90;

このクエリでやってること：

• AVG(grade) > (SELECT AVG(grade) FROM students) — 学部の平均点が全体より高いかチェック。
• MAX(grade) > 90 — 90点超えの学生がいるかチェック。

結果：

「Math」学部だけが、平均点も高くて90点超えの学生もいる唯一の学部だったね。

例：最小のばらつきがあるグループを選ぶ

例えば、学生の最高点と最低点の差が大学全体より小さいグループを探したいとしよう。

使うのはこのstudentsテーブル：

手順はこんな感じ：

1. まず大学全体の最高点と最低点の差を計算：

   SELECT MAX(grade) - MIN(grade) AS range_university
   FROM students;
   

2. 次にメインクエリでサブクエリと比較する：

SELECT department, MAX(grade) - MIN(grade) AS range_department
FROM students
GROUP BY department
HAVING (MAX(grade) - MIN(grade)) < ( SELECT MAX(grade) - MIN(grade) FROM students );

クエリの結果：

「Physics」と「Math」学部は、大学全体より成績のばらつきが小さかった、つまり安定してるってことだね。

HAVINGとサブクエリのクエリ最適化

サブクエリを多用すると、特に大きなデータベースではパフォーマンスに影響が出ることもあるから注意しよう。いくつかアドバイス：

インデックスを使おう。 サブクエリがWHEREやJOINで使うカラムにはインデックスを貼っておこう。

データの膨張に注意。 サブクエリが中間結果を大量に返す場合は、段階的に分けたり一時テーブルを使ったりしよう。

EXPLAINでクエリをプロファイルしよう。 PostgreSQLがどうクエリを実行してるか常にチェックしよう。サブクエリが何度も実行されてたら、最適化を考えてみて。

CTEと比較しよう。 場合によってはWITH（Common Table Expressions）を使った方が速くて読みやすいこともあるよ。これはまた別のレクチャーで話すね :P

サブクエリ・HAVING・GROUP BYの組み合わせ

HAVINGのサブクエリを使えば、集約値や平均値、他の指標も同時に考慮した複雑なフィルタも作れる。リアルなデータから面白いインサイトを見つけるのに役立つよ！

例：学部ごとの平均点と学生数で比較

例えば、こんな学部を選びたいとしよう：

1. 平均点が大学全体の平均より高い。
2. 学生数が、平均点が一番低い学部より多い。

元のstudentsテーブル：

クエリ：

SELECT department, AVG(grade) AS avg_grade, COUNT(*) AS student_count
FROM students
GROUP BY department
HAVING AVG(grade) > ( SELECT AVG(grade) FROM students )
   AND COUNT(*) > (
       SELECT COUNT(*)
       FROM students
       GROUP BY department
       ORDER BY AVG(grade)
       LIMIT 1
   );

このクエリは、HAVINGとGROUP BYでサブクエリを組み合わせて、複数条件で分析できることを示してる。結果：

History学部は平均点も学生数も一番低いから結果に入らなかった。PhysicsとMathはどちらも平均点も学生数も上位だね。

よくあるミスとその防止法

NULLのミス。 データにNULLがあると、HAVINGのサブクエリで予想外の結果になることがある。COALESCEでちゃんと処理しよう：

SELECT AVG(grade)
FROM students 
WHERE grade IS NOT NULL;

サブクエリの余計なデータ。 サブクエリが余計な結果を返すとパフォーマンスが落ちる。サブクエリの条件はしっかり絞ろう。

実行順序の誤解。 HAVINGはグループ化の後に実行されるけど、サブクエリはメインクエリより先に実行されることもあるから注意。

インデックスなし。 サブクエリで使うカラムにインデックスがないと、クエリがめっちゃ遅くなるよ。

HAVINGのサブクエリを使えば、集約レベルでデータ分析の幅が広がる！グループを複雑な条件でフィルタしたり、グループ間で比較したり、分析クエリをどんどん作れるよ。おめでとう、これでリアルなプロジェクトでもバッチリ使えるね！