SQLでは、あるカラムの値について複数のレコードをまとめて合計値や平均値を求めることができます。このような用途に使うものをグループ関数と呼びます。
テーブルの準備
グループ計算の実習のため、新たに以下のテーブルおよびレコードを追加します。
テーブル名：examination
| カラム名 | データ型 | 長さ | その他 | 意味 |
|---|---|---|---|---|
| student_id | INT | 主キー | 学生のID。meiboテーブルのidを指す | |
| subject | VARCHAR | 10 | 主キー | 教科名 |
| score | INT | 得点 |
examinationに追加するレコード
| student_id | subject | score |
|---|---|---|
| 1 | 数学 | 68 |
| 1 | 英語 | 65 |
| 1 | 国語 | 63 |
| 2 | 数学 | 63 |
| 2 | 英語 | 88 |
| 2 | 国語 | 69 |
| 3 | 数学 | 37 |
| 3 | 英語 | 81 |
| 3 | 国語 | 35 |
| 4 | 数学 | 77 |
| 4 | 英語 | 78 |
※student_id=4、subject=’国語’のデータがないのは間違いではありません
USE dbkiso1;
DROP TABLE IF EXISTS `examination`;
CREATE TABLE `examination` (
`student_id` int(11) NOT NULL,
`subject` varchar(10) NOT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `examination` (`student_id`, `subject`, `score`) VALUES
(1, '国語', 63),
(1, '数学', 68),
(1, '英語', 65),
(2, '国語', 69),
(2, '数学', 63),
(2, '英語', 88),
(3, '国語', 35),
(3, '数学', 37),
(3, '英語', 81),
(4, '数学', 77),
(4, '英語', 78);
ALTER TABLE `examination`
ADD PRIMARY KEY (`student_id`,`subject`);
COMMIT;グループ関数
複数のレコードにまたがる計算を行う関数をグループ関数と言います。ほぼすべてのデータベース処理系で、以下の5つのグループ関数が使用できます。書式はすべて共通で、
関数名（式）
となります。式にはカラム名を使用することが出来ます。各レコードのカラムの値を使用して、様々な計算を求めることが出来ます。
| 関数名 | 動作 |
|---|---|
| AVG | 式の値の平均値を求める |
| MAX | 式の値の最大値を求める |
| MIN | 式の値の最小値を求める |
| SUM | 式の値の合計を求める |
| COUNT | 総数を求める |
COUNTだけはちょっと変わっていて、式に『＊』（アスタリスク）を指定するとレコード総数を、カラム名を指定するとそのカラムの値がNULLではないレコード数を計算します。
例1
たとえば、テーブルexamination中のすべてのレコードについてscoreの平均値を求めるならば、
SELECT AVG(score) FROM examination;とします。
結果は以下のようになります。
グループ関数を使うとすべてのレコードについてまとめた計算が行われるため、演算結果以外は表示されません。
例2
WHERE句と組み合わせて、特定の条件を満たしたレコードについてグループ演算を行うことも出来ます。
たとえば、全員の数学の平均点（subject=’数学’であるレコードのscoreの平均値）を求めるには以下のようにします。
SELECT AVG(score) FROM examination WHERE subject='数学';結果は以下のようになります。
例3
AS句と組み合わせて、グループ計算の結果に名前を付けることも出来ます。
たとえば、student_id=1の学生の3科目の合計点（scoreの総和）を求め、結果表示でカラム名として『合計点』と表示をするには以下のようにします。
SELECT SUM(score) AS `合計点` FROM examination WHERE student_id=1;※合計点 を囲んでいるのはシングルクォートではなくバッククォート（日本語キーボードではSHIFT＋＠）です。
結果は以下のようになります。
例4
グループ関数のうち、COUNTはレコード数を数える機能のため、カラム名に代えて『＊』（アスタリスク）を記述出来ます。
たとえば数学の受験者数（subject=’数学’であるレコードの数）を求めるには以下のようにします。
SELECT COUNT(*) AS `数学受験者数` FROM examination WHERE subject='数学';※AS句で `数学受験者数`を囲んでいるのはバッククォート、WHERE句内で ’数学’ を囲んでいるのはシングルクォートです。間違えないように注意してください。
結果は以下のようになります。
例5
グループ関数の結果を使って、さらに計算をすることも出来ます。
この例では、subject=’数学’であるレコードのscoreの合計値をレコード数で割っています。これは平均値を求める計算なので、当然ながら計算結果は例2と一致します。
SELECT SUM(score)/COUNT(score) FROM examination WHERE subject='数学';結果は以下のようになります。
例6
グループ関数の結果を算術関数の引数とすることも出来ます。この例ではAVG関数でscoreの平均値を求め、さらにROUND関数で小数第1までで丸めています。
SELECT ROUND(AVG(score),1) FROM examination WHERE subject='数学';結果は以下のようになります。
例7
グループ関数に与える引数として、カラム名単体ではなく式を使うことも出来ます。この例の計算結果にはあまり意味はありませんが、まずscoreカラムの値を2倍してから平均を求めています。
SELECT AVG(score*2) FROM examination;結果は以下のようになります。
練習問題
テーブル examination について、以下の結果を求めるSQLを記述しなさい。
- 全科目での最低点を求める（35点）
- 英語の最高点を求める（88点）
- student_id=4 の学生の受験科目数を求める（2科目）
- student_id=2 の学生の、3科目平均点を小数第一位まで求める（73.3点）
コメント