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 ORDER BY message;
Output
| message |
Finished. Totally 69 patterns were found.
|
This query returns the following table:
SELECT * FROM output2 ORDER BY 3 DESC, 2 DESC, 1;
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 HISTORY;ACCOUNT SUMMARY;ACCOUNT HISTORY | 2 | 4
ACCOUNT SUMMARY;ACCOUNT HISTORY;ACCOUNT SUMMARY;FAQ | 2 | 4
ACCOUNT SUMMARY;ACCOUNT SUMMARY;ACCOUNT HISTORY;FAQ | 2 | 4
ACCOUNT SUMMARY;ACCOUNT SUMMARY;ACCOUNT SUMMARY;ACCOUNT HISTORY | 2 | 4
ACCOUNT SUMMARY;ACCOUNT SUMMARY;ACCOUNT SUMMARY;FUNDS TRANSFER | 2 | 4
ACCOUNT SUMMARY;ACCOUNT SUMMARY;FAQ;ACCOUNT SUMMARY | 2 | 4
ACCOUNT SUMMARY;FAQ;ACCOUNT HISTORY;ACCOUNT SUMMARY | 2 | 4
ACCOUNT SUMMARY;FAQ;ACCOUNT HISTORY;VIEW DEPOSIT DETAILS | 2 | 4
ACCOUNT SUMMARY;FAQ;ACCOUNT SUMMARY;ACCOUNT SUMMARY | 2 | 4
ACCOUNT SUMMARY;FAQ;ACCOUNT SUMMARY;VIEW DEPOSIT DETAILS | 2 | 4
… … …