対象読者
- SQLの基本は理解しているがより実践的な分析に取り組みたい方
サンプルの動作確認環境
- PostgreSQL 9.6
- CentOS 6.7
データの準備
本稿で用いるサンプルデータとして、ユーザー管理・購買履歴のテーブルを準備しました。サンプルデータ作成のためのSQLを、以下に示します。
リスト1 ユーザー管理テーブルの作成（create_table_customer_birth.sql）
create table customer_with_birthday( user_id char(008), （顧客コード） sex char(008), （性別） birthday char(010) （生年月日） );
ユーザー管理データの挿入（insert_sales_history.sql）
insert into customer_with_birthday(user_id,sex,birthday) values
('UID0001','M','1937-10-17'),
('UID0002','M','1982-03-27'),
('UID0003','M','1982-03-27'),
('UID0004','W','1994-06-07'),
('UID0005','W','1994-06-07'),
('UID0006','W','1937-10-17'),
('UID0007','M','1937-10-17'),
('UID0008','W','1994-06-07'),
('UID0009','M','1994-06-07'),
('UID0010','W','1982-03-27'),
('UID0011','W','1994-06-07'),
('UID0012','W','1982-03-27')
;
リスト3 購買履歴テーブルの作成（create_action_history.sql）
create table sales_history( item_name char(12), （商品名） item_category char(12 ), （商品カテゴリ） action_category char(16 ), （行動カテゴリ） date date, （購買日） place char(8), （購買地域・場所） user_id char(8), （顧客コード） price int （料金） );
リスト4 購買履歴 データの挿入（insert_action_history.sql）
insert into action_history values
('コーヒー豆','beverage','purchase','2017/5/29','EC Store','UID0001','250'),
('紅茶','food','purchase','2017/5/29','EC Store','UID0001','250'),
('紅茶','food','favorite','2017/5/30','EC Store','UID0004','200'),
('コーヒー豆','beverage','review','2017/5/30','EC Store','UID0004','350'),
('ハチミツ','food','review','2017/5/30','EC Store','UID00011','200'),
('コーヒー豆','beverage','purchase','2017/5/30','EC Store','UID00011','350'),
('シナモン','food','purchase','2017/6/1','EC Store','UID00011','450'),
('シナモン','food','purchase','2017/6/2','EC Store','UID00011','450'),
('マグカップ','zakka','favorite','2017/6/3','EC Store','UID0002','980'),
('ハチミツ','food','purchase','2017/6/4','EC Store','UID0006','250'),
('紅茶','food','purchase','2017/6/4','EC Store','UID0003','250'),
('ハチミツ','food','review','2017/6/5','EC Store','UID0006','250'),
('ハチミツ','food','purchase','2017/6/6','EC Store','UID0006','250'),
('コーヒー豆','beverage','purchase','2017/6/7','EC Store','UID0006','250'),
('コーヒー豆','beverage','purchase','2017/6/8','EC Store','UID0007','250'),
('ハチミツ','food','purchase','2017/6/9','EC Store','UID0007','250'),
('紅茶','food','purchase','2017/6/10','EC Store','UID0007','250'),
('ハチミツ','food','favorite','2017/6/10','EC Store','UID0008','250'),
('コーヒー豆','beverage','purchase','2017/6/10','EC Store','UID0004','250'),
('ハチミツ','food','review','2017/6/12','EC Store','UID0008','250'),
('ハチミツ','food','purchase','2017/6/12','EC Store','UID0009','250'),
('コーヒー豆','beverage','purchase','2017/6/13','EC Store','UID0005','250'),
('紅茶','food','purchase','2017/6/13','EC Store','UID00012','250'),
('マグカップ','zakka','purchase','2017/6/14','EC Store','UID0003','980'),
('マグカップ','zakka','purchase','2017/6/14','EC Store','UID0003','780')
;
ユーザーの「年齢＋性別」属性と「購買」行動分析
何らかのユーザー情報を保持するサービスでは、どのようなユーザーに利用されているのかを把握し、ユーザーの利用実態がサービス提供側の意図通りであるか確認することが必要です。ユーザーの属性を定義して集計することで、広告やサービス利用状況といった、まざまなレポートが作成可能になります。
本稿では視聴率やアンケートの分析に用いられるM1層、F1層（以降、年齢性別区分）と呼ばれる属性を、性別と年齢から導き出して設定し、その人数を集計する方法を紹介します。年齢性別区分は、以下の表のようにそれぞれの性別および年齢と対応します。
| 区分 | 年齢＋性別 |
|---|---|
| M1層 | 男性20～34歳 |
| M2層 | 男性35～49歳 |
| M3層 | 男性50歳以上 |
| F1層 | 女性20～34歳 |
| F2層 | 女性35～49歳 |
| F3層 | 女性50歳以上 |
| C層 | 4～12歳の男女 |
| T層 | 13～19歳の男女 |
年齢と性別はユーザー管理テーブルから取得します。性別に関してはテーブル上のデータを取得するだけですが、年齢の場合は少々やっかいです。
DBのテーブルに年齢そのものを保存した場合、登録日から日数が経過するとデータベースに保存している年齢と実際の年齢が合致しなくなります。そのため、通常は誕生日のみ保存し、年齢は表示時や集計時に計算して出力します。SQLによる詳しい方法は後ほど紹介します。
