nPath Resultの例: FIRST、LAST_NOTNULL、MAX_CHOOSE、MIN_CHOOSE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine分析関数

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
2021年7月
Language
日本語
Last Update
2021-09-23
dita:mapPath
ja-JP/wnd1589838592459.ditamap
dita:ditavalPath
ja-JP/ayr1485454803741.ditaval
dita:id
B035-1206
Product Category
Software
Teradata Vantage

入力

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呼び出し

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;

出力

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