跳到主要内容
版本：Latest-3.4

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