よしたく blog

ほぼ週刊で記事を書いています

GROUP BYで範囲を集計する

SQL の GROUP BY を使うとカラムのデータごとに集計できる。 例えば次のようなデータがあり、GROUP BY を使って SQL を実行する。

実行環境はsqlfiddleで、MySQL5.6を使った。

sqlfiddle.com

データはテストデータ生成サイトで作った。 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