1.1 - 8.10 - FrequentPaths Example: ItemDefinitionTable - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

  • InputTable: bank_web_url, which has the URL of each page browsed by the customer
  • ItemDefinitionTable: ref_url, which has the definitions of the browser pages
InputTable: bank_web_url
session_id page_url datestamp
0 www.bank.com/acsum 2004-03-17 16:35:00
0 www.bank.com/faq 2004-03-17 16:38:00
0 www.bank.com/achist 2004-03-17 16:42:00
0 www.bank.com/fundsxfer 2004-03-17 16:45:00
0 www.bank.com/onlinestat 2004-03-17 16:49:00
0 www.bank.com/profile 2004-03-17 16:50:00
0 www.bank.com/acsum 2004-03-17 16:51:00
0 www.bank.com/customer 2004-03-17 16:53:00
0 www.bank.com/deposit 2004-03-17 16:57:00
1 www.bank.com/acsum 2004-03-18 01:16:00
1 www.bank.com/acsum 2004-03-18 01:18:00
1 www.bank.com/faq 2004-03-18 01:20:00
1 www.bank.com/acsum 2004-03-18 01:21:00
1 www.bank.com/fundsxfer 2004-03-18 01:24:00
1 www.bank.com/achist 2004-03-18 01:25:00
1 www.bank.com/deposit 2004-03-18 01:27:00
1 www.bank.com/acsum 2004-03-18 01:27:00
1 www.bank.com/achist 2004-03-18 01:28:00
2 www.bank.com/acsum 2004-03-18 09:22:00
2 www.bank.com/acsum 2004-03-18 09:23:00
2 www.bank.com/acsum 2004-03-18 09:25:00
2 www.bank.com/achist 2004-03-18 09:27:00
2 www.bank.com/fundsxfer 2004-03-18 09:31:00
2 www.bank.com/acsum 2004-03-18 09:31:00
2 www.bank.com/faq 2004-03-18 09:33:00
2 www.bank.com/faq 2004-03-18 09:36:00
3 www.bank.com/acsum 2004-03-18 22:41:00
3 www.bank.com/achist 2004-03-18 22:45:00
3 www.bank.com/acsum 2004-03-18 22:47:00
3 www.bank.com/achist 2004-03-18 22:49:00
3 www.bank.com/faq 2004-03-18 22:50:00
3 www.bank.com/acsum 2004-03-18 22:53:00
3 www.bank.com/acsum 2004-03-18 22:55:00
4 www.bank.com/acsum 2004-03-19 08:33:00
4 www.bank.com/faq 2004-03-19 08:36:00
4 www.bank.com/deposit 2004-03-19 08:38:00
4 www.bank.com/faq 2004-03-19 08:41:00
5 www.bank.com/acsum 2004-03-19 10:06:00
5 www.bank.com/fundsxfer 2004-03-19 10:09:00
5 www.bank.com/deposit 2004-03-19 10:11:00
5 www.bank.com/deposit 2004-03-19 10:13:00
5 www.bank.com/achist 2004-03-19 10:14:00
ItemDefinitionTable: ref_url
page_id pagedef page
1 page_url like '%acsum%' ACCOUNT SUMMARY
2 page_url like '%faq%' FAQ
3 page_url like '%achist%' ACCOUNT HISTORY
4 page_url like '%fundsxfer%' FUNDS TRANSFER
5 page_url like '%onlinestat%' ONLINE STATEMENT ENROLLMENT
6 page_url like '%profile%' PROFILE UPDATE
8 page_url like '%customer%' CUSTOMER SUPPORT
9 page_url like '%deposit%' VIEW DEPOSIT DETAILS

SQL Call

SELECT * FROM FrequentPaths(
  ON bank_web_url AS InputTable
  ON ref_url AS ItemDefinitionTable
  OUT TABLE OutputTable(output2)
  USING
  PartitionColumns('session_id')
  TimeColumn('datestamp')
  ItemDefinitionColumns('[page_id:pagedef:page]')
  MinSupport(2)
) AS dt ;

Output

message                           
 --------------------------------- 
 Finished. 69 patterns were found.

SELECT CAST(pattern AS varchar(500)), support, length FROM output2 ORDER BY 3 DESC, 2 DESC;

 pattern                                                                         support length 
 ------------------------------------------------------------------------------- ------- ------ 
 account summary;account summary;account summary;account history;account summary       2      5
 account summary;account summary;faq;account summary;account summary                   2      5
 account summary;account summary;account history;account summary                       3      4
 account summary;account summary;account summary;account summary                       3      4
 account summary;account summary;account summary;funds transfer                        2      4
 account summary;account summary;account summary;account history                       2      4
 account summary;account history;account summary;account history                       2      4
 account summary;funds transfer;view deposit details;account history                   2      4
 account summary;account history;account summary;faq                                   2      4
 account summary;faq;account history;view deposit details                              2      4
 account summary;account summary;account history;faq                                   2      4
 account summary;faq;account history;account summary                                   2      4
 account summary;faq;account summary;view deposit details                              2      4
 account summary;faq;funds transfer;view deposit details                               2      4
 account summary;faq;funds transfer;account summary                                    2      4
 account summary;account summary;faq;account summary                                   2      4
 account summary;faq;account summary;account summary                                   2      4
 account summary;account history;account summary                                       4      3
 account summary;account summary;account summary                                       3      3
 account summary;account summary;account history                                       3      3
 account summary;funds transfer;view deposit details                                   3      3
 account summary;faq;account summary                                                   3      3
 account summary;faq;view deposit details                                              3      3
 account summary;account summary;faq                                                   3      3
 funds transfer;view deposit details;account history                                   2      3
 account summary;account summary;funds transfer                                        2      3
 account summary;account summary;view deposit details                                  2      3
 account summary;funds transfer;account summary                                        2      3
 account history;account summary;account history                                       2      3
 account summary;account history;funds transfer                                        2      3
 account summary;funds transfer;account history                                        2      3
 account summary;account history;account history                                       2      3
 account summary;account history;view deposit details                                  2      3
 account summary;view deposit details;account history                                  2      3
 account summary;faq;faq                                                               2      3
 account summary;account history;faq                                                   2      3
 faq;account summary;account summary                                                   2      3
 account history;account summary;faq                                                   2      3
 account summary;faq;account history                                                   2      3
 faq;account summary;view deposit details                                              2      3
 faq;account history;account summary                                                   2      3
 faq;account history;view deposit details                                              2      3
 account summary;faq;funds transfer                                                    2      3
 faq;funds transfer;account summary                                                    2      3
 faq;funds transfer;view deposit details                                               2      3
 account summary;account history                                                       5      2
 account summary;faq                                                                   5      2
 account history;account summary                                                       4      2
 account summary;view deposit details                                                  4      2
 account summary;account summary                                                       4      2
 account summary;funds transfer                                                        4      2
 funds transfer;view deposit details                                                   3      2
 faq;view deposit details                                                              3      2
 faq;account summary                                                                   3      2
 view deposit details;account history                                                  2      2
 funds transfer;account history                                                        2      2
 account history;view deposit details                                                  2      2
 account history;funds transfer                                                        2      2
 funds transfer;account summary                                                        2      2
 faq;faq                                                                               2      2
 account history;account history                                                       2      2
 faq;account history                                                                   2      2
 account history;faq                                                                   2      2
 faq;funds transfer                                                                    2      2
 account summary                                                                       6      1
 account history                                                                       5      1
 faq                                                                                   5      1
 view deposit details                                                                  4      1
 funds transfer                                                                        4      1

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