1.1 - 8.10 - FrequentPaths Example: GroupByColumns - 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_clicks2
customer_id session_id page datestamp
529 0 ACCOUNT SUMMARY 2004-03-17 16:35:00
529 0 FAQ 2004-03-17 16:38:00
529 0 ACCOUNT HISTORY 2004-03-17 16:42:00
529 0 FUNDS TRANSFER 2004-03-17 16:45:00
529 0 ONLINE STATEMENT ENROLLMENT 2004-03-17 16:49:00
529 0 PROFILE UPDATE 2004-03-17 16:50:00
529 0 ACCOUNT SUMMARY 2004-03-17 16:51:00
529 0 CUSTOMER SUPPORT 2004-03-17 16:53:00
529 0 VIEW DEPOSIT DETAILS 2004-03-17 16:57:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:16:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:18:00
529 1 FAQ 2004-03-18 01:20:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:21:00
529 1 FUNDS TRANSFER 2004-03-18 01:24:00
529 1 ACCOUNT HISTORY 2004-03-18 01:25:00
529 1 VIEW DEPOSIT DETAILS 2004-03-18 01:27:00
529 1 ACCOUNT SUMMARY 2004-03-18 01:27:00
529 1 ACCOUNT HISTORY 2004-03-18 01:28:00
... ... ... ...

SQL Call

SELECT * FROM FrequentPaths(
  ON bank_web_clicks2 AS InputTable
  OUT TABLE OutputTable(output3)
  USING
  PartitionColumns('session_id')
  GroupByColumns('customer_id')
  TimeColumn('datestamp')
  ItemColumn('page')
  MinSupport(2)
) AS dt;

Output

 message                            
 ---------------------------------- 
 Finished. 213 patterns were found.

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

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

(Row order can vary.)

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