Input
trans1
| userid |
gender |
ts |
productname |
productamt |
| 1 |
M |
2012-01-01 00:00:00 |
shoes |
100 |
| 1 |
M |
2012-02-01 00:00:00 |
books |
300 |
| 1 |
M |
2012-03-01 00:00:00 |
television |
500 |
| 1 |
M |
2012-04-01 00:00:00 |
envelopes |
10 |
| 2 |
|
2012-01-01 00:00:00 |
bookcases |
150 |
| 2 |
|
2012-02-01 00:00:00 |
tables |
250 |
| 2 |
F |
2012-03-01 00:00:00 |
appliances |
1500 |
| 3 |
F |
2012-01-01 00:00:00 |
chairs |
400 |
| 3 |
F |
2012-02-01 00:00:00 |
cellphones |
600 |
| 3 |
F |
2012-03-01 00:00:00 |
dvds |
50 |
SQL Call
SELECT * FROM nPath (
ON trans1 PARTITION BY userid ORDER BY ts
USING
Mode (NONOVERLAPPING)
Pattern ('A+')
Symbols (TRUE AS A)
Result (
FIRST (userid OF A) AS Userid,
LAST_NOTNULL (gender OF A) AS Gender,
MAX_CHOOSE (productamt, productname OF A) AS Max_prod,
MIN_CHOOSE (productamt, productname OF A) AS Min_prod
)
) ORDER BY 1;
Output
| userid |
gender |
max_prod |
min_prod |
| 1 |
M |
television |
envelopes |
| 2 |
F |
appliances |
bookcases |
| 3 |
F |
cellphones |
dvds |