16.20 - nPath Resultの例1: FIRST、LAST_NOTNULL、MAX_CHOOSE、MIN_CHOOSE - Teradata Vantage NewSQL Engine

Teradata Vantage™ - NewSQL Engineの分析関数

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1206-162K-JPN

入力

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