SQL の GROUP BY を使うとカラムのデータごとに集計できる。 例えば次のようなデータがあり、GROUP BY を使って SQL を実行する。
実行環境はsqlfiddleで、MySQL5.6を使った。
データはテストデータ生成サイトで作った。 https://tm-webtools.com/Tools/TestData
野尻 成美, 48 杉江 絢子, 40 古畑 詩音, 47 沢井 金之助, 44 大前 伊都子, 22 大隅 一行, 34 今津 陽菜子, 2 東 遥華, 29 河村 一平, 35 堀川 乃愛, 57
age で GROUP BY すると次のようになり、当然だがデータごとに GROUP BY されるのでデータとしてはバラバラになる。 今回は 10 代、20 代、30 代...と、一定の範囲で GROUP BY したいときにどうするかをメモする。
SELECT age, count(*) as 人数 FROM Table1 GROUP BY age ORDER BY age;
age | 人数 |
---|---|
2 | 1 |
22 | 1 |
29 | 1 |
34 | 1 |
35 | 1 |
40 | 1 |
44 | 1 |
47 | 1 |
48 | 1 |
57 | 1 |
1, CASE で分ける
まずは CASE 文を使って分ける方法がある。 考え方含めとてもシンプルに実行できる。
SELECT CASE WHEN age < 10 THEN '10代以下' WHEN age BETWEEN 10 AND 19 THEN '10代' WHEN age BETWEEN 20 AND 29 THEN '20代' WHEN age BETWEEN 30 AND 39 THEN '30代' WHEN age BETWEEN 40 AND 49 THEN '40代' WHEN age >= 50 THEN '50代以上' ELSE NULL END AS 年代, count(*) as 人数 FROM Table1 GROUP BY CASE WHEN age < 10 THEN '10代以下' WHEN age BETWEEN 10 AND 19 THEN '10代' WHEN age BETWEEN 20 AND 29 THEN '20代' WHEN age BETWEEN 30 AND 39 THEN '30代' WHEN age BETWEEN 40 AND 49 THEN '40代' WHEN age >= 50 THEN '50代以上' ELSE NULL END ORDER BY age;
年代 | 人数 |
---|---|
10 代以下 | 1 |
20 代 | 2 |
30 代 | 2 |
40 代 | 4 |
50 代以上 | 1 |
2, 計算をしてグループを作り出す
仮に年代を CASE 文で書くと、最大でも 10 代から 100 代までとなるが、もっと範囲が多いものになると辛くなる。 そういった場合は計算を使ってグループを作り出したほうが便利になる。
今回の年代のケースだと 10 の位があれば分類できるので、年齢を 10 で割り FLOOR で小数点になった 1 の位を切り落とし、GROUP BY している。
SELECT 文では# 1, CASE で分ける
と表記を合わせるために工夫しているが、基本的には次のようになる。
SELECT CONCAT( CASE FLOOR(age / 10) * 10 WHEN 0 THEN 10 ELSE FLOOR(age / 10) * 10 END, '代' ) AS 年代, COUNT(*) AS 人数 FROM Table1 GROUP BY FLOOR(age / 10) ORDER BY age;
年代 | 人数 |
---|---|
10 代 | 1 |
20 代 | 2 |
30 代 | 2 |
40 代 | 4 |
50 代 | 1 |