AI 助理
备案控制台
文档
输入文档关键字查找
首页 实时数仓 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
上一篇：漏斗函数下一篇：明细圈人函数
文档推荐