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.
  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. );
  12. INSERT into windowfunnel_test (xwho, xwhen, xwhat) values ('1', '2022-03-12 10:41:00', 1),
  13.                                                    ('1', '2022-03-12 13:28:02', 2),
  14.                                                    ('1', '2022-03-12 16:15:01', 3),
  15.                                                    ('1', '2022-03-12 19:05:04', 4);
  17. select window_funnel(3600 * 3, 'default', t.xwhen, t.xwhat = 1, t.xwhat = 2 ) AS level from windowfunnel_test t;
  19. | level |
  20. |---|
  21. | 2 |

keywords

  1. WINDOW,FUNCTION,WINDOW_FUNNEL