留存函數 - 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。
- 如果已串連到其他Schema，使用留存函數時，需在函數名前增加“public.”首碼

前置情境說明

本文中的樣本全部基於業務與資料認知。

資料集介紹
大量開發人員在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