Input
clicks
userid |
sessionid |
productname |
pagetype |
clicktime |
referrer |
productprice |
1039 |
1 |
null |
home |
06:59:13 |
Company1 |
100 |
1039 |
1 |
null |
home |
07:00:10 |
Company3 |
300 |
1039 |
1 |
television |
checkout |
07:00:12 |
Company3 |
500 |
1039 |
1 |
television |
checkout |
07:00:18 |
Company3 |
10 |
1039 |
1 |
envelopes |
checkout |
07:01:00 |
Company4 |
10 |
1039 |
1 |
null |
checkout |
07:01:10 |
Company4 |
10 |
SQL Call
SELECT * FROM nPath@coprocessor (
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
)
) AS dt;
Output
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] |
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.