MySQL でデータの範囲ごとにデータの個数を集計する

MySQL のデータを範囲ごとにデータの個数を集計したいと思いました。以下の記事に書いてあることを MySQL でやりたかったので調べました。

[Excel] データの範囲ごとにデータの個数を集計する - ピボットテーブルを利用した 値の範囲ごとの人数集計 (年代、価格帯、得点帯での集計方法)

環境

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.34, for osx10.11 (x86_64) using  EditLine wrapper

準備

今回はサンプルとして0 ~ 100 点までの成績の集計を想定します。

mysql> create database sampletables;
Query OK, 1 row affected (0.02 sec)

mysql> use sampletables
Database changed

mysql> create table student(name varchar(20), score int(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into student (name, score)
    -> values
    -> ('Alice', 80),
    -> ('Bob', 75),
    -> ('Charlie', 63),
    -> ('Carol', 91),
    -> ('Dave', 53),
    -> ('Eve', 88),
    -> ('Ellen', 72),
    -> ('Franc', 47),
    -> ('Isaac', 65),
    -> ('Ivan', 32),
    -> ('Justin', 68),
    -> ('Mallory', 39),
    -> ('Marvin', 49),
    -> ('Mallet', 12),
    -> ('Matilda', 18),
    -> ('Oscar', 70),
    -> ('Pat', 74),
    -> ('Peggy', 37),
    -> ('Victor', 44),
    -> ('Plod', 55),
    -> ('Steve', 98),
    -> ('Trent', 84),
    -> ('Trudy', 52),
    -> ('Walter', 79),
    -> ('Zoe', 77);

mysql> show tables;
+------------------------+
| Tables_in_sampletables |
+------------------------+
| student                |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+---------+-------+
| name    | score |
+---------+-------+
| Alice   |    80 |
| Bob     |    75 |
| Charlie |    63 |
| Carol   |    91 |
| Dave    |    53 |
| Eve     |    88 |
| Ellen   |    72 |
| Franc   |    47 |
| Isaac   |    65 |
| Ivan    |    32 |
| Justin  |    68 |
| Mallory |    39 |
| Marvin  |    49 |
| Mallet  |    12 |
| Matilda |    18 |
| Oscar   |    70 |
| Pat     |    74 |
| Peggy   |    37 |
| Victor  |    44 |
| Plod    |    55 |
| Steve   |    98 |
| Trent   |    84 |
| Trudy   |    52 |
| Walter  |    79 |
| Zoe     |    77 |
+---------+-------+
25 rows in set (0.00 sec)

実行した SQL

mysql> select
    ->     count(*),
    ->     sum(case when score <= 10 then 1 else 0 end) as '1~10',
    ->     sum(case when score > 10 and score <= 20 then 1 else 0 end) as '11~20',
    ->     sum(case when score > 20 and score <= 30 then 1 else 0 end) as '21~30',
    ->     sum(case when score > 30 and score <= 40 then 1 else 0 end) as '31~40',
    ->     sum(case when score > 40 and score <= 50 then 1 else 0 end) as '41~50',
    ->     sum(case when score > 50 and score <= 60 then 1 else 0 end) as '51~60',
    ->     sum(case when score > 60 and score <= 70 then 1 else 0 end) as '61~70',
    ->     sum(case when score > 70 and score <= 80 then 1 else 0 end) as '71~80',
    ->     sum(case when score > 80 and score <= 90 then 1 else 0 end) as '81~90',
    ->     sum(case when score > 90 and score <= 100 then 1 else 0 end) as '91~100'
    -> from
    ->     student;
+----------+------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| count(*) | 1~10 | 11~20 | 21~30 | 31~40 | 41~50 | 51~60 | 61~70 | 71~80 | 81~90 | 91~100 |
+----------+------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
|       25 |    0 |     2 |     0 |     3 |     3 |     3 |     4 |     6 |     2 |      2 |
+----------+------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
1 row in set (0.00 sec)

それぞれの範囲ごとに、範囲におさまっていれば1を、おさまってなければ0を足すという処理にしました。

まとめ

今回はデータの集計作業で使った SQL を紹介しました。

このあと可視化しようとすると Google スプレッドシートでグラフにすることになるので正味、全部 Google スプレッドシートでやったらいいのにという内容でもあるのですが、実際の作業はもうすこし複雑な SQL で、かつ、データ量もそこそこ多かったという背景もありました。