Vertica とは

高速な分析が可能な列指向DB。

公式サイト

最近良く使うので便利だな〜〜〜これいいわ〜〜〜と思ったことを書いていきます。

やりたいこと

たとえば、
ECサイトでページビュー、コンバージョン（購入完了とか）を何かしらのログに出力していたとして、
そのログデータから、

コンバージョンしたユーザーのよく通るページビューの経路を抽出する。（例１）
コンバージョンしていないけど、コンバージョンしたユーザーとよく通るページビューの経路を抽出する。（例２）
コンバージョンしていないけど、コンバージョンしたユーザーとよく通るページビューの経路を抽出する。（例３）

（例１）
○　コンバージョン←ページA←ページB
○　コンバージョン←ページA←ページB←ページC
×　コンバージョン←ページA←ページD←ページB
　↓
　ページA←ページB の経路がよく通るページビューの経路

（例２）
○　（コンバージョンしてない←）ページA←ページB
○　（コンバージョンしてない←）ページA←ページB←ページC
×　（コンバージョンしてない←）ページA←ページD←ページD
　↓
　ページA←ページB の経路がよく通るページビューの経路

（例３）
○　（コンバージョンしてない←）ページA←ページB
×　（コンバージョンしてない←）ページA←ページB←ページC
×　（コンバージョンしてない←）ページA←ページD←ページD
　↓
　ページA←ページB の経路がよく通るページビューの経路

やってみる

参考にした myVerticaのドキュメント

※ 最近は、「これ君のmyVerticaでどうにかならないかなぁ〜？」というのが口癖。

（例１）
・・・作業中

=> SELECT
	uid,
	sid,
	ts,
	refURL,
	pageURL,
	action,
	event_name(),
	pattern_id(),
	match_id()

FROM clickstream_log
MATCH
(PARTITION BY uid, sid ORDER BY ts
 DEFINE
	entry AS refURL NOT ILIKE '%website2.com%' AND pageURL
		ILIKE '%website2.com%',
	onsite as pageURL ILIKE '%website2.com%' and action='V',	
	purchase AS pageURL ILIKE '%website2.com%' AND
		action='P'
PATTERN P as (entry onsite* purchase)
ROWS MATCH FIRST EVENT);

（例２）
・・・作業中

（例３）
・・・作業中

課題

もっと簡単にできないか？
複雑なパターンをもっと増やしてやってみてもこのSQLでいけるか？

APPENDIX

サンプルデータ