mysql 年齢を指定した範囲内をGROUP BYしたい

Question

mysqlにて下記のテーブルを使い下記のようなことが出来ないかわかるかたがいらしたら
よろしくお願い致します。

＜したいこと＞
テーブルの「birth」の値を年齢を取得して、
・17歳未満
・18〜24歳
・25〜３４歳
・35〜４４歳
・45〜５４歳
・55〜６４歳
・６５歳以上
でGROUP BYしたいです。

#今まで使っていたsql
年齢計算のSQL
(YEAR(create()) - YEAR(birth)) - (RIGHT(create(), 5) < RIGHT(birth, 5)) AS age from table;



#サンプル
+----------+------------+------------+
| name     | birth      | CURDATE()  |
+----------+------------+------------+
| 田中     | 1982-01-15 | 2018-02-15 |
| 佐藤     | 1992-03-23 | 2018-02-15 |
| 鈴木     | 1970-05-05 | 2018-02-15 |
| 中川     | 1960-12-26 | 2018-02-15 |
| 安田     | 1920-05-21 | 2018-02-15 |
| 高橋     | 1958-08-11 | 2018-02-15 |
| 木村     | 1976-12-31 | 2018-02-15 |
| 加賀     | 1988-02-16 | 2018-02-15 |
| 安井     | 2000-01-30 | 2018-02-15 |
+----------+------------+------------+

Answer 1

今MySQLの環境がないのでこんな感じということで願います。

SELECT
   CASE age_range
       WHEN 1 THEN '17歳未満'
       WHEN 2 THEN '18〜24歳'
       WHEN 3 THEN '25〜34歳'
       WHEN 4 THEN '35〜44歳'
       WHEN 5 THEN '45〜54歳'
       WHEN 6 THEN '55〜64歳'
       ELSE        '65歳以上'
   END AS age_range
 , COUNT(*)
FROM(
    SELECT
        CASE
            WHEN age < 18 THEN 1
            WHEN age < 25 THEN 2
            WHEN age < 35 THEN 3
            WHEN age < 45 THEN 4
            WHEN age < 55 THEN 5
            WHEN age < 65 THEN 6
            ELSE               7
        END AS age_range
    FROM(
        SELECT
            (YEAR(create()) - YEAR(birth)) - (RIGHT(create(), 5) < RIGHT(birth, 5)) AS age 
        from table
        )
    )
GROUP BY age_range
ORDER BY age_range

Answer 2

(たとえばテンポラリで）年齢グループテーブルをつくりbirthdayを
その範囲に当てはめるほうがSQLらしいと思います

• ユーザーテーブル

create table user(id int,name varchar(20),birth date);
insert into user values
(1,'taro','2010-01-01'),
(2,'jiro','2001-02-15'),
(3,'saburo','2001-12-31'),
(4,'hanako','1990-01-01');

• 年齢テーブルをつくってjoin

create temporary table nenrei_group (id int,val varchar(20),start date,end date);
insert into nenrei_group values
(1,'17歳未満',current_date - interval 17 year + interval 1 day,current_date),
(2,'17-24歳',current_date - interval 24 year + interval 1 day,current_date - interval 17 year),
(3,'25-34歳',current_date - interval 34 year + interval 1 day,current_date - interval 24 year);
select name,val from user as t1
inner join nenrei_group as t2
on t1.birth between t2.start and t2.end;

• 集計

create temporary table nenrei_group (id int,val varchar(20),start date,end date);
insert into nenrei_group values
(1,'17歳未満',current_date - interval 17 year + interval 1 day,current_date),
(2,'17-24歳',current_date - interval 24 year + interval 1 day,current_date - interval 17 year),
(3,'25-34歳',current_date - interval 34 year + interval 1 day,current_date - interval 24 year);

select t2.id,val,count(*) as cnt from user as t1
inner join nenrei_group as t2
on t1.birth between t2.start and t2.end
group by t2.id,val
order by t2.id;

nenrei_groupテーブルはテンポラリではなく、普通にテーブルをつくっておいて
日が変わるごとに１度更新をかけるだけでもOKです

Answer 3

MySQL 5.6 にて、以下のクエリで動作を確認しました。
（※テーブル名は、q113552 としています。）

SELECT 
      age_range AS '年齢区分' ,
      COUNT(*) AS '人数'
FROM (
    SELECT 
       name, birth,  
       (@age:=FLOOR(DATEDIFF(NOW(), birth) / 365.25)) AS age,
       (
         CASE 
             WHEN @age < 18 THEN '18歳未満'
             WHEN @age < 35 THEN '18歳以上 34歳以下'
             WHEN @age < 45 THEN '35歳以上 44歳以下'
             WHEN @age < 55 THEN '45歳以上 54歳以下'
             WHEN @age < 65 THEN '55歳以上 64歳以下'
             ELSE '65歳以上'
         END ) AS age_range
     FROM q113552
) table_with_age_range
GROUP BY age_range
ORDER BY  age_range;

　　
年齢を算出する

FLOOR(DATEDIFF(NOW(), birth) / 365.25))

は、stackoverflowの以下の投稿

how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate

の回答に出ていた、シンプルなコード

I have used this query in our production code for nearly 10 years:

SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age

J__ answered Oct 15 '09 at 13:07

の算出方法を（"in our production code for nearly 10 years"と言っているのを信じて）使いました。
ただし、この回答のコメントにあるように、少し不正確なところがあります。

また、年齢の区分によって GROUP BY するのは、同じくstackoverflowの

sql-group-by-age-range

を参考にしました。

以下は動作確認のログです。

[ykt68@macbook15 ~]$ mysql -u root -p teratail_db
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1160
Server version: 5.6.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT name, birth FROM q113552;
+--------+------------+
| name   | birth      |
+--------+------------+
| 田中   | 1982-01-15 |
| 佐藤   | 1992-03-23 |
| 鈴木   | 1970-05-05 |
| 中川   | 1960-12-26 |
| 安田   | 1920-05-21 |
| 高橋   | 1958-08-11 |
| 木村   | 1976-12-31 |
| 加賀   | 1988-02-16 |
| 安井   | 2000-01-30 |
+--------+------------+
9 rows in set (0.00 sec)

mysql> SELECT 
    ->       age_range AS '年齢区分' ,
    ->       COUNT(*) AS '人数'
    -> FROM (
    ->     SELECT 
    ->        name, birth,  
    ->        (@age:=FLOOR(DATEDIFF(NOW(), birth) / 365.25)) AS age,
    ->        (
    ->          CASE 
    ->              WHEN @age < 18 THEN '18歳未満'
    ->              WHEN @age < 35 THEN '18歳以上 34歳以下'
    ->              WHEN @age < 45 THEN '35歳以上 44歳以下'
    ->              WHEN @age < 55 THEN '45歳以上 54歳以下'
    ->              WHEN @age < 65 THEN '55歳以上 64歳以下'
    ->              ELSE '65歳以上'
    ->          END ) AS age_range
    ->      FROM q113552
    -> ) table_with_age_range
    -> GROUP BY age_range
    -> ORDER BY  age_range;
+-------------------------+--------+
| 年齢区分                | 人数   |
+-------------------------+--------+
| 18歳以上 34歳以下       |      3 |
| 35歳以上 44歳以下       |      2 |
| 45歳以上 54歳以下       |      1 |
| 55歳以上 64歳以下       |      2 |
| 65歳以上                       |      1 |
+-------------------------+--------+
5 rows in set (0.00 sec)

mysql>

---

補足

ご質問に挙がっていた、

年齢計算のSQL
(YEAR(create()) - YEAR(birth)) - (RIGHT(create(), 5) < RIGHT(birth, 5)) AS age from table;

を、そのまま使うと以下のようにエラーになりました。質問者様のお使いの MySQL と、私の使っている MySQL とのバージョン違いの問題でしょうか？

mysql> SELECT name, birth, (YEAR(create()) - YEAR(birth)) - (RIGHT(create(), 5) < RIGHT(birth, 5)) AS age FROM q113552;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create()) - YEAR(birth)) - (RIGHT(create(), 5) < RIGHT(birth, 5)) AS age FROM q1' at line 1

Comments

[退会済みユーザー]

DATEDIFFの第三引数にyearやmonthを設定するのはずれすぎるんだっけ？