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 で、かつ、データ量もそこそこ多かったという背景もありました。