16.20 - LAG式とLEAD式の例2 - 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

入力

aggregate_clicks
userid sessionid productname pagetype clicktime referrer productprice
1039 1 sneakers home 2009-07-29 20:17:59 Company1 100
1039 2 books home 2009-04-21 13:17:59 Company4 300
1039 3 television home 2009-05-23 13:17:59 Company2 500
1039 4 envelopes home 2009-07-16 11:17:59 Company3 10
1039 4 envelopes home1 2009-07-16 11:18:16 Company3 10
1039 4 envelopes page1 2009-07-16 11:18:18 Company3 10
1039 5 bookcases home 2009-08-19 22:17:59 Company5 150
1039 5 bookcases home1 2009-08-19 22:18:02 Company5 150
1039 5 bookcases page1 2009-08-19 22:18:05 Company5 150
1039 5 bookcases page2 2009-08-22 04:20:05 Company5 150
1039 5 bookcases checkout 2009-08-24 14:30:05 Company5 150
1039 5 bookcases page2 2009-08-27 23:03:05 Company5 150
1040 1 tables home 2009-07-29 20:17:59 Company5 250
1040 2 Appliances home 2009-04-21 13:17:59 Company6 1500
1040 3 laptops home 2009-05-23 13:17:59 Company7 800
1040 4 chairs home 2009-07-16 11:17:59 Company3 400
1040 4 chairs home1 2009-07-16 11:18:16 Company3 400
1040 4 chairs page1 2009-07-16 11:18:18 Company3 400
1040 5 cellphones home 2009-08-19 22:17:59 Company8 600
1040 5 cellphones home1 2009-08-19 22:18:02 Company8 600
1040 5 cellphones page1 2009-08-19 22:18:05 Company8 600
1040 5 cellphones page2 2009-08-22 04:20:05 Company8 600
1040 5 cellphones checkout 2009-08-24 14:30:05 Company8 600
1040 5 cellphones page2 2009-08-27 23:03:05 Company8 600
..。 ..。 ..。 ..。 ..。 ..。 ..。

SQL呼び出し

SELECT * FROM nPath (
  ON aggregate_clicks PARTITION BY sessionid ORDER BY clicktime ASC
  USING
  Mode (NONOVERLAPPING)
  Pattern ('H+.D*.X*.P1.P2+')
  Symbols (
    TRUE AS X,
    pagetype = 'home' AS H,
    pagetype <> 'home' AND pagetype <> 'checkout' AS D,
    pagetype = 'checkout' AS P1,
    pagetype = 'checkout' AND
    productprice > 100 AND
    productprice > LAG (productprice, 1, 100) AS P2
  )
  Result (
    FIRST (productname OF P1) AS first_product,
    MAX_CHOOSE (productprice, productname OF P2) AS max_product,
    FIRST (sessionid OF P2) AS sessionid
  )
) AS dt ORDER BY sessionid;

出力

first_product max_product sessionid
bookcases cellphones 5