跳到主要内容
版本:3.0

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,分别计算筛选模式为 01 的结果。

假设有表 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,分别计算筛选模式为 02 的结果。

假设有表 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