window_funnel
功能
搜索滑动时间窗口内的事件列表,计算条件匹配的事件链里的最大连续事件数。该函数是一种漏斗函数,是比较常见的转化分析方法,用于分析用户在各阶段行为的转化率。
该函数遵循如下规则:
-
从事件链中的第一个条件开始判 断。如果数据中包含符合条件的事件,则向计数器加 1,并以此事件对应的时间作为滑动窗口的起始时间。如果未能找到符合第一个条件的数据,则返回为 0.
-
在滑动窗口内,如果事件链中的事件按顺序发生,则计数器递增;如果超出了时间窗口,则计数器不再增加。
-
如有多条符合条件的事件链,则输出最长的事件链。
该函数从 2.3 版本开始支持。
语法
BIGINT window_funnel(BIGINT window, DATE|DATETIME time, INT mode, array[cond1, cond2, ..., condN])
参数说明
-
window
:滑动窗口的大小,类型为 BIGINT。单位取决于time
参数,如果time
的取值类型为DATE,窗口单位为天;如果time
的取值类型为 DATETIME,窗口单位为秒。 -
time
:包含时间戳的列。目前支持 DATE 和 DATETIME 类型。 -
mode
:事件链的筛选模式 ,类型为 INT。取值范围:0,1,2,4。- 默认值为
0
,表示执行最一般的漏斗计算。 - 模式为
1
时(bits 设置第 1 位)表示DEDUPLICATION
模式,即筛选出的事件链不能有重复的事件。假设array
参数为[event_type='A', event_type='B', event_type='C', event_type='D']
,原事件链为 "A-B-C-B-D"。由于事件 B 重复,那么筛选出的事件链只能是 "A-B-C"。 - 模式为
2
时(bits 设置第 2 位)表示FIXED
模式,即筛选出的事件链不能有跳跃的事件,假设array
参数如上不变,原事件链为 "A-B-D-C",由于事件 D 跳跃,那么筛选出的事件链只能是 "A-B"。 - 模式为
4
时(bits 设置第3位)表示INCREASE
模式,即筛选出的事件链中,连续事件的时间戳必须严格递增。此模式自 2.5 版本开始支持。
- 默认值为
-
array
:定义的事件链,类型为 ARRAY 。
返回值说明
返回 BIGINT 类型的值,值为滑动窗口内满足条件的最大连续事件数。
示例
示例一:筛选出不同 uid
对应的最大连续事件数,窗口为 1800s,筛选模式为 0
。
假设有表 action
,数据以 uid
排序:
SELECT * FROM action;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | 浏览 | 2020-01-02 11:00:00 |
| 1 | 点击 | 2020-01-02 11:10:00 |
| 1 | 下单 | 2020-01-02 11:20:00 |
| 1 | 支付 | 2020-01-02 11:30:00 |
| 1 | 浏览 | 2020-01-02 11:00:00 |
| 2 | 下单 | 2020-01-02 11:00:00 |
| 2 | 支付 | 2020-01-02 11:10:00 |
| 3 | 浏览 | 2020-01-02 11:20:00 |
| 3 | 点击 | 2020-01-02 12:00:00 |
| 4 | 浏览 | 2020-01-02 11:50:00 |
| 4 | 点击 | 2020-01-02 12:00:00 |
| 5 | 浏览 | 2020-01-02 11:50:00 |
| 5 | 点击 | 2020-01-02 12:00:00 |
| 5 | 下单 | 2020-01-02 11:10:00 |
| 6 | 浏览 | 2020-01-02 11:50:00 |
| 6 | 点击 | 2020-01-02 12:00:00 |
| 6 | 下单 | 2020-01-02 12:10:00 |
+------+------------+---------------------+
17 rows in set (0.01 sec)
执行如下SQL语句计算最大连续事件数:
SELECT uid,
window_funnel(1800,time,0,[event_type='浏览', event_type='点击',
event_type='下单', event_type='支付'])
AS level
FROM action
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 4 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
+------+-------+
可以看到:
-
uid=1 匹配的事件链为“浏览-点击-下单-支付”,输出为 4,因为最后一个浏览事件的时间不符合条件,未计入;
-
uid=2 对应的事件链未从第一个事件“浏览”开始,输出为 0;
-
uid=3 对应的事件链为“浏览”,输出为 1,因为“点击”事件超过 1800s窗口,未计入;
-
uid=4 对应的事件链为“浏览-点击”,输出为 2;
-
uid=5 的事件链为“浏览-点击”,输出为 2,因为下单时间不属于该事件链,未计入;
-
uid=6 事件链为“浏览-点击-下单”,输出为 3。
示例二:筛选出不同 uid
对应的最大连续事件数,窗口为 1800s,分别计算筛选模式为 0
和 1
的结果。
假设有表 action1
,数据以 time
排序:
mysql> select * from action1 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | 浏览 | 2020-01-02 11:00:00 |
| 2 | 浏览 | 2020-01-02 11:00:01 |
| 1 | 点击 | 2020-01-02 11:10:00 |
| 1 | 下单 | 2020-01-02 11:29:00 |
| 1 | 点击 | 2020-01-02 11:29:50 |
| 1 | 支付 | 2020-01-02 11:30:00 |
| 1 | 点击 | 2020-01-02 11:40:00 |
+------+------------+---------------------+
7 rows in set (0.03 sec)
执行如下 SQL 语句计算最大连续事件数:
SELECT uid,
window_funnel(1800,time,0,[event_type='浏览',
event_type='点击', event_type='下单', event_type='支付'])
AS level
FROM action1
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 4 |
| 2 | 1 |
+------+-------+
2 rows in set (0.02 sec)
可以看到,对于 uid=1,即使“点击事件 (2020-01-02 11:29:50) ”已经重复出现,但是依然计入,最终输出 4
,因为使用了模式 0
。
将 mode
改为 1
,进行去重,再次执行 SQL:
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
2 rows in set (0.05 sec)
可以看到输出为 3
,去重后筛选出的最长事件链为“浏览-点击-下单”。
示例三:筛选出 uid
对应的最大连续事件数,窗口为1900s,分别计算筛选模式为 0
和 2
的结果。
假设有表 action2
,数据以 time
排序:
mysql> select * from action2 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | 浏览 | 2020-01-02 11:00:00 |
| 2 | 浏览 | 2020-01-02 11:00:01 |
| 1 | 点击 | 2020-01-02 11:10:00 |
| 1 | 支付 | 2020-01-02 11:30:00 |
| 1 | 下单 | 2020-01-02 11:31:00 |
+------+------------+---------------------+
5 rows in set (0.01 sec)
执行如下 SQL 语句:
SELECT uid,
window_funnel(1900,time,0,[event_type='浏览', event_type='点击',
event_type='下单', event_type='支付'])
AS level
FROM action2
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
2 rows in set (0.02 sec)
可以看到对于 uid=1,输出为 3
,因为使用了模式 0
,所以“支付 (2020-01-02 11:30:00)” 这一跳跃的事件并没有阻断筛选出的事件链。
将 mode
改为 2
,再次执行 SQL:
SELECT uid,
window_funnel(1900,time,2,[event_type='浏览', event_type='点击',
event_type='下单', event_type='支付'])
AS level
FROM action2
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 2 |
| 2 | 1 |
+------+-------+
2 rows in set (0.06 sec)
输出为 2
,因为“支付”事件跳跃,停止计数,此时筛选出的最大事件链是“浏览-点击”。
示例四:筛选出 uid
对应的最大连续事件数,窗口为 1900s,分别计算筛选模式为 0
(时间戳不需要严格递增)和 4
(时间戳需要严格递增)的结果。
假设有表 action3
,数据以 time
排序:
select * from action3 order by time;
+------+------------+---------------------+
| uid | event_type | time |
+------+------------+---------------------+
| 1 | 浏览 | 2020-01-02 11:00:00 |
| 1 | 点击 | 2020-01-02 11:00:01 |
| 2 | 浏览 | 2020-01-02 11:00:03 |
| 1 | 下单 | 2020-01-02 11:00:31 |
| 2 | 点击 | 2020-01-02 11:00:03 |
| 2 | 下单 | 2020-01-02 11:01:03 |
+------+------------+---------------------+
3 rows in set (0.02 sec)
执行如下 SQL 语句:
SELECT uid,
window_funnel(1900,time,0,[event_type='浏览', event_type='点击',
event_type='下单'])
AS level
FROM action3
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 3 |
+------+-------+
对于 uid=1 和 2,输出均为 3。
将 mode
改为 4
,再次执行 SQL:
SELECT uid, window_funnel(1900,time,4,[event_type='浏览', event_type='点击',
event_type='下单'])
AS level
FROM action3
GROUP BY uid
ORDER BY uid;
+------+-------+
| uid | level |
+------+-------+
| 1 | 3 |
| 2 | 1 |
+------+-------+
1 row in set (0.02 sec)
对于 uid=2,输出为 1,因为指定了时间戳严格递增,该用户的“点击”和“浏览”发生在同一秒,因此“浏览”及其后行为均被忽略。
Keywords
漏斗,漏斗函数,转化率,funnel