Input
The input table is aggregate_clicks, from LAG and LEAD Expressions Example: No Alias for Input Query.
Modify the previous query call in nPath Range-Matching Example: Find Data for Sessions That Checked Out 3-6 Products to find sessions where the user checked out at least three products by changing the Pattern syntax element to:
Pattern ('H+.D*.C{3,}.D')
SQL-MapReduce Call
SELECT * FROM nPath ( ON aggregate_clicks PARTITION BY sessionid ORDER BY clicktime USING Mode (NONOVERLAPPING) Pattern ('H+.D*.C{3,}.D') Symbols ( pagetype = 'home' AS H, pagetype='checkout' AS C, pagetype<>'home' AND pagetype<>'checkout' AS D ) Result ( FIRST(sessionid OF C) AS sessionid, max_choose(productprice, productname OF C) AS most_expensive_product, MAX (productprice OF C) AS max_price, min_choose (productprice, productname OF C) AS least_expensive_product, MIN (productprice OF C) AS min_price ) ) AS dt ORDER BY dt.sessionid;
Output
sessionid | most_expensive_product | max_price | least_expensive_product | min_price |
---|---|---|---|---|
5 | cellphones | 600 | bookcases | 150 |