Vertica でパターンマッチングしてみる。

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

• サンプルデータ