MySQLを使って年齢で世代ごとの人数を表示するプログラム

Question

まだ勉強し始めて三日ほどしか経っていないMySQL初心者です。
現在の日付と生年月日から年齢を求める式を用いて、世代ごとの人数を表示するプログラムを書いています。
case文を使って範囲を指定し、条件に当てはまるものを表示するようにしたいのですが、エラーがでてしまい、想像している実行結果とは異なるものが表示されてしまいます。

以下プログラムと実行結果です。見やすいように改行しています。
因みに year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )はXXXX-YY-ZZという年-月-日の日付から年齢を表示する式です。

select case birthday
when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 10
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 20
then '10代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 20
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 30
then '20代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 30
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 40
then '30代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 40
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 50
then '40代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 50
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 60
then '50代'

else '定年' end as 世代, 
count( (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) ) as 人数

from emp3

group by case birthday
when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 10
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 20
then '10代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 20
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 30
then '20代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 30
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 40
then '30代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 40
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 50
then '40代'

when 
(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) >= 50
and (( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )) < 60
then '50代'

else '定年' end;


+--------+--------+
| 世代   | 人数   |
+--------+--------+
| 定年   | 1500 |
+--------+--------+
1 row in set, 1218 warnings (0.01 sec)

Warning (Code 1292): Incorrect datetime value: '1'

エラー文は一行ではなく複数表示されています。
emp3の中身は１５００行分このようになっています。
お願いします。
+------+--------------+--------------+------------+---------+
| id | name | address | birthday | dept_id |
+------+--------------+--------------+------------+---------+
| 1 | 安斎 | 神奈川県 | 1969-10-13 | 3 |
| 2 | 神崎 | 愛媛県 | 1972-01-10 | 5 |
| 3 | 若山 | 兵庫県 | 1961-12-09 | 8 |
| 4 | 西野 | 神奈川県 | 1993-05-27 | 7 |
| 5 | 曽我 | 福岡県 | 1981-04-15 | 3 |
| 6 | 岡 | 滋賀県 | 1997-02-15 | 6 |
| 7 | 立石 | 岡山県 | 1950-08-16 | 1 |

Answer 1

kantomiさんの、ユーザ変数を使った方法ですが

MySQLのマニュアルと
OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村明治さんによると
1ステートメントでユーザ変数を複数回使用した場合の
ユーザ変数の評価順序は未定義です。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

具体的には、Select句の1列目のユーザ変数が、評価されてから、
Select句の2列目以降が評価ｓれることが保証されません。
https://bugs.mysql.com/bug.php?id=79753

結果が保証されないといえば、
OrderBy句がないと出力順が保証されないのは有名ですね。

Comments

[KiyoshiMotoki]

回答ではありません。
コメント欄を使用してください。

[kantomi]

複数回代入する場合と、一度代入したものを参照するのは意味が違います。
参照しても値は変わりません。

[kantomi]

間違いだから。

[KiyoshiMotoki]

kantomi様

tamako様が仰っていること自体はkantomi様のケースには当てはまりませんが、
kantomi様が提示されているユーザー変数の使用方法も、動作が保証されていません。

MySQLのマニュアルに、以下の通り説明されています。

https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html
> 一般的なルールとして、SET ステートメント以外では、同じステートメント内で、ユーザー変数に値を割り当ててその値を読み取ることは決してしないでください。
> （中略）
> SELECT などのほかのステートメント(*)では、予想した結果が得られることもありますが、これは保証されません。

* SETステートメントのこと

[tamako]

リンク先は
「Setステートメントでユーザ変数を複数回使った時の評価順序は保証しないよ」
という、MySQLの公式回答でしたね。

「Select文でユーザ変数を複数回使った時の評価順序は保証しないよ」
という、MySQLの公式回答は、こちらをどうぞ
http://bugs.mysql.com/bug.php?id=41741

[tamako]

>複数回代入する場合と、一度代入したものを参照するのは意味が違います。
>参照しても値は変わりません。

意味がよく分からないのですが、
emp3テーブルが2行以上あったら、@ageというユーザ変数に複数回代入しませんか？

Answer 2

year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) )
ではなく
year(curdate()) - year(birthday) - ( right(curdate(),5) < right(birthday,5) )
ではないですか？括弧が多い気がします。

以下追記
見やすくしてみました。

※以下修正しました11:30
もう一度修正しました11:36

select
  case
    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 between 50 and 59 then 50
    else '定年'
  end as `世代`,
  count(*) as `人数`
  from (select year(curdate()) - year(birthday) - (right(curdate(),5) < right(birthday,5)) as age from emp3) t1 group by `世代`;

Comments

[banbook18]

ありがとうございます、変えてみます！

[banbook18]

(( year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5) ))から
( year(curdate()) - year(birthday) - ( right(curdate(),5) < right(birthday,5) ))に変更したのですが先ほどと全く同じエラーが出てきてます。

[moonphase]

回答にSQLを追記しましたが、、、何故か全員40代になりますね。はて。

[banbook18]

追記ありがとうございます。
エラーはでなくなたのですが、実行結果が
+--------+--------+
| 世代 | 人数 |
+--------+--------+
| 40 | 1500 |
+--------+--------+
となって出てきます。
｜10　｜　数　｜
｜20　｜　数　｜
｜30　｜　数　｜
と各年齢ごとに対応できますでしょうか？

[moonphase]

SQLを間違ってました。
■修正後
group by `世代`
■修正前
group by '世代'

※カラム名なのでシングルクォートで囲んじゃだめですね・・・

[banbook18]

正しい結果が表示されました！
ありがとうございます！

[banbook18]

因みに最後の一文のt1は何の役割をしてるのでしょうか？
from (select year(curdate()) - year(birthday) - (right(curdate(),5) < right(birthday,5)) as age from emp3) t1(←ここ) group by `世代`;

[moonphase]

(select year(curdate()) - year(birthday) - (right(curdate(),5) < right(birthday,5)) as age from emp3)
の結果をt1というテーブル名（エイリアス）を付けてあげてます。
そのt1テーブルに対してselest case...している感じとなります。

[banbook18]

つまり言い換えれば
from t1 group by `世代`
ということですね！
何度も教えていただきありがとうございます！！

Answer 3

時間かかり既に解決済みになってましたが、ちょっと形が違うのでご参考と言う事で

select case when age>5 then '定年' else concat(age,'0代') end as 世代, sum(cnt) as 人数 FROM (
select truncate(((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5)<RIGHT(birthday,5))) / 10, 0) AS age,
1 AS cnt from emp3
union all select 1,0
union all select 2,0
union all select 3,0
union all select 4,0
union all select 5,0
union all select 6,0
) t1 group by 1 order by 1 ;

Comments

[banbook18]

わざわざありがとうございます！！

[A.Ichi]

どういたしまして、mysqlは、不慣れですが色々出来て楽しいです。

Answer 4

こんな感じにすればよいのでは？

select *
,case 
when birthday between (@c:=curdate() + interval 1 day) - interval 10 year and @c then 'g00'
when birthday between @c - interval 20 year and @c then 'g10'
when birthday between @c - interval 30 year and @c then 'g20'
when birthday between @c - interval 40 year and @c then 'g30'
when birthday between @c - interval 50 year and @c then 'g40'
when birthday between @c - interval 60 year and @c then 'g50'
when birthday between @c - interval 70 year and @c then 'g60'
when birthday between @c - interval 80 year and @c then 'g70'
else 'error'
end as gene
from tbl;

集計するとこう

select 
case 
when birthday between (@c:=curdate() + interval 1 day) - interval 10 year and @c then 'g00'
when birthday between @c - interval 20 year and @c then 'g10'
when birthday between @c - interval 30 year and @c then 'g20'
when birthday between @c - interval 40 year and @c then 'g30'
when birthday between @c - interval 50 year and @c then 'g40'
when birthday between @c - interval 60 year and @c then 'g50'
when birthday between @c - interval 70 year and @c then 'g60'
when birthday between @c - interval 80 year and @c then 'g70'
else 'error'
end as gene
,count(*)
from tbl
group by gene;

Comments

[退会済みユーザー]

幸運にも期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
業務では問題外の品質なので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

[mysql_help]

退会済ユーザ様

減点するなら下記の2点がより適切です。

https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html
>SET ステートメント以外では、同じステートメント内で、
>ユーザー変数に値を割り当ててその値を読み取ることは決してしないでください。
という記載に反して、割り当てた値を、読み取っていることが1点目。
select文の結果が10行なら、同じユーザ変数への10回以上の読取と割当を行ってます。

select文でのユーザ変数への値の代入は5.7で非推奨になり、8.0以降で削除予定の機能であることが2点目。
http://www.mysql.gr.jp/mysqlml/mysql/msg/16488

Answer 5

MySQL限定ですが、変数を使った方がすっきりするでしょう。
まずは、サブクエリだけ実行して確認してみましょう。

SET @age := 0;
SELECT generation AS `世代`, COUNT(*) AS `人数`
FROM
	(SELECT 
	(@age := (year(curdate()) - year(birthday) ) - ( right(curdate(),5) < right(birthday,5))) AS age
	, CASE WHEN @age >= 10 AND @age < 20 then '10代' 
		   WHEN @age >= 20 AND @age < 30 then '20代' 
		   WHEN @age >= 30 AND @age < 40 then '30代' 
		   WHEN @age >= 40 AND @age < 50 then '40代' 
		   WHEN @age >= 50 AND @age < 60 then '50代' 
		   ELSE '定年' END AS generation
	FROM emp3) a
GROUP BY generation

Comments

[kantomi]

最後 ; が抜けているねw

[banbook18]

まだ変数やサブクエリなどわからないことが多いので参考にさせていただきます！
ありがとうございます！

[tamako]

kantomi様

あなたの書いたインラインビューの
Select句の1列目は、
@age := (year(curdate()) - year(birthday) ) 長いので省略 AS age

Select句の2列目は、
CASE WHEN @age >= 10 長いので省略 as generation

そしてMySQLの仕様では、
Select句の2列目を評価してから、Select句の1列目を評価するかもしれないし、
Select句の1列目を評価してから、Select句の2列目を評価するかもしれないのです。
どっちの動作をするかはMySQLの仕様として定義されてません。
(マルチコアCPUが当たり前のこの時代、1列目と2列目がパラレルで処理されるかも)

さらには、全部の行のAgeという仮想列を評価してから
全部の行のgenerationという仮想列を評価する可能性もあります。

なので結果が保証されないのです。

[kantomi]

確かにそのようですね。

[tamako]

プロフィールを拝見したら、SQLの達人の生島さんでしたか
QiitaとSoftware Designの生島さんの連載で、いつも勉強させていただいております。

SQLの達人の生島さんの
SQLの間違いを指摘できるとは、凄く嬉しいです。

[退会済みユーザー]

幸運にも期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
業務では問題外の品質なので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

Answer 6

CURRENT_DATE関数とDATEDIFF関数を使い年齢を出してからCASE式で振り分けた方が良さそうです。
関数を使ってはいけないなら話は別ですが。

Comments

[banbook18]

関数の使い方自体まだ勉強しておらずわからないので、とりあえずはこの状態からできるようにしたいです…。使ってはいけないということはありませんが、研修で習ってないので微妙なところです。