【実践的】データベースから月間会員登録者数をSQLのSELECT文で作って抽出してみた。

はじめに

どうも、未経験からエンジニア転職を目指しているもきおです。

おそらく自社開発企業などで自社サービスのデータ分析をするとき、「月別の月間登録者数割り出しといて」みたいな感じで頼まれる事ってありそうな気がするんですよね。

えっ、そんなことねえよって？
ある程にしてください、お願いします笑

今回はSELECT文を使用しどうやって月間登録者数をグループ化し抽出するのかをまとめていきたいと思います。

最終目標物

最終的に作りたいSQLクエリ結果はこちら

こんな感じで月別に会員登録者数を集計したものを算出してます。

まず最初にやる事

SQL文を作成するにあたってまず最初に行うことはテーブル構造の把握です。
テーブル構造を把握した上で目標結果に対しどういったSQL文を作成したら良いのかを考えていきます。

SELECT*で全体を表示

SELECT * FROM テーブル名 LIMIT 5;

構造だけ把握すればいいので LIMITで5件のみを抽出しております。

結果はこちら

月間の会員登録者数を抽出したいのでcreated_atカラムに着目すれば良さそうです。
今回は月別なので曜日と時間は必要ありません。

よってこのcreated_atのフォーマットを変更→それを月別に集計→月別にまとめて表示という流れでSQL文を作成してみました。

最終的に作成したSQL文はこちら

SQL月間会員登録者数集計

SELECT 
  DATE_FORMAT(created_at, '%Y-%m') AS by_month, 
  COUNT(*) AS count
FROM
  テーブル名
GROUP BY
  DATE_FORMAT(created_at, '%Y-%m')
ORDER BY
  by_month
;

①まずDATE_FORMATというものを使用しcreated_atのフォーマットを2020-07のような感じに変更してます。
②変更したフォーマットをby_monthというカラム名にASで指定
③それを集計
④GROUP BYを使用し月別にまとめる
⑤ORDER BYで月を昇順で表示

こんな流れでしょうか。

最終成果物

月別の会員登録者数を抽出できました！
ちなみに登録者数多い順とかにするなら

月間登録者多い順

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS by_month,
  COUNT(*) AS count
FROM
  テーブル名
GROUP BY 
  DATE_FORMAT(created_at, '%Y-%m')
ORDER BY 
  count DESC
;

このような感じでORDER BYにおいてcountをDESC(降順に並べ替える句)で指定すると以下のような感じになります。

あとがき

いかがでしたでしょうか？
今回は企業でのデータ分析においてやりそうなことを勝手に想像して作成してみました笑

転職活動に励みたいと思います。

最後までご覧いただきありがとうございました！
この記事が少しでも良いと感じていただけましたらLGTMポチッとしていただけますと幸いです。

他にもSQL関連の記事書いているのでよければご覧ください！