PathGenerator Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

E-Commerce Website Page Symbols

This example uses clickstream data from an e-commerce web site. The following table lists and describes the symbols of the web site pages.

Symbol Meaning Description
H Home Home page
A Account User account pages
C Category Page with list of products
P Product Product information pages
I Information Shipping, order status, etc.
S ShoppingCart Pre-order pages
O Order Confirmation/purchase page
E Enter/Exit Noncommercial vendor pages

Input

The input table, clickstream1, identifies each customer by a userid. The path column lists the symbols for the pages that the customer clicked, ordered from first to last. For symbol meanings, see the preceding table.

clickstream1
userid usertype path cnt
1 browser H,E 2
2 browser H,C,P,E 5
3 browser H,C,H,P 7
4 browser A,H,C 3
5 browser A,H,P 2
6 buyer H,A,C,P,I,S,O,E 4
7 buyer A,P,S,O, 6
8 buyer P,C,P,S,O,E 2
9 buyer H,A,P,I,S,O 2
10 buyer C,S,O,E 1

SQL Call

SELECT * FROM PathGenerator(
  ON clickstream1
  USING
  SeqColumn('path')
  Delimiter(',')
) AS dt ORDER BY userid, usertype, cnt ASC;

Output

 userid usertype path            cnt prefix            sequence          
 ------ -------- --------------- --- ----------------- ----------------- 
      1 browser  h,e               2 ^,h,e             ^,h,e            
      1 browser  h,e               2 ^,h               ^,h,e            
      2 browser  h,c,p,e           5 ^,h,c             ^,h,c,p,e        
      2 browser  h,c,p,e           5 ^,h               ^,h,c,p,e        
      2 browser  h,c,p,e           5 ^,h,c,p           ^,h,c,p,e        
      2 browser  h,c,p,e           5 ^,h,c,p,e         ^,h,c,p,e        
      3 browser  h,c,h,p           7 ^,h,c,h           ^,h,c,h,p        
      3 browser  h,c,h,p           7 ^,h,c             ^,h,c,h,p        
      3 browser  h,c,h,p           7 ^,h,c,h,p         ^,h,c,h,p        
      3 browser  h,c,h,p           7 ^,h               ^,h,c,h,p        
      4 browser  a,h,c             3 ^,a,h,c           ^,a,h,c          
      4 browser  a,h,c             3 ^,a,h             ^,a,h,c          
      4 browser  a,h,c             3 ^,a               ^,a,h,c          
      5 browser  a,h,p             2 ^,a,h,p           ^,a,h,p          
      5 browser  a,h,p             2 ^,a,h             ^,a,h,p          
      5 browser  a,h,p             2 ^,a               ^,a,h,p          
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c,p,i,s     ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c,p,i       ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c           ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c,p,i,s,o   ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h               ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c,p         ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a,c,p,i,s,o,e ^,h,a,c,p,i,s,o,e
      6 buyer    h,a,c,p,i,s,o,e   4 ^,h,a             ^,h,a,c,p,i,s,o,e
      7 buyer    a,p,s,o,          6 ^,a,p,s           ^,a,p,s,o,       
      7 buyer    a,p,s,o,          6 ^,a,p             ^,a,p,s,o,       
      7 buyer    a,p,s,o,          6 ^,a               ^,a,p,s,o,       
      7 buyer    a,p,s,o,          6 ^,a,p,s,o         ^,a,p,s,o,       
      8 buyer    p,c,p,s,o,e       2 ^,p,c,p,s,o,e     ^,p,c,p,s,o,e    
      8 buyer    p,c,p,s,o,e       2 ^,p,c,p,s,o       ^,p,c,p,s,o,e    
      8 buyer    p,c,p,s,o,e       2 ^,p,c,p,s         ^,p,c,p,s,o,e    
      8 buyer    p,c,p,s,o,e       2 ^,p,c,p           ^,p,c,p,s,o,e    
      8 buyer    p,c,p,s,o,e       2 ^,p,c             ^,p,c,p,s,o,e    
      8 buyer    p,c,p,s,o,e       2 ^,p               ^,p,c,p,s,o,e    
      9 buyer    h,a,p,i,s,o       2 ^,h,a,p,i,s,o     ^,h,a,p,i,s,o    
      9 buyer    h,a,p,i,s,o       2 ^,h,a,p,i,s       ^,h,a,p,i,s,o    
      9 buyer    h,a,p,i,s,o       2 ^,h,a,p,i         ^,h,a,p,i,s,o    
      9 buyer    h,a,p,i,s,o       2 ^,h,a,p           ^,h,a,p,i,s,o    
      9 buyer    h,a,p,i,s,o       2 ^,h,a             ^,h,a,p,i,s,o    
      9 buyer    h,a,p,i,s,o       2 ^,h               ^,h,a,p,i,s,o    
     10 buyer    c,s,o,e           1 ^,c               ^,c,s,o,e        
     10 buyer    c,s,o,e           1 ^,c,s,o,e         ^,c,s,o,e        
     10 buyer    c,s,o,e           1 ^,c,s,o           ^,c,s,o,e        
     10 buyer    c,s,o,e           1 ^,c,s             ^,c,s,o,e

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