- StarRocks
- Introduction to StarRocks
- Quick Start
- Deployment
- Deployment overview
- Prepare
- Deploy
- Deploy shared-nothing StarRocks
- Deploy and use shared-data StarRocks
- Manage
- Table Design
- Understand StarRocks table design
- Table types
- Data distribution
- Data compression
- Sort keys and prefix indexes
- Data Loading
- Concepts
- Overview of data loading
- Load data from a local file system or a streaming data source using HTTP PUT
- Load data from HDFS
- Load data from cloud storage
- Load data from Apache Kafka®
- Continuously load data from Apache Kafka®
- Load data from Apache Sparkâ„¢
- Load data using INSERT
- Load data using Stream Load transaction interface
- Realtime synchronization from MySQL
- Continuously load data from Apache Flink®
- Change data through loading
- Transform data at loading
- Data Unloading
- Query Data Lakes
- Query Acceleration
- Gather CBO statistics
- Synchronous materialized views
- Asynchronous materialized views
- Colocate Join
- Lateral Join
- Query Cache
- Index
- Computing the Number of Distinct Values
- Sorted streaming aggregate
- Integrations
- Administration
- Management
- Data recovery
- User Privilege and Authentication
- Performance Tuning
- Reference
- SQL Reference
- User Account Management
- Cluster Management
- ADD SQLBLACKLIST
- ADMIN CANCEL REPAIR TABLE
- ADMIN CHECK TABLET
- ADMIN REPAIR TABLE
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER RESOURCE GROUP
- ALTER STORAGE VOLUME
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE FILE
- CREATE RESOURCE GROUP
- CREATE STORAGE VOLUME
- DELETE SQLBLACKLIST
- DESC STORAGE VOLUME
- DROP FILE
- DROP RESOURCE GROUP
- DROP STORAGE VOLUME
- EXPLAIN
- INSTALL PLUGIN
- KILL
- SET
- SET DEFAULT STORAGE VOLUME
- SHOW BACKENDS
- SHOW BROKER
- SHOW COMPUTE NODES
- SHOW FILE
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW PROC
- SHOW PROCESSLIST
- SHOW RESOURCE GROUP
- SHOW SQLBLACKLIST
- SHOW STORAGE VOLUMES
- SHOW TABLE STATUS
- SHOW VARIABLES
- UNINSTALL PLUGIN
- DDL
- ALTER DATABASE
- ALTER MATERIALIZED VIEW
- ALTER TABLE
- ALTER VIEW
- ALTER RESOURCE
- ANALYZE TABLE
- BACKUP
- CANCEL ALTER TABLE
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE ANALYZE
- CREATE DATABASE
- CREATE EXTERNAL CATALOG
- CREATE FUNCTION
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE VIEW
- DROP ANALYZE
- DROP CATALOG
- DROP DATABASE
- DROP FUNCTION
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP STATS
- DROP TABLE
- DROP VIEW
- HLL
- KILL ANALYZE
- RECOVER
- REFRESH EXTERNAL TABLE
- RESTORE
- SET CATALOG
- SHOW ANALYZE JOB
- SHOW ANALYZE STATUS
- SHOW FUNCTION
- SHOW META
- SHOW RESOURCES
- TRUNCATE TABLE
- USE
- DML
- ALTER LOAD
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- CANCEL EXPORT
- CANCEL REFRESH MATERIALIZED VIEW
- CREATE ROUTINE LOAD
- DELETE
- DROP TASK
- EXPORT
- GROUP BY
- INSERT
- PAUSE ROUTINE LOAD
- REFRESH MATERIALIZED VIEW
- RESUME ROUTINE LOAD
- SELECT
- SHOW ALTER TABLE
- SHOW ALTER MATERIALIZED VIEW
- SHOW BACKUP
- SHOW CATALOGS
- SHOW CREATE CATALOG
- SHOW CREATE DATABASE
- SHOW CREATE MATERIALIZED VIEW
- SHOW CREATE TABLE
- SHOW CREATE VIEW
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW MATERIALIZED VIEWS
- SHOW PARTITIONS
- SHOW PROPERTY
- SHOW REPOSITORIES
- SHOW RESTORE
- SHOW ROUTINE LOAD
- SHOW ROUTINE LOAD TASK
- SHOW SNAPSHOT
- SHOW TABLES
- SHOW TABLET
- SHOW TRANSACTION
- SPARK LOAD
- STOP ROUTINE LOAD
- STREAM LOAD
- SUBMIT TASK
- UPDATE
- Auxiliary Commands
- Data Types
- Keywords
- Function Reference
- Function list
- Java UDFs
- Window functions
- Lambda expression
- Aggregate Functions
- any_value
- approx_count_distinct
- array_agg
- avg
- bitmap
- bitmap_agg
- count
- corr
- covar_pop
- covar_samp
- group_concat
- grouping
- grouping_id
- hll_empty
- hll_hash
- hll_raw_agg
- hll_union
- hll_union_agg
- max
- max_by
- min
- min_by
- multi_distinct_sum
- multi_distinct_count
- percentile_approx
- percentile_cont
- percentile_disc
- retention
- stddev
- stddev_samp
- sum
- variance, variance_pop, var_pop
- var_samp
- window_funnel
- Array Functions
- all_match
- any_match
- array_agg
- array_append
- array_avg
- array_concat
- array_contains
- array_contains_all
- array_cum_sum
- array_difference
- array_distinct
- array_filter
- array_generate
- array_intersect
- array_join
- array_length
- array_map
- array_max
- array_min
- array_position
- array_remove
- array_slice
- array_sort
- array_sortby
- array_sum
- arrays_overlap
- array_to_bitmap
- cardinality
- element_at
- reverse
- unnest
- Bit Functions
- Bitmap Functions
- base64_to_bitmap
- bitmap_agg
- bitmap_and
- bitmap_andnot
- bitmap_contains
- bitmap_count
- bitmap_from_string
- bitmap_empty
- bitmap_has_any
- bitmap_hash
- bitmap_intersect
- bitmap_max
- bitmap_min
- bitmap_or
- bitmap_remove
- bitmap_subset_in_range
- bitmap_subset_limit
- bitmap_to_array
- bitmap_to_base64
- bitmap_to_string
- bitmap_union
- bitmap_union_count
- bitmap_union_int
- bitmap_xor
- intersect_count
- sub_bitmap
- to_bitmap
- JSON Functions
- Overview of JSON functions and operators
- JSON operators
- JSON constructor functions
- JSON query and processing functions
- Map Functions
- Binary Functions
- Conditional Functions
- Cryptographic Functions
- Date Functions
- add_months
- adddate
- convert_tz
- current_date
- current_time
- current_timestamp
- date
- date_add
- date_diff
- date_format
- date_slice
- date_sub, subdate
- date_trunc
- datediff
- day
- dayname
- dayofmonth
- dayofweek
- dayofyear
- days_add
- days_diff
- days_sub
- from_days
- from_unixtime
- hour
- hours_add
- hours_diff
- hours_sub
- last_day
- makedate
- microseconds_add
- microseconds_sub
- minute
- minutes_add
- minutes_diff
- minutes_sub
- month
- monthname
- months_add
- months_diff
- months_sub
- next_day
- now
- previous_day
- quarter
- second
- seconds_add
- seconds_diff
- seconds_sub
- str_to_date
- str2date
- time_slice
- time_to_sec
- timediff
- timestamp
- timestampadd
- timestampdiff
- to_date
- to_days
- unix_timestamp
- utc_timestamp
- week
- week_iso
- weekofyear
- weeks_add
- day_of_week_iso
- weeks_diff
- weeks_sub
- year
- years_add
- years_diff
- years_sub
- Geographic Functions
- Math Functions
- String Functions
- append_trailing_char_if_absent
- ascii
- char
- char_length
- character_length
- concat
- concat_ws
- ends_with
- find_in_set
- group_concat
- hex
- hex_decode_binary
- hex_decode_string
- instr
- lcase
- left
- length
- locate
- lower
- lpad
- ltrim
- money_format
- null_or_empty
- parse_url
- repeat
- replace
- reverse
- right
- rpad
- rtrim
- space
- split
- split_part
- starts_with
- strleft
- strright
- str_to_map
- substring
- trim
- ucase
- unhex
- upper
- url_decode
- url_encode
- Pattern Matching Functions
- Percentile Functions
- Scalar Functions
- Struct Functions
- Table Functions
- Utility Functions
- cast function
- hash function
- AUTO_INCREMENT
- Generated columns
- System variables
- User-defined variables
- Error code
- System limits
- AWS IAM policies
- SQL Reference
- FAQ
- Benchmark
- Ecosystem Release Notes
- Developers
- Contribute to StarRocks
- Code Style Guides
- Use the debuginfo file for debugging
- Development Environment
- Trace Tools
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. It is supported from v2.3.
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. This mode is supported since version 2.5.
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