跳到主要内容
跳到主要内容

WINDOW_FUNCTION_WINDOW_FUNNEL

WINDOW FUNCTION WINDOW_FUNNEL

description

在滑动时间窗口中搜索事件链,并计算链中发生的最大事件数。

  • window :滑动时间窗口大小,单位为秒。
  • mode :模式,共有四种模式
    • "default": 默认模式。
    • "deduplication": 当某个事件重复发生时,这个重复发生的事件会阻止后续的处理过程。如,指定事件链为[event1='A', event2='B', event3='C', event4='D'],原始事件链为"A-B-C-B-D"。由于B事件重复,最终的结果事件链为A-B-C,最大长度为3。
    • "fixed": 不允许事件的顺序发生交错,即事件发生的顺序必须和指定的事件链顺序一致。如,指定事件链为[event1='A', event2='B', event3='C', event4='D'],原始事件链为"A-B-D-C",则结果事件链为A-B,最大长度为2
    • "increase": 选中的事件的时间戳必须按照指定事件链严格递增。
  • timestamp_column :指定时间列,类型为DATETIME, 滑动窗口沿着此列工作。
  • eventN :表示事件的布尔表达式。

漏斗分析函数按照如下算法工作:

  • 搜索到满足满足条件的第一个事件,设置事件长度为1,此时开始滑动时间窗口计时。
  • 如果事件在时间窗口内按照指定的顺序发生,时间长度累计增加。如果事件没有按照指定的顺序发生,时间长度不增加。
  • 如果搜索到多个事件链,漏斗分析函数返回最大的长度。
window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)

example

example1: default 模式

使用默认模式,筛选出不同user_id对应的最大连续事件数,时间窗口为5分钟:

CREATE TABLE events(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num" = "1");

INSERT INTO
events
VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);

SELECT
user_id,
window_funnel(
300,
"default",
event_timestamp,
event_name = '登录',
event_name = '访问',
event_name = '下单',
event_name = '付款'
) AS level
FROM
events
GROUP BY
user_id
order BY
user_id;

+---------+-------+
| user_id | level |
+---------+-------+
| 100123 | 3 |
| 100125 | 3 |
| 100126 | 2 |
| 100127 | 2 |
+---------+-------+

对于uesr_id=100123,因为付款事件发生的时间超出了时间窗口,所以匹配到的事件链是登陆-访问-下单

example2: deduplication 模式

使用deduplication模式,筛选出不同user_id对应的最大连续事件数,时间窗口为1小时:

CREATE TABLE events(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num" = "1");

INSERT INTO
events
VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '登录', '2022-05-14 10:03:00', 'HONOR', 3),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);

SELECT
user_id,
window_funnel(
3600,
"deduplication",
event_timestamp,
event_name = '登录',
event_name = '访问',
event_name = '下单',
event_name = '付款'
) AS level
FROM
events
GROUP BY
user_id
order BY
user_id;

+---------+-------+
| user_id | level |
+---------+-------+
| 100123 | 2 |
| 100125 | 3 |
| 100126 | 2 |
| 100127 | 2 |
+---------+-------+

对于uesr_id=100123,匹配到访问事件后,登录事件重复出现,所以匹配到的事件链是登陆-访问

example3: fixed 模式

使用fixed模式,筛选出不同user_id对应的最大连续事件数,时间窗口为1小时:

CREATE TABLE events(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num" = "1");

INSERT INTO
events
VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:03:00', "HONOR", 4),
(100123, '登录2', '2022-05-14 10:04:00', 'HONOR', 3),
(100123, '付款', '2022-05-14 10:10:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);

SELECT
user_id,
window_funnel(
3600,
"fixed",
event_timestamp,
event_name = '登录',
event_name = '访问',
event_name = '下单',
event_name = '付款'
) AS level
FROM
events
GROUP BY
user_id
order BY
user_id;

+---------+-------+
| user_id | level |
+---------+-------+
| 100123 | 3 |
| 100125 | 3 |
| 100126 | 2 |
| 100127 | 2 |
+---------+-------+

对于uesr_id=100123,匹配到下单事件后,事件链被登录2事件打断,所以匹配到的事件链是登陆-访问-下单

example4: increase 模式

使用increase模式,筛选出不同user_id对应的最大连续事件数,时间窗口为1小时:

CREATE TABLE events(
user_id BIGINT,
event_name VARCHAR(64),
event_timestamp datetime,
phone_brand varchar(64),
tab_num int
) distributed by hash(user_id) buckets 3 properties("replication_num" = "1");

INSERT INTO
events
VALUES
(100123, '登录', '2022-05-14 10:01:00', 'HONOR', 1),
(100123, '访问', '2022-05-14 10:02:00', 'HONOR', 2),
(100123, '下单', '2022-05-14 10:04:00', "HONOR", 4),
(100123, '付款', '2022-05-14 10:04:00', 'HONOR', 4),
(100125, '登录', '2022-05-15 11:00:00', 'XIAOMI', 1),
(100125, '访问', '2022-05-15 11:01:00', 'XIAOMI', 2),
(100125, '下单', '2022-05-15 11:02:00', 'XIAOMI', 6),
(100126, '登录', '2022-05-15 12:00:00', 'IPHONE', 1),
(100126, '访问', '2022-05-15 12:01:00', 'HONOR', 2),
(100127, '登录', '2022-05-15 11:30:00', 'VIVO', 1),
(100127, '访问', '2022-05-15 11:31:00', 'VIVO', 5);

SELECT
user_id,
window_funnel(
3600,
"increase",
event_timestamp,
event_name = '登录',
event_name = '访问',
event_name = '下单',
event_name = '付款'
) AS level
FROM
events
GROUP BY
user_id
order BY
user_id;

+---------+-------+
| user_id | level |
+---------+-------+
| 100123 | 3 |
| 100125 | 3 |
| 100126 | 2 |
| 100127 | 2 |
+---------+-------+

对于uesr_id=100123付款事件的时间戳与下单事件的时间戳发生在同一秒,没有递增,所以匹配到的事件链是登陆-访问-下单

keywords

WINDOW,FUNCTION,WINDOW_FUNNEL