WINDOW_FUNNEL

Syntax

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

Purpose

WINDOW_FUNNEL is an aggregation funnel analysis function. It calculates the maximum number of steps that meet the conditions in order within the specified time window.

This function is compatible with Hologres/ClickHouse syntax.

Parameters

Return description

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

Examples

1. Create the test_tbl1 table.

   obclient> CREATE TABLE test_tbl1 (col1 INT, col2 DATETIME, col3 VARCHAR(50));
   

2. Insert test data into the test_tbl1 table.

   obclient> INSERT INTO test_tbl1 VALUES
       (1, '2026-01-01 10:00:00', 'step1'),
       (1, '2026-01-01 10:05:00', 'step2'),
       (1, '2026-01-01 10:09:00', 'step3'), -- within a 10-minute window
       (2, '2026-01-01 11:00:00', 'step1'),
       (2, '2026-01-01 11:05:00', 'step2'),
       (2, '2026-01-01 11:15:00', 'step3'), -- exceeds 10-minute window
       (3, '2026-01-01 11:00:00', 'step1'),
       (3, '2026-01-01 11:01:00', 'step2'),
       (3, '2026-01-01 11:02:00', 'step1'), -- repeated step1
       (3, '2026-01-01 11:03:00', 'step3');
   

3. Group by col1 (which has values 1 or 2), and count the number of users (identified by col2) who complete the 'step1' → 'step2' → 'step3' process in 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;
   

   Here is the result:

   +------+--------------+
   | col1 | funnel_stage |
   +------+--------------+
   |    1 |            3 |
   |    2 |            2 |
   +------+--------------+
   2 rows in set
   

4. Different modes: highlights the differences between the default and strict_deduplication modes.

   • default mode: Group data by col1. Count the users whose col1 = 3 (identified by col2) who complete the 'step1' → 'step2' → 'step3' funnel in 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 output is as follows:

     +------+--------------+
     | col1 | funnel_stage |
     +------+--------------+
     |    3 |            3 |
     +------+--------------+
     1 row in set
     

   • strict_deduplication mode (stops on duplicates): Group by col1 to track the progress of users with col1 = 3 (identified by col2) through the funnel stages 'step1' → 'step2' → 'step3' 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