16.10 - Example 1: Use FIRST, LAST_NOTNULL, MAX_CHOOSE, and MIN_CHOOSE - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K

Input

nPath Aggregate Functions Example 1 Input Table 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-MapReduce 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)
) as dt ORDER BY 1;

Output

nPath Aggregate Functions Example 1 Output Table
userid gender max_prod min_prod
1 M television envelopes
2 F appliances bookcases
3 F cellphones dvds