ClickStream Data Examples - Teradata® Database

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 );

) ;

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;

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;

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);