会員に人気のメールアドレスドメインは何だろう?

と思って、SQLを作っていたときに使った手法を紹介します。

下のようなテーブルから、『ドメイン別ランキングを作る』ことが今回のミッションです。

sample_db

結論

次のSQL文で無事にミッションコンプリート。

以下、分割して解説します。

SUBSTRING_INDEX()でメールアドレスからドメイン名を抜き出す

今回使ったSUBSTRING_INDEX()関数の使い方は、次の通りです。

SUBSTRING_INDEX(第1引数, 第2引数, 第3引数)

  • 第1引数……カラム名を指定
  • 第2引数……目印となる文字を指定
  • 第3引数……左からn個目の目印までの文字列を取得

ドメイン名が欲しいので、@を目印に文末まで取得すればOK。

第3引数を負にすると、右から数えてn個目の目印までの文字列を取得するので、n=-1とします。

これでドメイン名のみ抽出する関数が完成!

SUBSTRING_INDEX(mail_address, ‘@’, -1)

GRUOP BYと組み合わせる

上の関数の結果をSELECT文で取得すると、ドメイン名だけが並んだ結果が返ってきます。

SELECT SUBSTRING_INDEX(mail_address, ‘@’, -1) FROM member;

domain2

これをGROUP BYに渡すことで、同じドメインがグループ化します。

結果

結論のSQL文を実行すると、次のようにドメイン別ランキングを取得することができました!

result

SQLは素直でいい子ですね。得意なことは喜々として取り組み、ニガテなことは沈黙を貫いて。
でも、ちょっとした文字列操作くらいは頑張ってくれます。文字列関数は少しずつ身につけていきたいですね。

▼お知らせ
FirstLogicでは現在、開発メンバーを募集しています。
詳細は こちら をご覧ください。