1.1 - 8.10 - Sessionize_MLE Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

The input table, adweb_clickstream, contains web clickstream data recorded as a user navigates through a web site. Events—view, click, and so on—are recorded with a timestamp.

adweb_clickstream
userid adid productid event clicktime
1039 2 1001 view 2009-04-21 13:17:59
1039 2 1001 view 2009-04-21 13:17:59
1039 2 1001 view 2009-04-21 13:17:59
1039 3 1001 view 2009-05-23 13:17:59
1039 3 1001 view 2009-05-23 13:17:59
1039 3 1001 view 2009-05-23 13:17:59
1039 4 1001 view 2009-07-16 11:17:59
1039 4 1001 view 2009-07-16 11:17:59
1039 4 1001 view 2009-07-16 11:17:59
1039 4 1001 click 2009-07-16 11:18:16
1039 4 1001 click 2009-07-16 11:18:16
1039 4 1001 click 2009-07-16 11:18:16
1039 4 1001 landing_page 2009-07-16 11:18:18
1039 4 1001 landing_page 2009-07-16 11:18:18
1039 4 1001 landing_page 2009-07-16 11:18:18
... ... ... ... ...

SQL Call

SELECT * FROM Sessionize_MLE (
  ON adweb_clickstream PARTITION BY userid
  ORDER BY clicktime
  USING
  TimeColumn ('clicktime')
  TimeOut (60)
  Clicklag (0.2)
) AS dt;

Output

 userid adid productid event      clicktime                  sessionid clicklag 
 ------ ---- --------- ---------- -------------------------- --------- -------- 
   1039    2      1001 view       2009-04-21 13:17:59.000000         0        0
   1039    2      1001 view       2009-04-21 13:17:59.000000         0        1
   1039    2      1001 view       2009-04-21 13:17:59.000000         0        1
   1039    3      1001 view       2009-05-23 13:17:59.000000         1        0
   1039    3      1001 view       2009-05-23 13:17:59.000000         1        1
   1039    3      1001 view       2009-05-23 13:17:59.000000         1        1
   1039    4      1001 view       2009-07-16 11:17:59.000000         2        0
   1039    4      1001 view       2009-07-16 11:17:59.000000         2        1
   1039    4      1001 view       2009-07-16 11:17:59.000000         2        1
   1039    4      1001 click      2009-07-16 11:18:16.000000         2        0
   1039    4      1001 click      2009-07-16 11:18:16.000000         2        1
   1039    4      1001 click      2009-07-16 11:18:16.000000         2        1
   1039    4      1001 landing_pa 2009-07-16 11:18:18.000000         2        0
   1039    4      1001 landing_pa 2009-07-16 11:18:18.000000         2        1
   1039    4      1001 landing_pa 2009-07-16 11:18:18.000000         2        1
   1039    1      1001 view       2009-07-29 20:17:59.000000         3        0
   1039    1      1001 view       2009-07-29 20:17:59.000000         3        1
   1039    1      1001 view       2009-07-29 20:17:59.000000         3        1
   1039    5      1001 view       2009-08-19 22:17:59.000000         4        0
   1039    5      1001 view       2009-08-19 22:17:59.000000         4        1
   1039    5      1001 view       2009-08-19 22:17:59.000000         4        1
   1039    5      1001 click      2009-08-19 22:18:02.000000         4        0
   1039    5      1001 click      2009-08-19 22:18:02.000000         4        1
   1039    5      1001 click      2009-08-19 22:18:02.000000         4        1
   1039    5      1001 landing_pa 2009-08-19 22:18:05.000000         4        0
   1039    5      1001 landing_pa 2009-08-19 22:18:05.000000         4        1
   1039    5      1001 landing_pa 2009-08-19 22:18:05.000000         4        1
   1039    5      1001 page_02    2009-08-22 04:20:05.000000         5        0
   1039    5      1001 page_02    2009-08-22 04:20:05.000000         5        1
   1039    5      1001 page_02    2009-08-22 04:20:05.000000         5        1
   1039    5      1001 page_03    2009-08-24 14:30:05.000000         6        0
   1039    5      1001 page_03    2009-08-24 14:30:05.000000         6        1
   1039    5      1001 page_03    2009-08-24 14:30:05.000000         6        1
   1039    5      1001 page_02    2009-08-27 23:03:05.000000         7        0
   1039    5      1001 page_02    2009-08-27 23:03:05.000000         7        1
   1039    5      1001 page_02    2009-08-27 23:03:05.000000         7        1
   1071    4      1001 view       2009-03-13 17:17:59.000000         0        0
   1071    4      1001 view       2009-03-13 17:17:59.000000         0        1
   1071    3      1001 view       2009-04-28 01:17:59.000000         1        0
   1071    3      1001 view       2009-04-28 01:17:59.000000         1        1
   1071    2      1001 view       2009-05-25 00:17:59.000000         2        0
   1071    2      1001 view       2009-05-25 00:17:59.000000         2        1
   1071    2      1001 view       2009-05-25 00:17:59.000000         2        1
   1071    1      1001 view       2009-06-15 18:17:59.000000         3        0
   1071    1      1001 view       2009-06-15 18:17:59.000000         3        1
   1071    1      1001 view       2009-06-15 18:17:59.000000         3        1
   1103    1      1001 view       2009-05-11 22:17:59.000000         0        0
   1103    1      1001 view       2009-05-11 22:17:59.000000         0        1
   1103    2      1001 view       2009-08-03 01:17:59.000000         1        0
   1103    2      1001 view       2009-08-03 01:17:59.000000         1        1
   1135    2      1001 view       2009-04-23 20:17:59.000000         0        0
   1135    2      1001 view       2009-04-23 20:17:59.000000         0        1
   1135    3      1001 view       2009-05-11 01:17:59.000000         1        0
   1135    3      1001 view       2009-05-11 01:17:59.000000         1        1
   1135    4      1001 view       2009-06-26 12:17:59.000000         2        0
   1135    4      1001 view       2009-06-26 12:17:59.000000         2        1
   1135    1      1001 view       2009-08-23 08:17:59.000000         3        0
   1135    1      1001 view       2009-08-23 08:17:59.000000         3        1
   1167    3      1001 view       2009-05-21 08:17:59.000000         0        0
   1167    3      1001 view       2009-05-21 08:17:59.000000         0        1
   1167    1      1001 view       2009-07-16 14:17:59.000000         1        0
   1167    1      1001 view       2009-07-16 14:17:59.000000         1        1
   1167    2      1001 view       2009-07-24 04:17:59.000000         2        0
   1167    2      1001 view       2009-07-24 04:17:59.000000         2        1
   1167    2      1001 click      2009-07-24 04:18:10.000000         2        0
   1167    2      1001 click      2009-07-24 04:18:10.000000         2        1
   1167    2      1001 landing_pa 2009-07-24 04:18:12.000000         2        0
   1167    2      1001 landing_pa 2009-07-24 04:18:12.000000         2        1
   1167    2      1001 page_03    2009-07-27 06:10:12.000000         3        0
   1167    2      1001 page_03    2009-07-27 06:10:12.000000         3        1
   1167    2      1001 page_03    2009-07-30 02:20:12.000000         4        0
   1167    2      1001 page_03    2009-07-30 02:20:12.000000         4        1
   1199    2      1001 view       2009-03-09 21:17:59.000000         0        0
   1199    2      1001 view       2009-03-09 21:17:59.000000         0        1
   1199    1      1001 view       2009-03-19 01:17:59.000000         1        0
   1199    1      1001 view       2009-03-19 01:17:59.000000         1        1
   1199    1      1001 click      2009-03-19 01:18:08.000000         1        0
   1199    1      1001 click      2009-03-19 01:18:08.000000         1        1
   1199    1      1001 landing_pa 2009-03-19 01:18:09.000000         1        0
   1199    1      1001 landing_pa 2009-03-19 01:18:09.000000         1        1
   1199    1      1001 view       2009-03-19 16:43:09.000000         2        0
   1199    1      1001 view       2009-03-19 16:43:09.000000         2        1
   1199    1      1001 click      2009-03-19 16:43:26.000000         2        0
   1199    1      1001 click      2009-03-19 16:43:26.000000         2        1
   1199    1      1001 landing_pa 2009-03-19 16:43:27.000000         2        0
   1199    1      1001 landing_pa 2009-03-19 16:43:27.000000         2        1
   1199    3      1001 view       2009-08-15 12:17:59.000000         3        0
   1199    3      1001 view       2009-08-15 12:17:59.000000         3        1
   1231    2      1001 view       2009-04-27 01:17:59.000000         0        0
   1231    2      1001 view       2009-04-27 01:17:59.000000         0        1
   1231    1      1001 view       2009-07-04 09:17:59.000000         1        0
   1231    1      1001 view       2009-07-04 09:17:59.000000         1        1
   1231    1      1001 click      2009-07-04 09:18:17.000000         1        0
   1231    1      1001 click      2009-07-04 09:18:17.000000         1        1
   1231    1      1001 landing_pa 2009-07-04 09:18:20.000000         1        0
   1231    1      1001 landing_pa 2009-07-04 09:18:20.000000         1        1
   1231    1      1001 page_03    2009-07-06 13:00:20.000000         2        0
   1231    1      1001 page_03    2009-07-06 13:00:20.000000         2        1
   1231    1      1001 view       2009-07-06 19:20:20.000000         3        0
   1231    1      1001 view       2009-07-06 19:20:20.000000         3        1
   1231    1      1001 view       2009-07-08 18:14:20.000000         4        0
   1231    1      1001 view       2009-07-08 18:14:20.000000         4        1
   1263    4      1001 view       2009-02-09 15:17:59.000000         0        0
   1263    3      1001 view       2009-03-24 01:17:59.000000         1        0
   1263    5      1001 view       2009-07-30 11:17:59.000000         2        0
   1263    2      1001 view       2009-08-13 13:17:59.000000         3        0
   1263    1      1001 view       2009-08-14 02:17:59.000000         4        0
   9231    2      1001 view       2009-05-31 05:17:59.000000         0        0
   9231    1      1001 view       2009-08-01 17:17:59.000000         1        0
   9231    3      1001 view       2009-08-08 02:17:59.000000         2        0
   9231    3      1001 click      2009-08-08 02:18:12.000000         2        0
   9231    3      1001 landing_pa 2009-08-08 02:18:15.000000         2        0
   9231    3      1001 view       2009-08-11 00:01:15.000000         3        0
   9231    3      1001 click      2009-08-11 00:01:24.000000         3        0
   9231    3      1001 landing_pa 2009-08-11 00:01:27.000000         3        0

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.