mysqlでデータを管理していてよくある集計のひとつして、
様々な条件を満たすレコード数を取得するというものがある。
- 会員の年代傾向を取得したい
- 有効な会員数を取得したい
- 1000円以上の商品数を取得したい
このうちの年齢傾向を取得するケースを見ていきたいと思います。
オーソドックスなやりかた
一番簡単なやり方としては、以下のようなSQLを6回発行するパターン。
正直苦痛でしかないですね。。
select count(1) as '10歳未満' from user where age < 10;
select count(1) as '10代' from user where age >= 10 and age < 20;
select count(1) as '20代' from user where age >= 20 and age < 30
select count(1) as '30代' from user where age >= 30 and age < 40;
select count(1) as '40代' from user where age >= 40 and age < 50;
select count(1) as '500歳以上' from user where age >= 50;
本題
記事タイトルにも書いたように以下のポイントに注目するともう少し上手いとり方が出来ます。
- 0による除算はNULLになる
- count関数は非NULL値の数を返す
テストデータ
会員(user)テーブル
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| user_name | char(30) | NO | PRI | | |
| age | int(11) | YES | | NULL | |
| enabled | tinyint(1) | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
テストデータ
mysql> select * from user;
+-----------+------+---------+
| user_name | age | enabled |
+-----------+------+---------+
| hashimoto | 23 | 1 |
| inoue | 47 | 1 |
| ito | 14 | 1 |
| jonson | 56 | 1 |
| matsumoto | 10 | 1 |
| nagashima | 21 | 0 |
| saito | 34 | 0 |
| sakurai | 36 | 1 |
| sasaki | 17 | 0 |
| suzuki | 20 | 1 |
| takayama | 21 | 1 |
| watanabe | 31 | 1 |
| yamazaki | 8 | 0 |
+-----------+------+---------+
13 rows in set (0.00 sec)
このデータを使って、年齢傾向を一括で取得できる書き方が以下のクエリ。
クエリ
select
count(1) as '会員数',
count(1/(age < 10)) as '10歳未満',
count(1/(age >= 10 and age < 20)) as '10代',
count(1/(age >= 20 and age < 30)) as '20代',
count(1/(age >= 30 and age < 40)) as '30代',
count(1/(age >= 40 and age < 50)) as '40代',
count(1/(age >= 50)) as '50歳以上'
from
user;
結果
+--------+----------+------+------+------+------+----------+
| 会員数 | 10歳未満 | 10代 | 20代 | 30代 | 40代 | 50歳以上 |
+--------+----------+------+------+------+------+----------+
| 13 | 1 | 3 | 4 | 3 | 1 | 1 |
+--------+----------+------+------+------+------+----------+
簡単！
補足
ちなみにSum関数を使っても同様のことは出来るのでどっちの書き方をするかは好みの問題かなと思います。
sumを使うパターン
select
count(1) as '会員数',
sum(case when age < 10 then 1 else 0 end) as '10歳未満',
sum(case when age >= 10 and age < 20 then 1 else 0 end) as '10代',
sum(case when age >= 20 and age < 30 then 1 else 0 end) as '20代',
sum(case when age >= 30 and age < 40 then 1 else 0 end) as '30代',
sum(case when age >= 40 and age < 50 then 1 else 0 end) as '40代',
sum(case when age >= 50 then 1 else 0 end) as '50歳以上'm
from
user;