16.10 - ClickStream Data Examples - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K

Input, Symbols, and Symbol Predicates

This statement creates the input table of clickstream data that the examples use:
CREATE Multiset TABLE clicks1 (
   userid INTEGER,
   sessionid INTEGER,
   pageid INTEGER,
   category INTEGER,
   ts TIMESTAMP   FORMAT 'YYYY-MM-DDbHH:MI:SS',
   referrer VARCHAR (256),
   val FLOAT) PRIMARY INDEX ( userid );

) ;

The following table summarizes the symbols and symbol predicates that the examples use.

nPath Clickstream Data Examples Symbols and Symbol Predicates
Symbol Symbol Predicate
A pageid IN (10, 25)
B category = 10 OR (category = 20 AND pageid <> 33)
C category IN (SELECT pageid FROM clicks1 GROUP BY userid HAVING COUNT(*) > 10)
D referrer LIKE '%Amazon%'
X true
This invocation gets the pageid for each row and the pageid for the next row in sequence:
SELECT dt.sessionid, dt.pageid, dt.next_pageid FROM NPATH (
   ON clicks1
   PARTITION BY sessionid
   ORDER BY ts
   USING
   MODE (OVERLAPPING)
   PATTERN ('A.B')
   SYMBOLS (true AS A, true AS B)
   RESULT (FIRST(sessionid OF A) AS sessionid,
      FIRST (pageid OF A) AS pageid,
      FIRST (pageid OF B) AS next_pageid
   )
) as dt;

Counting Preceding Rows in a Sequence

For each row, this invocation counts the number of preceding rows in a given sequence (including the current row). The ORDER BY clause specifies DESC because the pattern must be matched over the rows preceding the start row, while the semantics dictate that the pattern be matched over the rows following the start row.
SELECT dt.sessionid, dt.pageid, dt.rank FROM NPATH (
   ON clicks1
   PARTITION BY sessionid
   ORDER BY ts DESC
   USING
   MODE (OVERLAPPING)
   PATTERN ('A*')
   SYMBOLS (true AS A)
   RESULT (FIRST(sessionid OF A) AS sessionid,
   FIRST (pageid OF A) AS pageid,
   COUNT (* OF A) AS countrank)
) as dt;

Complex Path Query

This query finds the user click-paths that start at pageid 50 and proceed either to pageid80 or to pages in category 9 or category 10, finds the pageid of the last page in the path, counts the visits to page 80, and returns the maximum count for each last page, by which it sorts the output. The query ignores paths of fewer than five pages and pages for which category is less than zero.
SELECT dt.last_pageid, MAX(dt.count_page80) FROM NPATH (
   ON (SELECT * FROM clicks1 WHERE category >= 0)
   PARTITION BY sessionid ORDER BY ts
   USING
   PATTERN ('A.(B|C)*')
   MODE (OVERLAPPING)
   SYMBOLS (pageid = 50 AS A,
   pageid = 80 AS B,
   pageid <> 80 AND category IN (9,10) AS C)
   RESULT (LAST(pageid OF ANY (A,B,C)) AS last_pageid,
   COUNT (* OF B) AS count_page80,
   COUNT (* OF ANY (A,B,C)) AS count_any)
) as dt WHERE dt.count_any >= 5
GROUP BY dt.last_pageid
ORDER BY MAX(dt.count_page80);