WINDOW_FUNNEL

Syntax

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

Purpose

WINDOW_FUNNEL is an aggregate function that calculates the maximum number of consecutive steps satisfying specified conditions within a given time window.

This procedure is compatible with Hologres/ClickHouse syntax.

Parameters

Return description

• Returns a UINT32 type with values in the range [0, N], where N is the number of conditions.
• Indicates the maximum number of steps, in sequence, matched from the start of the specified time window based on the first condition.

Examples

1. Create the test_tbl1 table.

   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'), -- Exceeds 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 and count the number of users (identified by col2) that completed the steps 'step1' → 'step2' → 'step3' within 600 seconds for each group.

   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 output is as follows:

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

4. Comparison of default and strict_deduplication modes: The difference between the default mode and the strict_deduplication mode is shown.

   • default mode: By the col1 column, count the number of users (identified by the col2 column) with col1 = 3 who have completed the 'step1' → 'step2' → 'step3' funnel stages in the last 1800 seconds.

     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 duplicate events): Group by col1 and count the number of users with col1 = 3 (identified by col2) that complete 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 following sample outputs are returned:

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