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 can be one of the followings:
    • “default”: Defualt mode.
    • “deduplication”: If the same event holds for the sequence of events, then such repeating event interrupts further processing. E.g. the array parameter is [event1=’A’, event2=’B’, event3=’C’, event4=’D’], and the original event chain is “A-B-C-B-D”. Since event B repeats, the filtered event chain can only be “A-B-C” and the max event level is 3.
    • “fixed”: Don’t allow interventions of other events. E.g. the array parameter is [event1=’A’, event2=’B’, event3=’C’, event4=’D’], and the original event chain is A->B->D->C, it stops finding A->B->C at the D and the max event level is 2.
    • “increase”: Apply conditions only to events with strictly increasing timestamps.
  • 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.
  1. window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)

example

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

keywords

  1. WINDOW,FUNCTION,WINDOW_FUNNEL