- Release Notes
- Introduction to CelerData Cloud Serverless
- Quick Start
- Sign up for CelerData Cloud Serverless
- A quick tour of the console
- Connect to CelerData Cloud Serverless
- Create an IAM integration
- Create and assign a warehouse
- Create an external catalog
- Load data from cloud storage
- Load data from Apache Kafka/Confluent Cloud
- Try your first query
- Invite new users
- Design data access control policy
- Warehouses
- Catalog, database, table, view, and MV
- Overview of database objects
- Catalog
- Table types
- Asynchronous materialized views
- Data Loading
- Data access control
- Networking and private connectivity
- Usage and Billing
- Organization and Account
- Integration
- Query Acceleration
- Reference
- AWS IAM policies
- Information Schema
- Overview
- be_bvars
- be_cloud_native_compactions
- be_compactions
- character_sets
- collations
- column_privileges
- columns
- engines
- events
- global_variables
- key_column_usage
- load_tracking_logs
- loads
- materialized_views
- partitions
- pipe_files
- pipes
- referential_constraints
- routines
- schema_privileges
- schemata
- session_variables
- statistics
- table_constraints
- table_privileges
- tables
- tables_config
- task_runs
- tasks
- triggers
- user_privileges
- views
- Data Types
- System Metadatabase
- Keywords
- SQL Statements
- Account Management
- Data Definition
- CREATE TABLE
- ALTER TABLE
- DROP CATALOG
- CREATE TABLE LIKE
- REFRESH EXTERNAL TABLE
- RESTORE
- SET CATALOG
- DROP TABLE
- RECOVER
- USE
- CREATE MATERIALIZED VIEW
- DROP DATABASE
- ALTER MATERIALIZED VIEW
- DROP REPOSITORY
- CANCEL RESTORE
- DROP INDEX
- DROP MATERIALIZED VIEW
- CREATE DATABASE
- CREATE TABLE AS SELECT
- BACKUP
- CANCEL BACKUP
- CREATE REPOSITORY
- CREATE INDEX
- Data Manipulation
- INSERT
- SHOW CREATE DATABASE
- SHOW BACKUP
- SHOW ALTER MATERIALIZED VIEW
- SHOW CATALOGS
- SHOW CREATE MATERIALIZED VIEW
- SELECT
- SHOW ALTER
- SHOW MATERIALIZED VIEW
- RESUME ROUTINE LOAD
- ALTER ROUTINE LOAD
- SHOW TABLES
- STREAM LOAD
- SHOW PARTITIONS
- CANCEL REFRESH MATERIALIZED VIEW
- SHOW CREATE CATALOG
- SHOW ROUTINE LOAD TASK
- SHOW RESTORE
- CREATE ROUTINE LOAD
- STOP ROUTINE LOAD
- SHOW DATABASES
- BROKER LOAD
- SHOW ROUTINE LOAD
- PAUSE ROUTINE LOAD
- SHOW SNAPSHOT
- SHOW CREATE TABLE
- CANCEL LOAD
- REFRESH MATERIALIZED VIEW
- SHOW REPOSITORIES
- SHOW LOAD
- Administration
- DESCRIBE
- SQL Functions
- Function List
- String Functions
- CONCAT
- HEX
- LOWER
- SPLIT
- LPAD
- SUBSTRING
- PARSE_URL
- INSTR
- REPEAT
- LCASE
- REPLACE
- HEX_DECODE_BINARY
- RPAD
- SPLIT_PART
- STRCMP
- SPACE
- CHARACTER_LENGTH
- URL_ENCODE
- APPEND_TAILING_CHAR_IF_ABSENT
- LTRIM
- HEX_DECODE_STRING
- URL_DECODE
- LEFT
- STARTS_WITH
- CONCAT
- GROUP_CONCAT
- STR_TO_MAP
- STRLEFT
- STRRIGHT
- MONEY_FORMAT
- RIGHT
- SUBSTRING_INDEX
- UCASE
- TRIM
- FIND_IN_SET
- RTRIM
- ASCII
- UPPER
- REVERSE
- LENGTH
- UNHEX
- ENDS_WITH
- CHAR_LENGTH
- NULL_OR_EMPTY
- LOCATE
- CHAR
- Predicate Functions
- Map Functions
- Binary Functions
- Geospatial Functions
- Lambda Expression
- Utility Functions
- Bitmap Functions
- BITMAP_SUBSET_LIMIT
- TO_BITMAP
- BITMAP_AGG
- BITMAP_FROM_STRING
- BITMAP_OR
- BITMAP_REMOVE
- BITMAP_AND
- BITMAP_TO_BASE64
- BITMAP_MIN
- BITMAP_CONTAINS
- SUB_BITMAP
- BITMAP_UNION
- BITMAP_COUNT
- BITMAP_UNION_INT
- BITMAP_XOR
- BITMAP_UNION_COUNT
- BITMAP_HAS_ANY
- BITMAP_INTERSECT
- BITMAP_AND_NOT
- BITMAP_TO_STRING
- BITMAP_HASH
- INTERSECT_COUNT
- BITMAP_EMPTY
- BITMAP_MAX
- BASE64_TO_ARRAY
- BITMAP_TO_ARRAY
- Struct Functions
- Aggregate Functions
- RETENTION
- MI
- MULTI_DISTINCT_SUM
- WINDOW_FUNNEL
- STDDEV_SAMP
- GROUPING_ID
- HLL_HASH
- AVG
- HLL_UNION_AGG
- COUNT
- BITMAP
- HLL_EMPTY
- SUM
- MAX_BY
- PERCENTILE_CONT
- COVAR_POP
- PERCENTILE_APPROX
- HLL_RAW_AGG
- STDDEV
- CORR
- COVAR_SAMP
- MIN_BY
- MAX
- VAR_SAMP
- STD
- HLL_UNION
- APPROX_COUNT_DISTINCT
- MULTI_DISTINCT_COUNT
- VARIANCE
- ANY_VALUE
- COUNT_IF
- GROUPING
- PERCENTILE_DISC
- Array Functions
- ARRAY_CUM_SUM
- ARRAY_MAX
- ARRAY_LENGTH
- ARRAY_REMOVE
- UNNEST
- ARRAY_SLICE
- ALL_MATCH
- ARRAY_CONCAT
- ARRAY_SORT
- ARRAY_POSITION
- ARRAY_DIFFERENCE
- ARRAY_CONTAINS
- ARRAY_JOIN
- ARRAY_INTERSECT
- CARDINALITY
- ARRAY_CONTAINS_ALL
- ARRAYS_OVERLAP
- ARRAY_MIN
- ARRAY_MAP
- ELEMENT_AT
- ARRAY_APPEND
- ARRAY_SORTBY
- ARRAY_TO_BITMAP
- ARRAY_GENERATE
- ARRAY_AVG
- ARRAY_FILTER
- ANY_MATCH
- REVERSE
- ARRAY_AGG
- ARRAY_DISTINCT
- ARRAY_SUM
- Condition Functions
- Math Functions
- Date and Time Functions
- DAYNAME
- MINUTE
- FROM_UNIXTIME
- HOUR
- MONTHNAME
- MONTHS_ADD
- ADD_MONTHS
- DATE_SUB
- PREVIOUS_DAY
- TO_TERA_DATA
- MINUTES_SUB
- WEEKS_ADD
- HOURS_DIFF
- UNIX_TIMESTAMP
- DAY
- DATE_SLICE
- DATE
- CURTIME
- SECONDS_SUB
- MONTH
- WEEK
- TO_DATE
- TIMEDIFF
- MONTHS_DIFF
- STR_TO_JODATIME
- WEEK_ISO
- MICROSECONDS_SUB
- TIME_SLICE
- MAKEDATE
- DATE_TRUNC
- JODATIME
- DAYOFWEEK
- YEARS_SUB
- TIMESTAMP_ADD
- HOURS_SUB
- STR2DATE
- TIMESTAMP
- FROM_DAYS
- WEEK_OF_YEAR
- YEAR
- TIMESTAMP_DIFF
- TO_TERA_TIMESTAMP
- DAYOFMONTH
- DAYOFYEAR
- DATE_FORMAT
- MONTHS_SUB
- NEXT_DAY
- MINUTES_DIFF
- DATA_ADD
- MINUTES_ADD
- CURDATE
- DAY_OF_WEEK_ISO
- CURRENt_TIMESTAMP
- STR_TO_DATE
- LAST_DAY
- WEEKS_SUB
- TO_DAYS
- DATEDIFF
- NOW
- TO_ISO8601
- TIME_TO_SEC
- QUARTER
- SECONDS_DIFF
- UTC_TIMESTAMP
- DATA_DIFF
- SECONDS_ADD
- ADDDATE
- WEEKSDIFF
- CONVERT_TZ
- MICROSECONDS_ADD
- SECOND
- YEARS_DIFF
- YEARS_ADD
- HOURS_ADD
- DAYS_SUB
- DAYS_DIFF
- Cryptographic Functions
- Percentile Functions
- Bit Functions
- JSON Functions
- Hash Functions
- Scalar Functions
- Table Functions
window_funnel
Description
Searches for an event chain in a sliding window and calculates the maximum number of consecutive events in the event chain. This function is commonly used for analyzing conversion rate.
This function works according to the following rules:
It starts the count from the first event in the event chain. If the first event is found, the event counter is set to 1 and the sliding window starts. If the first event is not found, 0 is returned.
In the sliding window, the counter is incremented if the events in the event chain occur in sequence. If the sliding window is exceeded, the event counter is no longer incremented.
If multiple event chains match the specified condition, the longest event chain is returned.
Syntax
BIGINT window_funnel(BIGINT window, DATE|DATETIME time, INT mode, array[cond1, cond2, ..., condN])
Parameters
window
: The length of the sliding window. The supported data type is BIGINT. The unit depends on thetime
parameter. If the data type oftime
is DATE, the unit is days. If the data type oftime
is DATETIME, the unit is seconds.time
: The column containing timestamps. DATE and DATETIME types are supported.mode
: The mode in which the event chain is filtered. The supported data type is INT. Value range: 0, 1, 2.0
is the default value, which indicates common funnel calculation.1
indicates theDEDUPLICATION
mode, that is, the filtered event chain cannot have repeated events. Suppose thearray
parameter is[event_type = 'A', event_type = 'B', event_type = 'C', event_type = 'D']
and the original event chain is "A-B-C-B-D". Event B is repeated and the filtered event chain is "A-B-C".2
indicates theFIXED
mode, that is, the filtered event chain cannot have events that disrupt the specified sequence. Suppose the previousarray
parameter is used and the original event chain is "A-B-D-C". Event D interrupts the sequence and the filtered event chain is "A-B".4
indicates theINCREASE
mode, which means the filtered events must have strictly increasing timestamps. Duplicate timestamp disrupts the event chain.
array
: The defined event chain. It must be an array.
Return value
Returns a value of the BIGINT type.
Examples
Example 1: Calculate the maximum number of consecutive events based on uid
. The sliding window is 1800s and the filtering mode is 0
.
This example uses table action
, in which data is sorted by uid
.
mysql> select * from action;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | Browse | 2020-01-02 11:00:00 |
| 1 | Click | 2020-01-02 11:10:00 |
| 1 | Order | 2020-01-02 11:20:00 |
| 1 | Pay | 2020-01-02 11:30:00 |
| 1 | Browse | 2020-01-02 11:00:00 |
| 2 | Order | 2020-01-02 11:00:00 |
| 2 | Pay | 2020-01-02 11:10:00 |
| 3 | Browse | 2020-01-02 11:20:00 |
| 3 | Click | 2020-01-02 12:00:00 |
| 4 | Browse | 2020-01-02 11:50:00 |
| 4 | Click | 2020-01-02 12:00:00 |
| 5 | Browse | 2020-01-02 11:50:00 |
| 5 | Click | 2020-01-02 12:00:00 |
| 5 | Order | 2020-01-02 11:10:00 |
| 6 | Browse | 2020-01-02 11:50:00 |
| 6 | Click | 2020-01-02 12:00:00 |
| 6 | Order | 2020-01-02 12:10:00 |
+------+------------+---------------------+
17 rows in set (0.01 sec)
Execute the following statement:
select uid,
window_funnel(1800,time,0,[event_type='Browse', event_type='Click',
event_type='Order', event_type='Pay']) AS level
from action
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 4 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
+------+-------+
Description of the results:
The matching event chain for
uid = 1
is "Browse-Click-Order-Pay", and4
is returned. The time of the last "Browse" event (2020-01-02 11:00:00) does not meet the condition and is not counted.The event chain of
uid = 2
does not start from the first event "Browse", and0
is returned.The matching event chain for
uid = 3
is "Browse", and1
is returned. The "Click" event exceeds the 1800s time window and is not counted.The matching event chain for
uid = 4
is "Browse-Click", and2
is returned.The matching event chain for
uid = 5
is "Browse-Click", and2
is returned. The "Order" event (2020-01-02 11:10:00) does not belong to the event chain and is not counted.The matching event chain for
uid = 6
is "Browse-Click-Order", and3
is returned.
Example 2: Calculate the maximum number of consecutive events based on uid
. The sliding window is 1800s, and filtering modes 0
and 1
are used.
This example uses table action1
, in which data is sorted by time
.
mysql> select * from action1 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | Browse | 2020-01-02 11:00:00 |
| 2 | Browse | 2020-01-02 11:00:01 |
| 1 | Click | 2020-01-02 11:10:00 |
| 1 | Order | 2020-01-02 11:29:00 |
| 1 | Click | 2020-01-02 11:29:50 |
| 1 | Pay | 2020-01-02 11:30:00 |
| 1 | Click | 2020-01-02 11:40:00 |
+------+------------+---------------------+
7 rows in set (0.03 sec)
Execute the following statement:
select uid,
window_funnel(1800,time,0,[event_type='Browse',
event_type='Click', event_type='Order', event_type='Pay']) AS level
from action1
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 4 |
| 2 | 1 |
+------+-------+
2 rows in set (0.02 sec)
For uid = 1
, the "Click" event (2020-01-02 11:29:50) is a repeated event but it is still counted because mode 0
is used. Therefore, 4
is returned.
Change mode
to 1
and execute the statement again.
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
2 rows in set (0.05 sec)
The longest event chain filtered after deduplication is "Browse-Click-Order", and 3
is returned.
Example 3: Calculate the maximum number of consecutive events based on uid
. The sliding window is 1900s, and filter modes 0
and 2
are used.
This example uses table action2
, in which data is sorted by time
.
mysql> select * from action2 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | Browse | 2020-01-02 11:00:00 |
| 2 | Browse | 2020-01-02 11:00:01 |
| 1 | Click | 2020-01-02 11:10:00 |
| 1 | Pay | 2020-01-02 11:30:00 |
| 1 | Order | 2020-01-02 11:31:00 |
+------+------------+---------------------+
5 rows in set (0.01 sec)
Execute the following statement:
select uid,
window_funnel(1900,time,0,[event_type='Browse', event_type='Click',
event_type='Order', event_type='Pay']) AS level
from action2
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
2 rows in set (0.02 sec)
3
is returned for uid = 1
because mode 0
is used and the "Pay" event (2020-01-02 11:30:00) does not disrupt the event chain.
Change mode
to 2
and execute the statement again.
select uid,
window_funnel(1900,time,2,[event_type='Browse', event_type='Click',
event_type='Order', event_type='Pay']) AS level
from action2
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 2 |
| 2 | 1 |
+------+-------+
2 rows in set (0.06 sec)
2
is returned because the "Pay" event disrupts the event chain and the event counter stops. The filtered event chain is "Browse-Click".
Example 4: Calculate the maximum number of consecutive events based on uid
. The sliding window is 1900s, and filter modes 0
and 4
are used.
This example uses table action3
, in which data is sorted by time
.
select * from action3 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | Browse | 2020-01-02 11:00:00 |
| 1 | Click | 2020-01-02 11:00:01 |
| 2 | Browse | 2020-01-02 11:00:03 |
| 1 | Order | 2020-01-02 11:00:31 |
| 2 | Click | 2020-01-02 11:00:03 |
| 2 | Order | 2020-01-02 11:01:03 |
+------+------------+---------------------+
3 rows in set (0.02 sec)
Execute the following statement:
select uid,
window_funnel(1900,time,0,[event_type='Browse', event_type='Click',
event_type='Order']) AS level
from action3
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 3 |
+------+-------+
3
is returned for uid = 1
and uid = 2
.
Change mode
to 4
and execute the statement again.
select uid,
window_funnel(1900,time,4,[event_type='Browse', event_type='Click',
event_type='Order']) AS level
from action3
group by uid
order by uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
1 row in set (0.02 sec)
1
is returned for uid = 2
because mode 4
(strictly increasing) is used. "Click" happens at the same second as "BROWSE". Therefore, "Click" and "Order" are not counted.
Keywords
window funnel, funnel, window_funnel