SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

ビジネスデータ解析のためのSQL入門

ユーザーの年齢・性別と購買履歴を活用する「属性」×「行動」分析のSQL

ビジネスデータ解析のためのSQL入門 第5回

  • X ポスト
  • このエントリーをはてなブックマークに追加

SQLサンプルと解説

 ここからは、ユーザー属性・行動分析のSQLの例を示しつつ、解説を進めます。

 まず、ユーザー属性として年齢性別区分、ユーザー行動として購買した商品カテゴリを集計し、分析する方法を説明します。欲しい結果は以下の通りです。

 product_category | user_category | purchase_count 
------------------+---------------+----------------
 beverage         | M3            |              2
 beverage         | W1            |              2
 beverage         | W3            |              1
 food             | M1            |              2
 food             | M2            |              2
 food             | M3            |              8
 food             | W1            |              2
 food             | W3            |              5
 zakka            | M2            |              4
年齢性別区分と購買商品カテゴリの傾向
年齢性別区分と購買商品カテゴリの傾向

 上図はSQLクエリ結果から作成したグラフです。このグラフから以下の分析ができます。

  • 「食品(food)」はM3層およびW1・W2層が多く購入している。
  • 「雑貨(zakka)」を購入しているのはM2層のみ。
  • W2層は購入がない(少ない)。

 こうした分析結果をもとに、広告の手法やサービスの改善を施策として実施していくことになります。

 なお、グラフは可視化ツールであるRe:dashで作成しています。Re:dashについては連載の前回の記事で解説しています。

 上の結果を得るためのSQLは以下の通りです。

リスト5 ユーザーの年齢カテゴリ属性の集計(count_age_sex_category.sql)
with/* (1) */
sq_users_int_birthday as (/* (2)-(a) */
    select
    *,
    20171001 as benchmark_date/* (2)-(b) */
    ,cast(replace(birthday,'-','' ) as integer) as int_birth_date
    from customer_with_birthday
),
sq_users_age as (/* (3) */
    select
        *,
        floor((benchmark_date - int_birth_date )/ 10000 ) as age/* (3)-(a) */
    from sq_users_int_birthday
),
sq_users_category as (/* (4) */
    select
        user_id
        ,sex
        ,age
        ,concat(/* (4)-(a) */
            case
                when 20 <= age then sex else ''/* (4)-(b) */
            end
            ,case
                when age >= 4 and age <= 12 then 'C'/* (4)-(c) */
                when age >= 13 and age <= 19 then 'T'
                when age >= 20 and age <= 34 then '1'
                when age >= 35 and age <= 49 then '2'
                when age >= 50 then '3'
            end
        )as age_sex_category/* (4)-(d) */
    from
        sq_users_age
)
select
    a.item_category
    ,u.age_sex_category
    ,count(*) as purchase_count/* (5) */
from action_history as a/* (6) */
    join sq_users_category as u
    ON a.user_id = u.user_id
where a.action_category = 'purchase'
group by a.item_category, u.age_sex_category/* (7) */
order by a.item_category, u.age_sex_category
;

 内側のサブクエリの内容から順に確認していきます。

(1)With句

 With句はサブクエリに名前をつけ、SQLの中ではなく前で記述します。サブクエリを複数利用するケースでは、SQLの中で記述する場合にくらべ視認性を高めることができます。

 以下の通り記述します。

WITH
  <クエリ名> AS ( select <列>  ~ )

(2)ユーザーの誕生日を10桁の整数型に加工する

 年齢を計算するための前準備として、日付型の誕生日を整数型に加工します。

 また、年齢算出したい日付――基準日(benchmark_date)として、ここでは2017年10月1日と設定しています。

 このサブクエリによって得られる途中結果は以下の通りです。

 user_id  | sex |  birthday  | benchmark_date | int_birth_date 
----------+-----+------------+----------------+----------------
 UID0001  | M   | 1937-10-17 |       20171001 |       19371017
 UID0002  | M   | 1982-03-27 |       20171001 |       19820327
 UID0003  | M   | 1982-03-27 |       20171001 |       19820327
 UID0004  | W   | 1994-06-07 |       20171001 |       19940607
 UID0005  | W   | 1994-06-07 |       20171001 |       19940607
 UID0006  | W   | 1937-10-17 |       20171001 |       19371017
 UID0007  | M   | 1937-10-17 |       20171001 |       19371017
 UID0008  | W   | 1994-06-07 |       20171001 |       19940607
 UID0009  | M   | 1994-06-07 |       20171001 |       19940607
 UID0010  | W   | 1982-03-27 |       20171001 |       19820327
 UID0011  | W   | 1994-06-07 |       20171001 |       19940607
 UID0012  | W   | 1982-03-27 |       20171001 |       19820327

(3)年齢を計算する

 (a)整数型に加工した誕生日から年齢を算出します。年齢をSQLで算出する方法はいくつかありますが、誕生日と特定の日付を整数型で表現し、その差の値を10000で割る方法が最もスマートです。(2)で準備した10桁整数型の日付の差を算出し、10000で割ると年齢が算出されます。

 例えば1990年1月1日生まれの場合、2017年10月1日時点での年齢は以下のように「27歳」と算出されます。

20171001-19900101=270900
270900/10000=27 (小数点以下切り捨て)

 このサブクエリによって得られる途中結果は以下の通りです。

 user_id  | sex |  birthday  | age
----------+-----+------------+-----
 UID0001  | M   | 1937-10-17 |  79
 UID0002  | M   | 1982-03-27 |  35
 UID0003  | M   | 1982-03-27 |  35
 UID0004  | W   | 1994-06-07 |  23
 UID0005  | W   | 1994-06-07 |  23
 UID0006  | W   | 1937-10-17 |  79
 UID0007  | M   | 1937-10-17 |  79
 UID0008  | W   | 1994-06-07 |  23
 UID0009  | M   | 1994-06-07 |  23
 UID0010  | W   | 1982-03-27 |  35
 UID0011  | W   | 1994-06-07 |  23
 UID0012  | W   | 1982-03-27 |  35

(4)性別と年齢からカテゴリを作成

 (a)concat関数で文字列を連結します。concat関数はすべての引数を結合します(ただし、NULLは無視されます)。

concat('abc', 2, NULL, 22) → abc222

 (b)CASE式を使って20歳以上の場合は性別の文字を使用し、9歳以下は使用しないためNULLにします。

 CASE式の構文は以下の通りです。

[構文]CASE式(比較演算子は"="のほか不等号などが使用可能)
CASE WHEN <項目> = <値1>  THEN  <値4>
      WHEN <項目> = <値2>  THEN  <値5>
 ELSE  <値3>  END

 (c)「age >= 4 and age <= 12」で年齢ageが4から12までの指定になります。

 (d)concat関数によって連結されたテキストにage_sex_categoryという別名をつけます。

 このサブクエリによって得られる途中結果は以下の通りです。

 user_id  | sex | age | age_sex_category 
----------+-----+-----+------------------
 UID0001  | M   |  79 | M3
 UID0002  | M   |  35 | M2
 UID0003  | M   |  35 | M2
 UID0004  | W   |  23 | W1
 UID0005  | W   |  23 | W1
 UID0006  | W   |  79 | W3
 UID0007  | M   |  79 | M3
 UID0008  | W   |  23 | W1
 UID0009  | M   |  23 | M1
 UID0010  | W   |  35 | W2
 UID0011  | W   |  23 | W1
 UID0012  | W   |  35 | W2

(5)カテゴリごとのユーザー数集計

 group by句でcategoryを指定し、count(*)で行数をカウントすることで、categoryごとのUserID数が集計されます。

(6)購買履歴テーブルと年齢性別区分テーブルを結合

 join句で購買履歴テーブルである「action_history」と年齢性別区分テーブルの「mst_users_with_category」の内部結合を行います。ONで示すように両テーブルのuser_idをもとに結合します。テーブルにはそれぞれ簡略化のため「a」「u」の別名をつけます。

(7)集計のためのグループ指定

 購買履歴テーブルの商品カテゴリとユーザー管理テーブルの年齢性別区分ごとに、集計を行うよう指定します。

 以上で、年齢性別区分別・商品カテゴリ別の分析のための集計ができました。

まとめ

 今回はユーザー分析における属性と行動のデータを活用した分析を取り上げました。

 サンプルとしてECサイトのデータを例としましたが、ユーザー管理データと購買履歴さえあればSQLで分析を実施できることがおわかりいただけたと思います。これらのデータはほとんどの場合データベースにあるので、ぜひSQLを使って分析をしてみてください。

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
ビジネスデータ解析のためのSQL入門連載記事一覧

もっと読む

この記事の著者

WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)

WINGSプロジェクトについて>有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛...

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

山田 祥寛(ヤマダ ヨシヒロ)

静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for Visual Studio and Development Technologies。執筆コミュニティ「WINGSプロジェクト」代表。主な著書に「独習シリーズ(Java・C#・Python・PHP・Ruby・JSP&サーブレットなど)」「速習シリーズ(ASP.NET Core・Vue.js・React・TypeScript・ECMAScript、Laravelなど)」「改訂3版JavaScript本格入門」「これからはじめるReact実践入門」「はじめてのAndroidアプリ開発 Kotlin編 」他、著書多数

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/10611 2018/01/18 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング