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.
| 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.