1.0 - 8.00 - PathGenerator Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

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;

Output

userid usertype path cnt prefix sequence
1 browser H,E 2 ^,H ^,H,E
1 browser H,E 2 ^,H,E ^,H,E
2 browser H,C,P,E 5 ^,H ^,H,C,P,E
2 browser H,C,P,E 5 ^,H,C ^,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 ^,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 ^,H,C,H,P
3 browser H,C,H,P 7 ^,H,C,H,P ^,H,C,H,P
4 browser A,H,C 3 ^,A ^,A,H,C
4 browser A,H,C 3 ^,A,H ^,A,H,C
4 browser A,H,C 3 ^,A,H,C ^,A,H,C
5 browser A,H,P 2 ^,A ^,A,H,P
5 browser A,H,P 2 ^,A,H ^,A,H,P
5 browser A,H,P 2 ^,A,H,P ^,A,H,P
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 ^,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 ^,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,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,S,O ^,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
7 buyer A,P,S,O, 6 ^,A ^,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,P,S ^,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 ^,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,C,P ^,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,S,O ^,P,C,P,S,O,E
8 buyer P,C,P,S,O,E 2 ^,P,C,P,S,O,E ^,P,C,P,S,O,E
9 buyer H,A,P,I,S,O 2 ^,H ^,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,A,P ^,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,I,S ^,H,A,P,I,S,O
9 buyer H,A,P,I,S,O 2 ^,H,A,P,I,S,O ^,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 ^,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,O,E ^,C,S,O,E