現在エンジニアを目指してPHPを学習中です。
SQLで集計関数の結果をJOINして取得する方法を学んだので、メモです。
前提
以下の2つのテーブル（生徒、テスト結果）があったとして、
各生徒の科目毎の平均点を算出した上で、生徒テーブルにJOINして取得したい
というのが今回やりたいことです。
ちなみにMySQLは5.7.34
↓生徒テーブル
| id | 名前 | 出身 |
|---|---|---|
| 1 | 鈴木 | 東京 |
| 2 | 佐藤 | 東京 |
| 3 | 高橋 | 沖縄 |
↓テスト結果テーブル（）
| 生徒id | 英語 | 数学 |
|---|---|---|
| 1 | 60 | 30 |
| 2 | 20 | 100 |
| 3 | 80 | 70 |
| 1 | 70 | 30 |
| 1 | 90 | 50 |
| 3 | 100 | 80 |
↓取得したいデータ
| id | 名前 | 出身 | 英語平均 | 数学平均 |
|---|---|---|---|---|
| 1 | 鈴木 | 東京 | ||
| 2 | 佐藤 | 東京 | ||
| 3 | 高橋 | 沖縄 |
先に結論：「JOIN hoge ON」 のhogeに集計関数のSQLをはめる
色々調べた結果、今回は以下のSQLを流すことで希望のデータを取得できました。
SELECT * FROM 生徒 LEFT JOIN (SELECT 生徒id, AVG(英語), AVG(数学) FROM review GROUP BY 生徒id) AS avg ON 生徒.id = avg.生徒id
組み立ての順序
順を追って見ていきましょう
①まずは集計結果を取得するSQLを用意
はじめに、集計結果を取得する関数を用意します。集計関数は以下が代表的なものらしい。
（記載方法がわかりやすくなるよう、SELECTもつけてます）
SELECT COUNT(x) /* xの件数を取得 */
SELECT AVG(x) /* xの平均を取得 */
SELECT SUM(x) /* xの合計を取得 */
SELECT MIN(x) /* xのなかで最小値を取得 */
SELECT MAX(x) /* xのなかで最大値を取得 */
今回であれば、テスト結果テーブルから生徒ごとに英語・数学の平均点を取得したいです。
以下のSQLで、平均点を取得できます。
SELECT 生徒id, AVG(英語), AVG(数学) FROM テスト結果 GROUP BY 生徒ID
②JOIN元となるテーブルの関数を用意
今回は、生徒テーブルを基準に、平均点をJOINしたデータを出したいです。
なので以下。（WHERE句などついていても問題ありません）
SELECT * FROM 生徒 LEFT JOIN /**/ ON 生徒.id = テスト結果.生徒id
③2つのSQLをドッキング
②のSQLにおける/**/の部分に①を結合して完成です。
なお①の関数にはASで別名をつけます。今回はavgとしました。
ONの部分も若干書き換わっているので注意です。
SELECT * FROM 生徒 LEFT JOIN (SELECT 生徒id, AVG(英語), AVG(数学) FROM review GROUP BY 生徒id) AS avg ON 生徒.id = avg.生徒id
まとめ
今回の考え方ですが、
①の集計関数で新たなテーブルを作成し、③で生徒テーブルと新テーブル（avgと命名）を結合した
と考えるとわかりやすいかなと思います。
↓avg テーブル
| 生徒id | 英語平均 | 数学平均 |
|---|---|---|
| 1 | ||
| 2 | ||
| 3 |
そのため、①のSQLでは後々の結合に必要な生徒idも取得しておかないと、③でエラーになります。
↓以下はエラー
SELECT AVG(英語), AVG(数学) FROM テスト結果 GROUP BY 生徒ID
今回は以上です。SQL深めたい。。
※内容や考え方に間違い、補足等ありましたらぜひ教えていただけますと幸いです！