Input
The input table is clicks, as in nPath Results Example: FIRST, ACCUMULATE.
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)) AS products_accumulate, COUNT (DISTINCT productname OF ANY(H,A,C)) AS count_distinct_products, ACCUMULATE (CDISTINCT productname OF ANY (H,A,C)) AS consecutive_distinct_products, ACCUMULATE (DISTINCT productname OF ANY (H,A,C)) AS distinct_products, NTH (productname, -1 OF ANY(H,A,C)) AS nth ) ) AS dt;
Output
sessionid | firsthome | firstcheckout | products_accumulate | count_distinct_products | consecutive_distinct_products | distinct_products | nth |
---|---|---|---|---|---|---|---|
1 | 06:59:13 | 07:00:12 | [null, null, television, television, envelopes, null] | 3 | [null, television, envelopes, null] | [null, television, envelopes] | null |
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.