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 |
