Sessionize Function Example | Teradata Vantage - Example: How to Use Sessionize - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantage™

Every complete example in this document is available in a zip file that you can download. The zip file includes a SQL script file that creates the input tables for the examples. If you are reading this document on https://docs.teradata.com/, you can download the zip file from the attachment in the left sidebar.

Sessionize Input

sessionize_table
clicktime userid productname pagetype referrer productprice
2023-03-17 11:31:59 333 Ipod Checkout www.yahoo.com 200.20
2023-03-17 12:07:20 67403 Home NULL NULL NULL
2023-03-17 12:03:20 67403 Home NULL www.google.com NULL
2023-03-17 11:50:00 67403 Home NULL www.google.com NULL
2023-03-17 12:22:00 80000 Home NULL godaddy.com NULL
2023-03-17 12:21:40 67403 Bose Checkout NULL 750.00
2023-03-17 12:26:40 880000 Iphone Checkout NULL 650.00
2023-03-17 11:40:00 333 Bose Checkout NULL 340.00
2023-03-17 12:13:20 67403 Iphone Checkout NULL 650.00
2023-03-17 11:46:40 333 Home NULL www.google.com NULL
2023-03-17 12:05:00 67403 Home NULL NUJLL NULL
2023-03-17 12:23:20 80000 Bose Checkout NULL 340.00
2023-03-17 11:30:00 333 Home NULL www.yahoo.com NULL
2023-03-17 12:23:20 80000 Itrip Checkout NULL 450.00

Sessionize SQL Call

SELECT * FROM SESSIONIZE (
  ON sessionize_table PARTITION BY user_id ORDER BY clicktime
  USING
  TimeColumn ('clicktime')
  TimeOut (300)
  ClickLag (0.2)
) ORDER BY user_id, clicktime;

Sessionize Output

clicktime userid productname pagetype referrer productprice SessionID Clicklag
2023-03-17 11:30:00 333 Home NULL www.yahoo.com NULL 0 f
2023-03-17 11:31:59 333 Ipod Checkout www.yahoo.com 200.20 0 f
2023-03-17 11:40:00 333 Bose Checkout NULL 340.00 1 f
2023-03-17 11:46:40 333 Home NULL www.google.com NULL 2 f
2023-03-17 11:50:00 67403 Home NULL www.google.com NULL 0 f
2023-03-17 12:03:20 67403 Home NULL www.google.com NULL 1 f
2023-03-17 12:05:00 67403 Home NULL NULL NULL 1 f
2023-03-17 12:07:20 67403 Home NULL NULL NULL 1 f
2023-03-17 12:13:20 67403 Iphone Checkout NULL 650.00 2 f
2023-03-17 12:21:40 67403 Bose Checkout NULL 750.00 3 f
2023-03-17 12:22:00 80000 Home NULL godaddy.com NULL 0 t
2023-03-17 12:23:20 80000 Bose Checkout NULL 340.00 0 f
2023-03-17 12:23:20 80000 Itrip Checkout NULL 450.00 0 f
2023-03-17 12:26:40 880000 Iphone Checkout NULL 650.00 0 f

There’s a new session recorded for each user if time between their clicks was more than 5 minutes (300 seconds). For userid 80000, time between two clicks was less than 0.2 seconds, and therefore recorded in clicklag column as ‘t’ for True.