Input
Input table banking_web_clicks contains information for 5000 clicks to different pages in one session for one customer.
| customer_id | session_id | page | datestamp |
|---|---|---|---|
| 540 | 1 | ONLINE STATEMENT ENROLLMENT | 2022-03-18 06:14:20.008294 |
| 540 | 1 | CUSTOMER SUPPORT | 2022-03-18 06:14:21.009358 |
| 540 | 1 | ACCOUNT HISTORY | 2022-03-18 06:14:22.010443 |
| 540 | 1 | VIEW DEPOSIT DETAILS | 2022-03-18 06:14:23.011530 |
| 540 | 1 | PROFILE UPDATE | 2022-03-18 06:14:24.012609 |
| 540 | 1 | FREQUENTLY ASKED QUESTIONS | 2022-03-18 06:14:25.013711 |
| 540 | 1 | FUNDS TRANSFER | 2022-03-18 06:14:26.014798 |
| 540 | 1 | ACCOUNT HISTORY | 2022-03-18 06:14:27.015879 |
| 540 | 1 | ONLINE STATEMENT ENROLLMENT | 2022-03-18 06:14:28.016968 |
| 540 | 1 | BANK STATEMENT | 2022-03-18 06:14:29.018053 |
| 540 | 1 | COMPLAINTS | 2022-03-18 06:14:30.019146 |
| 540 | 1 | BANK STATEMENT | 2022-03-18 06:14:31.020222 |
| 540 | 1 | ACCOUNT SUMMARY | 2022-03-18 06:14:32.021308 |
| 540 | 1 | COMPLAINTS | 2022-03-18 06:14:33.022394 |
| 540 | 1 | FUNDS TRANSFER | 2022-03-18 06:14:34.023481 |
| ... | ... | ... | ... |
| 540 | 1 | CUSTOMER SUPPORT | 2022-03-18 07:37:44.428571 |
SQL Call
SELECT * FROM nPath (
ON banking_web_clicks PARTITION BY session_id ORDER BY datestamp
USING
Mode (NONOVERLAPPING)
Pattern ('A*')
Symbols (TRUE AS A)
Result (
FIRST (session_id OF A) AS session_id,
ACCUMULATE (100000)(page OF A) AS path
)
) AS dt;
Output
Because ACCUMULATE specifies a size greater than 64000, the data type of the path column is CLOB. The length of the value of path is 91009.
| session_id | path |
|---|---|
| 1 | [ONLINE STATEMENT ENROLLMENT, CUSTOMER SUPPORT, ACCOUNT HISTORY, VIEW DEPOSIT DETAILS, PROFILE UPDATE, FREQUENTLY ASKED QUESTIONS, FUNDS TRANSFER, ACCOUNT HISTORY, ONLINE STATEMENT ENROLLMENT, BANK STATEMENT, COMPLAINTS, BANK STATEMENT, ACCOUNT SUMMARY, COMPLAINTS, FUNDS TRANSFER, … , HOME PAGE] |