(MySQL)条件に当てはまる件数をCASEで求めるSQL文

会員データや応募データなど個人情報を管理していたりすると、
アクティブユーザーの統計が気になってきます。

仕事で「応募データの統計をだす」なんて方もいらっしゃるかもしれません。

少し難易度が高めのDB集計ですが、サンプルがあれば意外と簡単にできますので是非ご活用ください。

今回は私の住んでいる地方都市「福岡市」の人口統計を元にSELECT文でどのようにSQLを作ればいいかを書いていきます。

＜具体的な例＞
・男女別で件数を取得したい
・月別で件数を集計したい
・時間帯で応募数の統計を出したい

男女別で件数を取得したい
月別で件数を集計したい
クエリの応用

男女別で件数を取得したい

userテーブルに性別: genderがあったとして。

SELECT
count( CASE WHEN gender = 1 THEN 1 ELSE null END ) as '男性',
count( CASE WHEN gender = 2 THEN 1 ELSE null END ) as '女性'
FROM user

CASE WHEN [条件] THEN [A] ELSE [B] END

条件を満たせばA、条件を満たさなければBという意味になります。

たとえば福岡市の人口のデータがuserテーブルにあったと仮定してSQLを実行すると。。

男性	女性
735,081	821,056

福岡市推計人口 12/1版男女比
http://www.city.fukuoka.lg.jp/soki/tokeichosa/shisei/toukei/jinkou/jinnkousokuhou.html

上記クエリでユーザーテーブルに登録されている男性の数と女性の数を求めることができます。
現時点では暫定数値ですが、本物の福岡市の人口です（笑

あ、年代別ってどんな感じなんだろう・・・
と、思いまして調べてみました。

全国の統計ですが、20代、30代、40代の割合は次のようになるみたいです。

(表1)

年齢	割合
20～24	4.85%
25～29	5.03%
30～34	5.70%
35～39	6.37%
40～44	7.63%
45～49	7.39%

全国の年齢(5歳階級)人口を元に集計
（2016/12月暫定版）
http://www.stat.go.jp/data/jinsui/pdf/201612.pdf

これを福岡市に当てはめてみますと。

(表2)

年齢	男性	女性
20～24	35,677	39,850
25～29	36,951	41,273
30～34	41,932	46,836
35～39	46,797	52,270
40～44	56,064	62,621
45～49	54,326	60,680

割合から算出しただけなのでアバウトですが、
福岡市内にはこんなに人がいるんですね。

技術系ブログの記事を見られている方は男性で20-30代の方が多いかと思います。

私の住んでいる福岡に限れば、世代ごとに4,000人から6,000人も女性が多いことになります！
少し前から言われてはいましたけど、これってすごいことですよね〜

女性が多いってことは、こんなアプリで気軽に恋人がさがせたりも・・・

すみません、大きく脱線しましたm(_ _)m

表2の男女別の年齢別の統計を求めるクエリはこんな感じになります。

先ほどのuserテーブルに年齢: ageがあったとして。

SELECT
count( CASE WHEN gender = 1 AND 20 <= age AND age <= 24 THEN 1 ELSE null END ) as '男性 20-24',
count( CASE WHEN gender = 1 AND 25 <= age AND age <= 29 THEN 1 ELSE null END ) as '男性 25-29',
count( CASE WHEN gender = 1 AND 30 <= age AND age <= 34 THEN 1 ELSE null END ) as '男性 30-34',
count( CASE WHEN gender = 1 AND 35 <= age AND age <= 39 THEN 1 ELSE null END ) as '男性 35-39',
count( CASE WHEN gender = 1 AND 40 <= age AND age <= 44 THEN 1 ELSE null END ) as '男性 40-44',
count( CASE WHEN gender = 1 AND 45 <= age AND age <= 49 THEN 1 ELSE null END ) as '男性 45-49',
count( CASE WHEN gender = 2 AND 20 <= age AND age <= 24 THEN 1 ELSE null END ) as '女性 20-24',
count( CASE WHEN gender = 2 AND 25 <= age AND age <= 29 THEN 1 ELSE null END ) as '女性 25-29',
count( CASE WHEN gender = 2 AND 30 <= age AND age <= 34 THEN 1 ELSE null END ) as '女性 30-34',
count( CASE WHEN gender = 2 AND 35 <= age AND age <= 39 THEN 1 ELSE null END ) as '女性 35-39',
count( CASE WHEN gender = 2 AND 40 <= age AND age <= 44 THEN 1 ELSE null END ) as '女性 40-44',
count( CASE WHEN gender = 2 AND 45 <= age AND age <= 49 THEN 1 ELSE null END ) as '女性 45-49'
FROM user

CASEの条件は複数追加することもできます。

これまでWHERE句で細々と結果を出して

あとからエクセルで集計みたいな
大変な作業をされていた方はCASEを使ったSELECT文を使うととても効率的です。

月別で件数を集計したい

先ほどのuserテーブルに生年月日: birthday(datetime型)があったとして。

SELECT
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 1 THEN 1 ELSE null END ) as '1月生まれ',
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 2 THEN 1 ELSE null END ) as '2月生まれ',
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 3 THEN 1 ELSE null END ) as '3月生まれ',
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 4 THEN 1 ELSE null END ) as '4月生まれ',
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 5 THEN 1 ELSE null END ) as '5月生まれ',
count( CASE WHEN DATE_FORMAT(birthday, '%m') = 6 THEN 1 ELSE null END ) as '6月生まれ'
FROM user