ユーザーによるアプリケーション機能利用分析ですぐに使えるSQL

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

　本シリーズではビジネスデータ解析でデータベースから情報を取得して活用する必要がある方を対象に、ビジネスデータ解析でよく使われる実践的なSQL例など、分析向けの活用方法を解説します。前回はSQLによるユーザー属性・行動分析のポイントを解説しました。今回はSQLによるアプリケーションでのユーザーアクション分析に挑戦します。ユーザー行動ログとひとつのSQLクエリ文でユーザーアクション分析ができることを確認します。

対象読者

• SQLの基本は理解しているがより実践的な分析に取り組みたい方

サンプルの動作確認環境

• PostgreSQL9.6
• CentOS 6.7

データの準備

　本稿で用いるサンプルデータとして、ユーザー行動ログのテーブルを準備しました。以下のSQLを実行してデータを作成してください。

リスト1　ユーザー行動ログテーブルの作成（create_action_history.sql）

create table sales_history(
item_name char(12), -- （商品名）
item_category char(12 ),    -- （商品カテゴリ）
action_category char(16 ), -- （行動カテゴリ）
date date,  -- （購買日）
user_id char(8)    -- （顧客コード）
);

リスト2　ユーザー行動ログ データの挿入（insert_action_history.sql）

insert into action_history values
('コーヒー豆','food','tagging','2017/5/29','UID0001'),
('紅茶','food','tagging','2017/5/29','UID0001'),
('紅茶','food','review','2017/5/30','UID0001'),
('コーヒー豆','food','purchase','2017/5/30','UID0001'),
('ハチミツ','food','purchase','2017/5/30','UID0001'),
('コーヒー豆','food','tagging','2017/5/30','UID0001'),
('シナモン','food','tagging','2017/6/1','UID0001'),
('シナモン','food','tagging','2017/6/2','UID0001'),
('マグカップ','zakka','review','2017/6/3','UID0002'),
('ハチミツ','food','tagging','2017/6/4','UID0001'),
('紅茶','food','tagging','2017/6/4','UID0003'),
('ハチミツ','food','purchase','2017/6/5','UID0001'),
('ハチミツ','food','tagging','2017/6/6','UID0001'),
('コーヒー豆','food','tagging','2017/6/7','UID0001'),
('コーヒー豆','food','tagging','2017/6/8','UID0001'),
('ハチミツ','food','tagging','2017/6/9','UID0001'),
('紅茶','food','tagging','2017/6/10','UID0001'),
('ハチミツ','food','review','2017/6/10','UID0001'),
('コーヒー豆','food','tagging','2017/6/10','UID0004'),
('ハチミツ','food','purchase','2017/6/12','UID0001'),
('ハチミツ','food','tagging','2017/6/12','UID0001'),
('コーヒー豆','food','tagging','2017/6/13','UID0001'),
('紅茶','food','tagging','2017/6/13','UID0001'),
('マグカップ','zakka','purchase','2017/6/14','UID0003'),
('マグカップ','zakka','review','2017/6/14','UID0003')
;