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

入力

bank_web_clicks
customer_id session_id page datestamp
529 0 ACCOUNT SUMMARY 2004-03-17 16:35:00
529 0 FAQ 2004-03-17 16:38:00
529 0 ACCOUNT HISTORY 2004-03-17 16:42:00
529 0 FUNDS TRANSFER 2004-03-17 16:45:00
529 0 ONLINE STATEMENT ENROLLMENT 2004-03-17 16:49:00
529 0 PROFILE UPDATE 2004-03-17 16:50:00
529 0 ACCOUNT SUMMARY 2004-03-17 16:51:00
529 0 CUSTOMER SUPPORT 2004-03-17 16:53:00
529 0 VIEW DEPOSIT DETAILS 2004-03-17 16:57:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:16:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:18:00
529 1 FAQ 2004-03-18 01:20:00
..。 ..。 ..。 ..。

SQL呼び出し

SELECT * FROM nPath (
  ON (SELECT customer_id, session_id, datestamp, page FROM bank_web_clicks) AS dt1
    PARTITION BY customer_id, session_id
    ORDER BY datestamp
  USING
  Mode (NONOVERLAPPING)
  Pattern ('(DUP|A)*')
  Symbols (
    TRUE AS A,
    page = LAG (page,1) AS DUP
  )
  Result (
    FIRST (customer_id OF any (A)) AS customer_id,
    FIRST (session_id OF A) AS session_id,
    FIRST (datestamp OF A) AS first_date,
    LAST (datestamp OF ANY(A,DUP)) AS last_date,
    ACCUMULATE (page OF A) AS page_path,
    ACCUMULATE (page of DUP) AS dup_path
  )
) AS dt2;

出力

列1~4
customer_id session_id first_date last_date
529 0 2004-03-17 16:35:00 2004-03-17 16:57:00
529 1 2004-03-18 01:16:00 2004-03-18 01:28:00
529 2 2004-03-18 09:22:00 2004-03-18 09:36:00
529 3 2004-03-18 22:41:00 2004-03-18 22:55:00
529 4 2004-03-19 08:33:00 2004-03-19 08:41:00
529 5 2004-03-19 10:06:00 2004-03-19 10:14:00
..。 ..。 ..。 ..。
列5~6
page_path dup_path
[ACCOUNT SUMMARY, FAQ, ACCOUNT HISTORY, FUNDS TRANSFER, ONLINE STATEMENT ENROLLMENT, PROFILE UPDATE, ACCOUNT SUMMARY, CUSTOMER SUPPORT, VIEW DEPOSIT DETAILS] []
[ACCOUNT SUMMARY, FAQ, ACCOUNT SUMMARY, FUNDS TRANSFER, ACCOUNT HISTORY, VIEW DEPOSIT DETAILS, ACCOUNT SUMMARY, ACCOUNT HISTORY] [ACCOUNT SUMMARY]
[ACCOUNT SUMMARY, ACCOUNT HISTORY, FUNDS TRANSFER, ACCOUNT SUMMARY, FAQ] [ACCOUNT SUMMARY, ACCOUNT SUMMARY, FAQ]
[ACCOUNT SUMMARY, ACCOUNT HISTORY, ACCOUNT SUMMARY, ACCOUNT HISTORY, FAQ, ACCOUNT SUMMARY] [ACCOUNT SUMMARY]
[ACCOUNT SUMMARY, FAQ, VIEW DEPOSIT DETAILS, FAQ] []
[ACCOUNT SUMMARY, FUNDS TRANSFER, VIEW DEPOSIT DETAILS, ACCOUNT HISTORY] [VIEW DEPOSIT DETAILS]
..。 ..。