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

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
9.02
9.01
2.0
1.3
Published
February 2022
Language
English (United States)
Last Update
2022-02-10
dita:mapPath
rnn1580259159235.ditamap
dita:ditavalPath
ybt1582220416951.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

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@coprocessor (
  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)
) AS dt;

Output

userid gender max_prod min_prod
1 M television envelopes
2 F appliances bookcases
3 F cellphones dvds

Download a zip file of all examples and a SQL script file that creates their input tables.