mysql 年齢を指定した範囲内をGROUP BYしたい
解決済
回答 3
投稿
- 評価
- クリップ 1
- VIEW 4,884
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 |
+----------+------------+------------+-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
+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投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
checkベストアンサー
0
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の
を参考にしました。
以下は動作確認のログです。
[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投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
0
(たとえばテンポラリで）年齢グループテーブルをつくり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です
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 89.98%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる