【WordPressカスタマイズ相談】現役プログラマが解決策を教えます

WordPress(ワードプレス)のお悩み、うまくいかなくてお困りなこと、不具合調査、新規制作依頼まで、ウェブアプリケーションエンジニアがあなたをサポートします。

(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


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


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

全国の統計ですが、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

※7月以降は割愛

生年月日ことや応募日ごとの月別の集計が必要な場合は月を条件にすることができます。
DATE_FORMAT(birthday, '%m')

birthday: 1978/10/1 → DATE_FORMAT(birthday, '%m'): 10
birthday: 1986/3/19 → DATE_FORMAT(birthday, '%m'): 3

THEN 1 ELSE null END


ちなみに集計している箇所を詳しく説明すると、
CASEで条件に合うものは"1"となり、その"1"の件数をCOUNTで求める感じです。
(ざっくりと説明しています)

クエリの応用

男女別、年齢別、日付別等、mysqlで件数を求めることができるCASEを使ってきました。
ANDで複数条件を指定することができるのでだいたいの統計情報は取得できるかと思います。


MySQLに関するご質問・ご相談はページ下部のリンクより、お気軽にお問い合わせください。

広告を非表示にする