nPath Function Results Example | Teradata Vantage - nPath Results Example: FIRST, LAST_NOTNULL, MAX_CHOOSE, and MIN_CHOOSE - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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