Chapter 05

データの検索③

JboyHashimoto
JboyHashimoto
2022.10.11に更新

集約関数を使う

SQLでデータに対して何らかの操作や計算を行うには、「関数」を使います。あらかじめ用意されているものがありまして、それがこちらですね。

COUNT : テーブルのレコード数(行数)を数える
SUM : テーブルの数値列のデータを合計する
AVG : テーブルの数値列のデータを平均する
MAX : テーブルの任意の列のデータの最大値を求める
MIN : テーブルの任意の列のデータの最小値を求める

このような関数を、「集約関数」や「集合関数」と呼ぶそうです。集約関数はすべて、複数行の入力から1行を出力する働きを持っています。実際にクエリを実行してどんな結果になるのか見てみましょう!

/* テーブルを集約して検索する */
/* count関数で生徒の数を調べる */
SELECT COUNT(student_name) FROM ClassD;
/* Dクラス全員の点数の合計 */
SELECT SUM(class_score) FROM ClassD;
/* Dクラスの点数の平均点 */
SELECT AVG(class_score) FROM ClassD;
/* Dクラスの点数の最高点 */
SELECT MAX(class_score) FROM ClassD;
/* Dクラスの点数の最低点 */
SELECT MIN(class_score) FROM ClassD;

実行結果

mysql> /* テーブルを集約して検索する */
mysql> /* count関数で生徒の数を調べる */
mysql> SELECT COUNT(student_name) FROM ClassD;
+---------------------+
| COUNT(student_name) |
+---------------------+
|                   8 |
+---------------------+
1 row in set (0.00 sec)

mysql> /* Dクラス全員の点数の合計 */
mysql> SELECT SUM(class_score) FROM ClassD;
+------------------+
| SUM(class_score) |
+------------------+
|              547 |
+------------------+
1 row in set (0.00 sec)

mysql> /* Dクラスの点数の平均点 */
mysql> SELECT AVG(class_score) FROM ClassD;
+------------------+
| AVG(class_score) |
+------------------+
|          68.3750 |
+------------------+
1 row in set (0.00 sec)

mysql> /* Dクラスの点数の最高点 */
mysql> SELECT MAX(class_score) FROM ClassD;
+------------------+
| MAX(class_score) |
+------------------+
|              100 |
+------------------+
1 row in set (0.00 sec)

mysql> /* Dクラスの点数の最低点 */
mysql> SELECT MIN(class_score) FROM ClassD;
+------------------+
| MIN(class_score) |
+------------------+
|               38 |
+------------------+
1 row in set (0.00 sec)

クラスの生徒さんの人数を数えてくれたり、クラスの点数の合計、点数の平均点、最高点、最低点を調べてくれましたね。生徒8人のクラスは少なすぎますが(笑)、少子化が進みすぎたんでしょうね😇

テーブルをグループに切り分ける

GROUP BY句と呼ばれているテーブルをケーキのようにカット(切り分)してくれるクエリがあります。今回のだと簡単すぎてあまり参考にならないかも?

僕もGROUP BY何それ?
理解できたのもSQLゼロからはじめるデータベース操作をやったからですね。スクールでやったどこQLだったかな?
古い本でEclipseとかいうIDEで勉強させれたんですけど、操作の仕方がちっともわからなくて挫折しました😅
どこQLってブラウザに実行環境があるので、Eclipseいらないんですよね。そもそもこれはJAVAを使う人が使うIDEなんで、はじめて勉強した言語がPHPだったんですけど、Visual Sutadio Codeがあれば使えましたね!
Docker使えば環境構築も入りませんでした😇
当時はWindwosで勉強してましたけどね。Laravelの使い方も教えてくれないので全然勉強にならなかったです。コード書かされるだけなら、Udemyの方がコスパいいと1年後に気づく!

スクールに行かなくても昔からある技術は、インターネットで勉強できます。スクールに行くなら現役のメンターがいて、コード書くだけでなく、設計の仕方、データベース設計、HTTPって何?の勉強ができるところがいいですね。
それと、フロントエンドはReact、Vue.js、ホスティングサービスにはAWSを使わないとWeb業界への転職は不可能ですね😇

GROUP BYの注意点

  • SELECT句に書けるものが限定される
  • GROUP BY句にはSELECT句でつけた列の別名は使えない
  • GROUP BY句には集約結果をソートしない
  • WHERE句に集約関数を書くことはできない

GROUP BYやってみる

といってもデータの量が少ないので、参考になるかな...
やはり生徒さんの数を増やそう!

/* 生徒のデータを追加 */
START TRANSACTION;

INSERT INTO
  ClassD
VALUES
  ('0009', '佐藤', 52),
  ('0010', '松下', 94),
  ('0011', '平田', 98),
  ('0012', '須藤', 78);

COMMIT;

/* 点数によって生徒をグループ分けする */
/* 38点の生徒は3名いました! */
SELECT class_score, COUNT(student_name)
  FROM ClassD
GROUP BY class_score;

ああ、でもGROUP BYを実行したら点数にばらつきがあったので、松下さんと平田くんの点数を95点にしました。これで、テーブルの分割をするとどんな結果になるかイメージがつきやすくなったと思います。

/*優秀な生徒の点数を95点に変更して合わせる*/
UPDATE ClassD
  SET class_score = 95
WHERE student_name = '平田';

UPDATE ClassD
  SET class_score = 95
WHERE student_name = '松下';

SELECT * FROM ClassD;

実行結果

mysql> SELECT class_score, COUNT(student_name)
    ->   FROM ClassD
    -> GROUP BY class_score;
+-------------+---------------------+
| class_score | COUNT(student_name) |
+-------------+---------------------+
|         100 |                   1 |
|          95 |                   3 |
|          92 |                   1 |
|          78 |                   2 |
|          38 |                   3 |
|          68 |                   1 |
|          52 |                   1 |
+-------------+---------------------+
7 rows in set (0.00 sec)

95点の生徒が3人いて、78点の生徒が2人いて、38点の生徒が3人いました!
このように、成績の良い生徒さんと悪い生徒さんに、テーブルを分割することができました。やっとGROUP BYの使い方がわかってきました(笑)
何か自分の興味あることをデータ化して、検索したら勉強も捗るし理解しやすくなりましたね。テストの問題を解くだけだとつまらないですからね。