はじめに
Vertica拡張SQLのPattern Matchingを使用すると、WEBサイトのクリックストリームデータなどから、一連の動きにマッチしたデータを抽出することができます。例えば「広告をクリック→商品を見る→レジに行く→購入」というパターンにマッチしたデータのみ抽出してコンバージョン分析に活用することができます。
コマンド構文
Pattern MatchingはMATCH句を使用します。|
1 2 3 4 5 6 |
SELECT 列名 FROM テーブル名 MATCH ( [ PARTITION BY 列名, ] ORDER BY 列名 ... DEFINE event_name AS boolean_expr [, ...] ... PATTERN pattern_name AS ( regexp ) ... [ ROWS MATCH { ALL EVENTS | FIRST EVENT } ] ); |
各パラメータの説明
| パラメータ名 | 内容 |
|---|---|
| PARTITION BY | ※オプション PATTERN節で定義されたパターンが一致するウィンドウデータの範囲を定義します。 PATTERN節で定義された一致パターンによってデータを分割します。PARTITION句を省略すると、データセット全体が単一のパーティションとみなされます。 |
| ORDER BY | パーティション内でのソート順を指定します。 |
| DEFINE | イベントタイプを構成するブール式を正規表現で定義します。 |
| event_name | 各行を評価するためのイベント名を指定します。 |
| boolean_expr | 評価のための式(true or falseを返す式)を指定します。 |
| PATTERN pattern_name | パターン名を指定します。 |
| regexp | パターンマッチングのルールを正規表現で指定します。 例えばEntry→0以上のOnsite→Purchaseにマッチするログのみを抽出する場合は以下のように指定します。 (Entry Onsite* Purchase) |
| ROWS MATCH | ※オプション 同じ行に対して複数のイベントが同時にtrue(マッチする)と評価された場合の動作を指定します。 ROWS MATCH ALL EVENTS 各行に対してマッチするイベントが1つのみの場合に指定します。複数のイベントがマッチする場合はエラーが返ります。 ROWS MATCH FIRST EVENT 各行に対してマッチするイベントが複数ある場合、SQL内で最初に定義されたイベントが選択されます。 |
利用例
Webサイトのクリックストリームデータに対してPattern Matching関数を使用してコンバージョン分析を行います。Webサイトのクリックストリームデータが格納されるテーブル(clickstream_log)の定義
|
1 2 3 4 5 6 7 8 |
CREATE TABLE clickstream_log ( uid INT, --ユーザID sid INT, --セッションID ts TIME, --ページ滞在中に発生したタイムスタンプ refURL VARCHAR(20), --クリック元ページのURL pageURL VARCHAR(20), --クリック先(滞在先)ページのURL action CHAR(1) --ユーザがページにアクセス後に行ったアクション('P' = 購入, 'V' = 閲覧のみ) ); |
clickstream_logテーブル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
dbadmin=> SELECT * FROM clickstream_log ORDER BY ts ; uid | sid | ts | refURL | pageURL | action -----+-----+----------+----------------------+----------------------+-------- 1 | 100 | 12:00:00 | website1.com | website3.com/home | V 1 | 100 | 12:01:00 | website3.com/home | website3.com/floby | V 1 | 100 | 12:02:00 | website3.com/floby | website3.com/shamwow | V 1 | 100 | 12:03:00 | website3.com/shamwow | website3.com/buy | P 3 | 100 | 12:08:00 | website2.com | website3.com/home | V 2 | 100 | 12:10:00 | website1.com | website3.com/home | V 3 | 100 | 12:10:00 | website3.com/home | website3.com/forks | V 2 | 100 | 12:11:00 | website3.com/home | website3.com/forks | V 3 | 100 | 12:12:00 | website3.com/forks | website3.com/buy | P 2 | 100 | 12:13:00 | website3.com/forks | website3.com/buy | P (10 rows) |
本例では、website1.comを広告ページのURL、website2.comを別ページのURL、website3.comを自社ECサイトのURLと仮定します。
実行するSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
dbadmin=> SELECT uid, dbadmin-> sid, dbadmin-> ts, dbadmin-> refurl, dbadmin-> pageurl, dbadmin-> action, dbadmin-> event_name(), dbadmin-> pattern_id(), dbadmin-> match_id() dbadmin-> FROM clickstream_log dbadmin-> MATCH dbadmin-> (PARTITION BY uid, sid ORDER BY ts,uid dbadmin(> DEFINE dbadmin(> Entry AS RefURL ILIKE '%website1.com%' AND PageURL ILIKE '%website3.com%', dbadmin(> Onsite AS PageURL ILIKE '%website3.com%' AND Action='V', dbadmin(> Purchase AS PageURL ILIKE '%website3.com%' AND Action = 'P' dbadmin(> PATTERN dbadmin(> a AS (Entry Onsite* Purchase ) dbadmin(> ROWS MATCH FIRST EVENT); |
DEFINE節では各行のデータを条件に応じて「Entry」「Onsite」「Purchase」と定義しています。
・広告ページ(website1.com)からの訪問で、かつ自社ECサイトのwebsite3.com/homeにアクセスしている→「Entry」と定義
・自社ECサイトのwebsite3.com内の移動→「Onsite」と定義
・自社ECサイトのwebsite3.com内で商品を購入→「Purchase」と定義
PATTERN節では「Entryから始まり、1回以上のOnsiteを経由し、Purchaseに至る」という条件にマッチした行を抽出するよう定義しています。
実行結果
|
1 2 3 4 5 6 7 8 9 10 |
uid | sid | ts | refurl | pageurl | action | event_name | pattern_id | match_id -----+-----+----------+----------------------+----------------------+--------+------------+------------+---------- 1 | 100 | 12:00:00 | website1.com | website3.com/home | V | Entry | 1 | 1 1 | 100 | 12:01:00 | website3.com/home | website3.com/floby | V | Onsite | 1 | 2 1 | 100 | 12:02:00 | website3.com/floby | website3.com/shamwow | V | Onsite | 1 | 3 1 | 100 | 12:03:00 | website3.com/shamwow | website3.com/buy | P | Purchase | 1 | 4 2 | 100 | 12:10:00 | website1.com | website3.com/home | V | Entry | 1 | 1 2 | 100 | 12:11:00 | website3.com/home | website3.com/forks | V | Onsite | 1 | 2 2 | 100 | 12:13:00 | website3.com/forks | website3.com/buy | P | Purchase | 1 | 3 (7 rows) |
上記を実行すると、uid,sid単位で条件に合致したデータのみが抽出されます。
本例ではuid1、2のデータが条件に合致しました。uid3は商品を購入していますが、広告ページ(website1.com)からの訪問ではないため抽出されません。
Pattern Matchingで抽出した結果を使用することで、購入に至るまでにどのようなサイトを見たかといったコンバージョン分析を行うことが容易になります。
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。更新履歴
2019/04/11 検証バージョンを9.2に変更2017/3/6 本記事を公開
- 投稿タグ
- Pattern Matching
