LAG and LEAD Expressions Example 1 - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.00
1.0
Published
May 2019
Language
English (United States)
Last Update
2019-11-22
dita:mapPath
blj1506016597986.ditamap
dita:ditavalPath
blj1506016597986.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

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 Call

SELECT * FROM nPath@coprocessor (
  ON (SELECT customer_id, session_id, datestamp, page FROM bank_web_clicks) AS alias1
    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 dt ORDER BY 1, 2;

Output

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