使用留存函数实现用户留存分析-实时数仓 Hologres-阿里云

留存分析是一种用来分析用户参与情况或活跃程度的分析模型，可以考察初始行为的用户中有多少用户会进行后续行为，从而衡量产品对用户的价值，被广泛应用在互联网、电商、游戏等行业客户的用户分析场景中。Hologres是阿里云自研的一站式实时数仓，支持多种场景的实时数据多维分析。在用户行为分析场景中，Hologres提供留存函数，助力业务高效地进行用户行为分析。

使用限制

仅Hologres V0.9及以上版本支持retention函数。
仅Hologres V0.10及以上版本支持range_retention_count和range_retention_sum函数。
留存函数均需要由具备Superuser权限的账号执行以下语句开启Extension。
```
CREATE extension flow_analysis; --开启Extension
```
- Extension是数据库级别的函数，一个数据库只需开启一次即可。
- Extension默认加载到public Schema下，且不支持加载到其他Schema。

前置场景说明

本文中的示例全部基于GitHub公开事件数据集。

数据集介绍
大量开发人员在GitHub上进行开源项目的开发工作，并在项目的开发过程中产生海量事件。GitHub会记录每次事件的类型及详情、开发者、代码仓库等信息，并开放其中的公开事件，包括加星标、提交代码等，具体事件类型请参见Webhook events and payloads。
数据集数据导入
通过Hologres将github_event数据集一键导入至Hologres实例，详情请参见一键导入公共数据集。

GitHub公开事件的建表语句如下：

BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
    id bigint,
    actor_id bigint,
    actor_login text,
    repo_id bigint,
    repo_name text,
    org_id bigint,
    org_login text,
    type text,
    created_at timestamp with time zone NOT NULL,
    action text,
    iss_or_pr_id bigint,
    number bigint,
    comment_id bigint,
    commit_id text,
    member_id bigint,
    rev_or_push_or_rel_id bigint,
    ref text,
    ref_type text,
    state text,
    author_association text,
    language text,
    merged boolean,
    merged_at timestamp with time zone,
    additions bigint,
    deletions bigint,
    changed_files bigint,
    push_size bigint,
    push_distinct_size bigint,
    hr text,
    month text,
    year text,
    ds text
);

CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');

COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
ALTER TABLE hologres_dataset_github_event.hologres_github_event OWNER TO test1_developer;
END;

留存函数（retention）

留存分析用于分析用户的整体参与程度、活跃程度等，常用来分析一段时间内的用户保留率，从而辅助业务做进一步的产品决策。

函数语法

留存函数将一组条件作为参数，类型为1到32个UInt 8类型的参数，用来表示这些事件是否满足特定条件。语法如下：

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

参数说明：

参数

说明

cond

返回结果的表达式。返回值包括：

1，条件满足。
0，条件不满足。

使用示例

示例1：使用GitHub公开数据集，分析某一天开始，在PushEvent事件上，用户的留存情况，SQL语句如下：

SELECT
    sum(r[1]) as first_day,
    sum(r[2]) as second_day,
    sum(r[3]) as third_day,
    sum(r[4]) as fourth_day,
    sum(r[5]) as fifth_day,
    sum(r[6]) as sixth_day,
    sum(r[7]) as seventh_day
FROM (
    SELECT
        retention (ds = '2023-07-23' AND type = 'PushEvent',ds = '2023-07-24' AND type = 'PushEvent', ds = '2023-07-25'AND type = 'PushEvent', ds = '2023-07-26' AND type = 'PushEvent', ds = '2023-07-27'AND type = 'PushEvent' , ds = '2023-07-28'AND type = 'PushEvent', ds = '2023-07-29'AND type = 'PushEvent') AS r
    FROM
        hologres_dataset_github_event.hologres_github_event
    GROUP BY
        actor_id) tt;

结果如下：

first_day	|second_day |	third_day |	fourth_day |	fifth_day	|sixth_day	|seventh_day
----------------+-----------+-------------+----------------+--------------------+---------------+-----------
250678	        |105351	    |93219	  |81071	   |81474	        |76264	        |23319

示例2：分析每个用户近7天的留存情况。

--每个用户的留存情况
SELECT
            actor_id,
            retention(
                DATE(created_at) =  DATE(TIMESTAMP '2023-07-23 10:00:00+08') ,
                DATE(created_at) =  DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '1 day'),
                DATE(created_at) =  DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '2 day'),
                DATE(created_at) =  DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day') 
                ) AS r
            FROM    hologres_dataset_github_event.hologres_github_event
            WHERE   (created_at >= TIMESTAMP '2023-07-23 10:00:00+08')
            AND     (created_at <= TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day')
            GROUP BY actor_id

结果如下：

actor_id	| r
----------------+-------
122588631	|{1,1,1,0}
909706	        |{1,0,1,0}
1893117	        |{0,0,0,0}
56136246	|{1,0,1,0}
33266253	|{0,0,0,0}
106332614	|{0,0,0,0}

留存扩展函数

留存分析是最常见的典型用户增长分析场景，用户经常需要绘制数据可视化图形，分析用户的留存情况。基于该场景，Hologres构造了range_retention_count和range_retention_sum两个函数用于服务该场景。

range_retention_count：用于记录每个用户的留存情况，返回值为bigint数组。不支持直接读取，但可以作为range_retention_sum的输入。
range_retention_sum：用于汇总计算所有用户每天的留存率。返回值为text数组。

函数语法

range_retention_count

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

参数说明如下：

参数	类型	说明
is_first	boolean	是否符合初始行为。 true：符合初始行为。 false：不符合初始行为。
is_active	boolean	是否符合后续留存行为。 true：符合后续留存行为。 false：不符合后续留存行为。
dt	date	发生行为日期。如2020-12-12
retention_interval	int[]	留存间隔，最多支持15个留存间隔。如ARRAY[1,3,5,7,15,30]
retention_granularity	text	留存粒度，支持如下三种： day week month
output_format	text	输出格式，支持如下两种： normal（默认） expand 说明 Hologres V1.1.38及以上版本支持此参数。 expand可取得每日留存明细。

range_retention_sum
```
range_retention_sum(range_retention_count)
```
参数说明如下：
range_retention_count：记录每个用户的留存情况，返回结果bigint数组。

使用示例

以GitHub公开数据集为例，分析用户的留存情况。

示例1：计算每个用户的留存情况，SQL语句如下：

SELECT
  actor_id,
  r
FROM
  (
    SELECT
      actor_id,
      range_retention_count(type = 'CreateEvent', type = 'PushEvent', ds::date, array[1, 2],'day','normal') AS r
    FROM
      hologres_dataset_github_event.hologres_github_event
    GROUP BY
      actor_id
  ) AS t
ORDER BY
  actor_id;

部分查询结果如下：

actor_id   | r
-----------+-------
771	   |{84022445211651}
772	   |{84022445211651,84026740178947,84031035146243,84048215015424}
777	   |{}
797	   |{84013855277059,84018150244353,84022445211648,84035330113536}

示例2：计算近7天的留存率，SQL语句如下：

WITH tbl_detail AS (
        SELECT range_retention_count(is_first, is_active, dt, ARRAY[1, 3, 7],'day' ) AS detail
        FROM (
            SELECT actor_id, created_at::DATE AS dt
                , CASE 
                    WHEN 
                    created_at >= timestamp '2023-07-23 00:00:00'
                    AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' DAY
                    AND type = 'CreateEvent'  
                    THEN true
                    ELSE false
                END AS is_first
                , CASE 
                    WHEN created_at >= timestamp '2023-07-23 00:00:00' + INTERVAL '1' DAY
                    AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' DAY + INTERVAL '7' DAY 
                    THEN true
                    ELSE false
                END AS is_active
            FROM hologres_dataset_github_event.hologres_github_event
        ) tbl
        GROUP BY actor_id
    ),
    tbl_sum AS (
        SELECT regexp_split_to_array(unnest(range_retention_sum(detail)), ',') AS s
        FROM tbl_detail
    )
SELECT s[1] AS 访问日期
        ,s[3]::numeric / s[2]::numeric AS 第1天
        ,s[4]::numeric / s[2]::numeric AS 第3天
        ,s[5]::numeric / s[2]::numeric AS 第7天
FROM tbl_sum
ORDER BY s[1];

查询结果如下：

访问日期    |	第1天	    |第3天          |	第7天
------------+---------------+---------------+---------
20230723    |0.3656546240   |0.2603730723   |0.0000000000
20230724    |0.4076098204   |0.3123156723   |0.2030384953
20230725    |0.3930270566   |0.3154280149   |0.0000000000
20230726    |0.4275885111   |0.1000445494   |0.0000000000
20230727    |0.4043748374   |0.0000000000   |0.0000000000
20230728    |0.1463595703   |0.2279064106   |0.0000000000
20230729    |0.0000000000   |0.0000000000   |0.0000000000