入力
clicksuserid |
sessionid |
productname |
pagetype |
clicktime |
referrer |
productprice |
1039 |
1 |
? |
home |
06:59:13 |
Company1 |
100 |
1039 |
1 |
? |
home |
07:00:10 |
Company2 |
300 |
1039 |
1 |
television |
checkout |
07:00:12 |
Company2 |
500 |
1039 |
1 |
television |
checkout |
07:00:18 |
Company2 |
10 |
1039 |
1 |
envelopes |
checkout |
07:01:00 |
Company3 |
10 |
1039 |
1 |
? |
checkout |
07:01:10 |
Company3 |
10 |
SQL呼び出し
SELECT * FROM nPath (
ON clicks PARTITION BY sessionid ORDER BY clicktime
USING
Mode (NONOVERLAPPING)
Symbols (
pagetype='home' AS H,
pagetype='checkout' AS C,
pagetype <> 'home' AND pagetype <>'checkout' AS A
)
Pattern ('^H+.A*.C+$')
Result (
FIRST (sessionid OF ANY (H, A, C)) AS sessionid,
FIRST (clicktime OF H) AS firsthome,
FIRST (clicktime OF C) AS firstcheckout,
ACCUMULATE (productname OF ANY (H,A,C) DELIMITER '*') AS products_accumulate,
ACCUMULATE (CDISTINCT productname OF ANY (H,A,C) DELIMITER '$$') AS cde_dup_products,
ACCUMULATE (DISTINCT productname OF ANY (H,A,C)) AS de_dup_products
)
) ORDER BY sessionid;
出力
sessionid |
firsthome |
firstcheckout |
products_accumulate |
cde_dup_products |
de_dup_products |
1 |
06:59:13 |
07:00:12 |
[null*null*television*television*envelopes*null] |
[null$$television$$envelopes$$null] |
[null, television, envelopes] |