文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(9.1.0.x)/ 函数和操作符/ 漏斗函数和留存函数
更新时间：2025-01-08 GMT+08:00
查看PDF
分享

漏斗函数和留存函数

漏斗和留存相关函数仅8.3.0及以上集群版本支持。

背景信息

漏斗函数与留存函数都是常见的用户行为分析工具，广泛应用于产品分析和数据分析领域，尤其是在产品经理、数据科学家和市场营销人员的工作中。这些函数的目标是帮助分析用户的行为路径、理解用户的流失和转化问题，以及评估产品的长期吸引力和用户忠诚度。

  • 漏斗分析：一种用于分析用户在执行某一目标过程中所经历的各个步骤的分析方法。通常，漏斗分析可以帮助产品团队或营销人员了解用户在使用产品或服务时的行为轨迹，以及每个步骤中的转化率和流失情况。漏斗分析的关键在于定义一个“漏斗”，即用户从进入某个流程（如访问网站、注册账户、选购商品等）到完成最终目标（如完成支付、完成注册等）的整个路径。
  • 留存分析：留存分析关注的是在某一特定时间段后，仍然活跃使用产品的用户比例。具体来说，留存分析计算的是从某一时间点（例如，用户注册、首次下单、首次使用等）开始，经过一段时间后，仍然继续使用产品的用户数量。常见的留存分析函数通常涉及的是“日留存率”、“周留存率”、“月留存率”等，它们帮助分析用户在首次接触后是否会继续使用该产品。

漏斗函数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。

留存扩展函数

GaussDB(DWS)留存扩展函数支持：range_retention_count和range_retention_sum函数。

  • 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,'Browse','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 10),
(1,'Click','2021-01-31 11:10:00', '2021-01-31 11:10:00+07', 20),
(1,'Order','2021-01-31 11:20:00', '2021-01-31 11:20:00+06', 30),
(1,'Pay','2021-01-31 11:30:00', '2021-01-31 11:30:00+05', 40),
(2,'Order','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 11),
(2,'Pay','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 12),
(1,'Browse','2021-01-31 11:00:00', '2021-01-31 11:00:00+01', 50),
(3,'Browse','2021-01-31 11:20:00', '2021-01-31 11:20:00-04', 30),
(3,'Click','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 80),
(4,'Browse','2021-01-31 11:50:00', '2021-01-31 11:50:00-01', 1000),
(4,'Pay','2021-01-31 12:00:00', '2021-01-31 12:00:00-02', 900),
(4,'Order','2021-01-31 12:00:00', '2021-01-31 12:00:00-03', 1001),
(4,'Click','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 1001),
(5,'Browse','2021-01-31 11:50:00', '2021-01-31 11:50:00+08', NULL),
(5,'Click','2021-01-31 12:00:00', '2021-01-31 12:00:00+08', 776),
(5,'Order','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 999),
(6,'Browse','2021-01-31 11:50:00', '2021-01-31 11:50:00+01', -1),
(6,'Click','2021-01-31 12:00:00', '2021-01-31 12:00:00+02', -2),
(6,'Order','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 = 'Browse', event_type = 'Click', event_type = 'Order', event_type = 'Pay'
  ) 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 = 'Browse', event_type = 'Click', event_type = 'Order', event_type = 'Pay'
  ) 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 = 'Browse', event_type = 'Click', event_type = 'Order', event_type = 'Pay'
    ) 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 = 'Browse', event_type = 'Click', event_type = 'Order', event_type = 'Pay'
    ) 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 = 'Browse', event_type = 'Click', event_type = 'Order', event_type = 'Pay') AS r
    FROM funnel_test
    GROUP BY user_id
);
 sum | sum | sum | sum
-----+-----+-----+-----
   5 |   5 |   4 |   2
(1 row)

创建表retention_test：

CREATE TABLE retention_test(
uid INT,
event TEXT,
event_time TIMESTAMP
);

插入数据：

1
2
3
4
5
6
7
8
9
 
INSERT INTO retention_test VALUES
(1, 'pay', '2024-05-01'),
(1, 'login', '2024-05-01'),
(1, 'pay', '2024-05-02'),
(1, 'login', '2024-05-02'),
(2, 'login', '2024-05-01'),
(3, 'login', '2024-05-02'),
(3, 'pay', '2024-05-03'),
(3, 'pay', '2024-05-04');

统计每个用户在1，2天后的付费留存率：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
WITH retention_count_info AS (
    SELECT
        uid,
        range_retention_count(event = 'login', event = 'pay', 
                               DATE(event_time), array[1, 2], 'day') AS info
    FROM
        retention_test
    GROUP BY
        uid
), retention_sum AS (
         SELECT regexp_split_to_array(unnest(range_retention_sum(info)), ',') AS s
         FROM retention_count_info
     ) SELECT to_date(s[1]::int) AS login_date,
             s[3]::numeric / s[2]::numeric AS retention_rate1,
             s[4]::numeric / s[2]::numeric AS retention_rate2
FROM retention_sum
ORDER BY login_date;
     login_date      |    retention_rate1    |    retention_rate2
---------------------+-----------------------+------------------------
 2024-05-01 00:00:00 | .50000000000000000000 | 0.00000000000000000000
 2024-05-02 00:00:00 | .50000000000000000000 |  .50000000000000000000
(2 rows)
父主题： 函数和操作符

相关文档