【エンジニア教材】MySQL編(3) 集合関数について (SUM,AVG,MAX,MIN,COUNT,GROUP_CONCAT)

まえがき

このドキュメントは社員やインターン生を教育するために作成したものですが、
ぜひ社外の人にも見ていただきたいと思い公開いたしました。

ちょこちょこ更新しますのでリクエストなどございましたらお気軽にお申し付け下さい。
体系的に書き進めていくのでリクエストしていただいたものについてすぐかける保証はございませんのでご理解とご了承のほどよろしくお願いいたします。
もし役に立ったら、いいね！やストックをしてもらえると励みになりますm(_ _)m

ちなみにプロフィールはこちら。
https://www.wantedly.com/users/1955894

SELECT文で集合関数を理解する。

今回はSELECT文で集合関数を使ってレコードを集計するということをやって見ます。
はじめにデータを用意します。
今回はテスト用のデータをランダムに生成してくれるこちらのサイトを用いてデータをインポートしました。
http://kazina.com/dummy/index.html

同じデータを用いて試して見たいという方は次のSQLを実行してデータを入れて見てください。

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `name_kana` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `blood_type` varchar(10) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`id`, `name`, `name_kana`, `email`, `gender`, `age`, `blood_type`, `birthday`)
VALUES
    (1,'大村郁恵','おおむらいくえ','oomuraikue@example.com','F',39,'B','1978/1/23'),
    (2,'片岡寿明','かたおかとしあき','kataokatoshiaki@example.com','M',21,'A','1995/4/21'),
    (3,'岡島慎之介','おかじましんのすけ','okajimashinnosuke@example.com','M',20,'O','1997/2/4'),
    (4,'中田英嗣','なかたひでつぐ','nakatahidetsugu@example.com','M',23,'AB','1994/1/3'),
    (5,'藤村涼子','ふじむらりょうこ','fujimuraryouko@example.com','F',33,'O','1984/1/28'),
    (6,'相川一輝','あいかわかずき','aikawakazuki@example.com','M',35,'A','1981/8/18'),
    (7,'野崎由美子','のざきゆみこ','nozakiyumiko@example.com','F',12,'O','2005/1/20'),
    (8,'井口愛子','いぐちあいこ','iguchiaiko@example.com','F',18,'B','1998/8/6'),
    (9,'小森愛','こもりあい','komoriai@example.com','F',30,'O','1986/7/17'),
    (10,'那須由美子','なすゆみこ','nasuyumiko@example.com','F',13,'A','2003/10/5');

でははじめていきます。

集合関数一覧

一覧は以下のページにあります。
https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html

今回はそのなかでもよく使うもの、知っておいたほうが良いものについて説明します。

関数名	説明
SUM()	合計値を返します
AVG()	平均値を返します
MAX()	最大値を返します
MIN()	最小値を返します
COUNT()	個数を返します
GROUP_CONCAT()	連結された文字列を返します

SUMを用いて合計値を計算する。

SUMは合計値を求める時に使用します。

例：usersの年齢の合計値を求める。

mysql> SELECT SUM(age) FROM users;
+----------+
| SUM(age) |
+----------+
|      244      |
+----------+
1 row in set (0.01 sec)

AVGを用いて平均値を計算する。

AVGはAVERAGEの略で平均値を求める時に使用します。
例：usersの年齢の平均値を求める。

 mysql> SELECT AVG(age) FROM users;
+----------+
| AVG(age) |
+----------+
|  24.4000 |
+----------+
1 row in set (0.00 sec)

MAXを用いて最大値を計算する。

MAXは最大値を求める時に使用します。
例：usersの年齢の最大値を求める。

 mysql> SELECT MAX(age) FROM users;
+----------+
| MAX(age) |
+----------+
|       39       |
+----------+
1 row in set (0.00 sec)

MINを用いて最小値を計算する。

MINは最小値を求める時に使用します。
例：usersの年齢の最小値を求める。

 mysql> SELECT MIN(age) FROM users;
+----------+
| MIN(age) |
+----------+
|       12       |
+----------+
1 row in set (0.00 sec)

COUNTを用いてレコードの件数を計算する。

COUNTは当てはまるレコードの件数を求める時に使用します。
COUNT(*)とすると対象のレコード全件となります。
COUNT(カラム名)とすることも可能で、この場合は対象のレコードのうちそのカラムがNULLでない件数となります。
COUNT(DISTINCT カラム名)とすると、そのカラムで重複した値は1件とみて集計するので、すなわちそのカラムのユニーク件数を取得することができます。

例：usersの件数を求める。

mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|       10       |
+----------+
1 row in set (0.00 sec)

例：usersのblood_typeの種類の数を求める。

mysql> SELECT COUNT(DISTINCT blood_type) FROM users;
+----------------------------+
| COUNT(DISTINCT blood_type) |
+----------------------------+
|                          4                    |
+----------------------------+
1 row in set (0.00 sec)

生データではA,B,AB,Oの4種類が存在するのでこの結果は4となります。

GROUP_CONCATを用いて連結された文字列を取得する。

場合によってはカラムの値を連結させた文字列を利用したいときがあると思います。
GROUP_CONCAT(カラム名)とすると対象レコードのそのカラムをカンマで連結させた値を取得することができます。
GROUP_CONCAT(DISTINCT カラム名)とすることも可能で、この場合はCOUNTのときのように対象レコードのそのカラムの重複を除いたものをカンマで連結させた値を取得することができます。
GROUP_CONCATが効果的に使われる例については今後お話しいたします。

例：blood_typeを連結させた値を取得する。

mysql> SELECT GROUP_CONCAT(blood_type) FROM users;
+--------------------------+
| GROUP_CONCAT(blood_type) |
+--------------------------+
| B,A,O,AB,O,A,O,B,O,A     |
+--------------------------+
1 row in set (0.00 sec)

例：blood_typeを重複をのぞいて連結させた値を取得する。

mysql> SELECT GROUP_CONCAT(DISTINCT blood_type) FROM users;
+-----------------------------------+
| GROUP_CONCAT(DISTINCT blood_type) |
+-----------------------------------+
| B,A,O,AB                          |
+-----------------------------------+
1 row in set (0.00 sec)

ひとつ注意しなければならない点は、GROUP_CONCATの結果が長すぎると後ろが切り捨てられる場合があることです。
GROUP_CONCATの結果の最大長はgroup_concat_max_lenで定義されていて、デフォルトでは1024となっています。
group_concat_max_lenを変更したい場合には以下のコマンドでできます。

SET [GLOBAL | SESSION] group_concat_max_len = 2000;