漏斗和留存函数

windowfunnel

windowfunnel函数用于在滑动的时间窗口中搜索事件列表并计算条件匹配的事件列表的最大长度。GaussDB(DWS)根据用户定义的事件列表，从第一个事件开始匹配，依次做有序最长的匹配，返回匹配的最大长度。一旦匹配失败，结束整个匹配。具体介绍如下：

假设在窗口足够大的条件下：

• 条件事件为c1，c2，c3，而用户数据为c1，c2，c3，c4，最终匹配到c1，c2，c3，函数返回值为3。
• 条件事件为c1，c2，c3，而用户数据为c4，c3，c2，c1，最终匹配到c1，函数返回值为1。
• 条件事件为c1，c2，c3，而用户数据为c4，c3，最终没有匹配到事件，函数返回值为0。

语法

1

windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)

入参说明

• window：bigint类型。滑动的时间窗口的大小，指从第一个事件开始，往后推移的时间窗口大小，单位为秒。
• mode：text类型。目前仅支持default模式，其他模式报错处理。Default模式是指在同一个窗口期内，从第一个事件开始匹配，尽量匹配多的事件。
• timestamp：事件发生的时间范围，支持timestamp without time zone、timestamp with time zone、date、int、bigint类型。
• cond：变长boolean数组。指当前Tuple的数据满足事件的哪个步骤。GaussDB(DWS)只支持1~32个condition，不在此范围报错处理。

返回值

level：int类型。条件匹配的事件列表的最大长度。

retention

retention函数可以将一组条件作为参数，分析事件是否满足该条件。

语法

1

retention(cond1, cond2, ..., cond32);

入参说明

cond：变长boolean数组，最大长度32，用来表示事件是否满足特定条件。GaussDB(DWS)只支持1~32个condition，不在此范围报错处理。

返回值

retention condition：tinyint数组类型。返回结果的表达式，与入参cond长度一致，若第cond1和condi条件满足，则返回值第i个值为1，否则为0。

range_retention_count

描述：记录每个用户的留存情况，该函数返回数组，可以作为range_retention_sum函数的入参。

语法

1

range_retention_count(is_first, is_active, dt, retention_interval, retention_granularity, output_format)

入参说明

• is_first：bool类型，是否符合初始行为，true表示符合， false表示不符合 。
• is_active：bool类型，是否符合留存行为，true表示符合， false表示不符合 。
• dt：date类型，发生行为的日期。
• retention_interval：数组类型，表示留存间隔，最多支持15个留存间隔。例如ARRAY[1,3,5,7,15,30]。
• retention_granularity：text类型，表示留存分析粒度，执行日（day）、周（week）、月（month）三种。
• output_format：text类型，表示输出格式，支持normal（默认）和expand（可取得每日留存明细）两种。

返回值：用户留存情况BIGINT数组。

range_retention_sum

描述：汇总计算所有用户每天(周/月)的留存率。

语法

1

range_retention_sum(range_retention_count_result)

入参说明：range_retention_count函数返回值。

返回值：用户留存统计情况text数组。

示例

创建表funnel_test：

CREATE TABLE IF NOT EXISTS funnel_test
(
    user_id INT ,
    event_type TEXT,
    event_time TIMESTAMP,
    event_timez TIMESTAMP WITH TIME ZONE,
    event_time_int BIGINT
);

插入数据：

INSERT INTO funnel_test VALUES
(1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 10),
(1,'点击商品','2021-01-31 11:10:00', '2021-01-31 11:10:00+07', 20),
(1,'加入购物车','2021-01-31 11:20:00', '2021-01-31 11:20:00+06', 30),
(1,'支付货款','2021-01-31 11:30:00', '2021-01-31 11:30:00+05', 40),
(2,'加入购物车','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 11),
(2,'支付货款','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 12),
(1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+01', 50),
(3,'浏览页面','2021-01-31 11:20:00', '2021-01-31 11:20:00-04', 30),
(3,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 80),
(4,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00-01', 1000),
(4,'支付货款','2021-01-31 12:00:00', '2021-01-31 12:00:00-02', 900),
(4,'加入购物车','2021-01-31 12:00:00', '2021-01-31 12:00:00-03', 1001),
(4,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 1001),
(5,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+08', NULL),
(5,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+08', 776),
(5,'加入购物车','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 999),
(6,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+01', -1),
(6,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+02', -2),
(6,'加入购物车','2021-01-31 12:10:00', '2021-01-31 12:00:00+03', -3);

计算每个用户的漏斗情况。返回结果如下，其中level=0表示用户在窗口期内匹配最大事件深度为0，level=1表示用户在窗口期内匹配最大事件深度为1：

SELECT
    user_id,
  windowFunnel(
    0, 'default', event_timez, 
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
 user_id | level
---------+-------
       1 |     1
       2 |     0
       3 |     1
       4 |     1
       5 |     1
       6 |     1
(6 rows)

计算每个用户的漏斗情况，指定滑动的时间窗口的大小为NULL，返回报错：

SELECT
    user_id,
  windowFunnel(
    NULL, 'default', event_time, 
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
ERROR:  Invalid parameter : window length or mode is null.

计算每个用户的漏斗情况，指定多个条件：

SELECT
    user_id,
  windowFunnel(
    40, 'default', date(event_time),
    true, true, false, true
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
 user_id | level
---------+-------
       1 |     2
       2 |     2
       3 |     2
       4 |     2
       5 |     2
       6 |     2
(6 rows)

分析用户的留存情况：

SELECT
    user_id,
  retention(
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
    ) AS r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id ASC;
 user_id |     r
---------+-----------
       1 | {1,1,1,1}
       2 | {0,0,0,0}
       3 | {1,1,0,0}
       4 | {1,1,1,1}
       5 | {1,1,1,0}
       6 | {1,1,1,0}
(6 rows)

分析用户的留存情况，指定第一个时间为false：

SELECT
    user_id,
  retention(
    false, event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
    ) AS r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id ASC;
 user_id |      r
---------+-------------
       1 | {0,0,0,0,0}
       2 | {0,0,0,0,0}
       3 | {0,0,0,0,0}
       4 | {0,0,0,0,0}
       5 | {0,0,0,0,0}
       6 | {0,0,0,0,0}
(6 rows)

分析用户的留存情况总和：

SELECT sum(r[1]), sum(r[2]), sum(r[3]), sum(r[4])
FROM
(
    SELECT
    retention(event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款') AS r
    FROM funnel_test
    GROUP BY user_id
);
 sum | sum | sum | sum
-----+-----+-----+-----
   5 |   5 |   4 |   2
(1 row)

统计每个用户在1,3,7天后的付费留存率：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT
    user_id,
    range_retention_count(event_type = '浏览页面', event_type = '支付货款', DATE(event_time), ARRAY[1, 3, 7], 'day') as r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id;
 user_id |        r
---------+------------------
       1 | {80135499808768}
       2 | {}
       3 | {80135499808768}
       4 | {80135499808768}
       5 | {80135499808768}
       6 | {80135499808768}
(6 rows)