WINDOW FUNCTION WINDOW_FUNNEL

description

Searches the longest event chain happened in order (event1, event2, … , eventN) along the timestamp_column with length of window.

• window is the length of time window in seconds.
• mode is reserved for future, not used for now.
• timestamp_column specifies column of DATETIME type, sliding time window works on it.
• evnetN is boolean expression like eventID = 1004.

The function works according to the algorithm:

• The function searches for data that triggers the first condition in the chain and sets the event counter to 1. This is the moment when the sliding window starts.
• If events from the chain occur sequentially within the window, the counter is incremented. If the sequence of events is disrupted, the counter is not incremented.
• If the data has multiple event chains at varying points of completion, the function will only output the size of the longest chain.

window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)

example

CREATE TABLE windowfunnel_test (
                `xwho` varchar(50) NULL COMMENT 'xwho',
                `xwhen` datetime COMMENT 'xwhen',
                `xwhat` int NULL COMMENT 'xwhat'
                )
DUPLICATE KEY(xwho)
DISTRIBUTED BY HASH(xwho) BUCKETS 3
PROPERTIES (
    "replication_num" = "1"
);
INSERT into windowfunnel_test (xwho, xwhen, xwhat) values ('1', '2022-03-12 10:41:00', 1),
                                                   ('1', '2022-03-12 13:28:02', 2),
                                                   ('1', '2022-03-12 16:15:01', 3),
                                                   ('1', '2022-03-12 19:05:04', 4);
select window_funnel(3600 * 3, 'default', t.xwhen, t.xwhat = 1, t.xwhat = 2 ) AS level from windowfunnel_test t;
| level |
|---|
| 2 |

keywords

WINDOW,FUNCTION,WINDOW_FUNNEL