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

Database Analytic Functions

Product
Teradata® Database
Release Number
16.10
15.10
Published
November 2017
Language
English (United States)
Last Update
2018-05-10
dita:mapPath
hoj1499019223447.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-1206
lifecycle
previous
Product Category
Teradata® Database

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