WINDOW_FUNNEL

Declaration

WINDOW_FUNNEL(window, mode, timestamp, cond1, cond2, ..., condN)

Description

WINDOW_FUNNEL is a funnel analysis aggregate function that calculates the maximum number of steps that meet the condition in sequence within a specified time window.

This function is compatible with Hologres/ClickHouse syntax.

Parameters

Response parameters

• Returns a UINT32 value in the range [0, N], where N is the number of conditional events.
• Indicates the maximum number of steps matched in sequence from the first condition within the specified time window.

Examples

1. Create the table test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT, col2 DATE, col3 VARCHAR2(50));
   

2. Insert test data into the test_tbl1 table.

   obclient> INSERT INTO test_tbl1 VALUES
       (1, TO_DATE('2026-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'step1'),
       (1, TO_DATE('2026-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS'), 'step2'),
       (1, TO_DATE('2026-01-01 10:09:00', 'YYYY-MM-DD HH24:MI:SS'), 'step3'), -- Within a 10-minute window
       (2, TO_DATE('2026-01-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'step1'),
       (2, TO_DATE('2026-01-01 11:05:00', 'YYYY-MM-DD HH24:MI:SS'), 'step2'),
       (2, TO_DATE('2026-01-01 11:15:00', 'YYYY-MM-DD HH24:MI:SS'), 'step3'), -- Outside the 10-minute window
       (3, TO_DATE('2026-01-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'step1'),
       (3, TO_DATE('2026-01-01 11:01:00', 'YYYY-MM-DD HH24:MI:SS'), 'step2'),
       (3, TO_DATE('2026-01-01 11:02:00', 'YYYY-MM-DD HH24:MI:SS'), 'step1'), -- duplicate step1
       (3, TO_DATE('2026-01-01 11:03:00', 'YYYY-MM-DD HH24:MI:SS'), 'step3');
   

3. Group by col1. The value of col1 is either 1 or 2. Calculate the funnel stage progress for each group, where users (identified by col2) complete the 'step1' → 'step2' → 'step3' process within 600 seconds.

   obclient> SELECT
       col1,
       WINDOW_FUNNEL(
           600,
           'default',
           col2,
           col3 = 'step1', col3 = 'step2', col3 = 'step3') AS funnel_stage
       FROM test_tbl1
       WHERE col1 IN (1, 2)
       GROUP BY col1
       ORDER BY col1;
   

   The return result is as follows:

   +------+--------------+
   | COL1 | FUNNEL_STAGE |
   +------+--------------+
   |    1 |            3 |
   |    2 |            2 |
   +------+--------------+
   2 rows in set
   

4. Comparison of different modes: Displays the differences between the default mode and the strict_deduplication mode.

   • default mode: Group by col1 and count the progress of users (col2) who completed the funnel stage of the 'step1' → 'step2' → 'step3' process within 1,800 seconds when col1 = 3.

     obclient> SELECT 
         col1,
         WINDOW_FUNNEL(
             1800,
             'default',
             col2,
             col3 = 'step1', col3 = 'step2', col3 = 'step3') AS funnel_stage
         FROM test_tbl1
         WHERE col1 = 3
         GROUP BY col1;
     

     The return result is as follows:

     +------+--------------+
     | COL1 | FUNNEL_STAGE |
     +------+--------------+
     |    3 |            3 |
     +------+--------------+
     1 row in set
     

   • strict_deduplication mode (stops upon encountering duplicate events): Group by col1 and count the progress of users with col1 = 3 (identified by col2) completing the funnel stage of the 'step1' → 'step2' → 'step3' process within 1800 seconds.

     obclient> SELECT
         col1,
         WINDOW_FUNNEL(
             1800,
             'strict_deduplication',
             col2,
             col3 = 'step1', col3 = 'step2', col3 = 'step3') AS funnel_stage
         FROM test_tbl1
         WHERE col1 = 3
         GROUP BY col1;
     

     The return result is as follows:

     +------+--------------+
     | COL1 | FUNNEL_STAGE |
     +------+--------------+
     |    3 |            2 |
     +------+--------------+
     1 row in set