Hologres漏鬥和留存函數介紹 - Hologres

漏鬥分析和留存分析是常見的轉化分析方法，被廣泛應用於使用者行為分析和App資料分析的流量分析、產品目標轉化等資料營運與資料分析。本文將為您介紹Hologres支援的漏鬥函數和留存函數，並詳細說明它們的使用限制、功能介紹以及使用樣本。

背景資訊

事件（Event）代表了使用者的某個或一系列有意義的行為，比如遊戲App的下載、註冊、登入等，通過分析使用者的各項行為資料還原使用者真實的使用過程，從而提升產品轉化率，助力業務增長。常見的使用者行為分析包括事件分析、漏鬥分析、留存分析等，其中漏鬥和留存是最常見的使用者行為分析情境：

漏鬥分析：是一種用來分析使用者在指定階段轉化情況的分析模型，可以分析使用者在各個階段的行為轉化率，然後通過轉化率來衡量每一個階段的表現，從而助力產品針對性最佳化體驗，提升轉化率。
留存分析：是一種用來分析使用者參與情況或活躍程度的分析模型，考察初始行為的使用者中有多少使用者會進行後續行為，從而衡量產品對使用者的價值。

Hologres是阿里雲自研的一站式即時數倉，支援多種情境的即時資料多維分析。在使用者行為分析情境上，Hologres提供多種漏鬥和留存函數，快速高效的協助業務進行使用者行為分析，被廣泛應用在互連網、電商、遊戲等行業客戶的使用者分析情境中。

使用限制

僅Hologres V0.9及以上版本支援漏鬥函數（windowFunnel）和留存函數（retention）。
僅Hologres V0.10及以上版本支援range_retention_count和range_retention_sum函數。
僅Hologres V2.1及以上版本支援區間漏鬥函數（range_funnel）。
漏鬥和留存等函數需要有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;

漏鬥函數（windowFunnel）

漏鬥分析是用來分析使用者在指定階段轉化情況的一種分析模型，可以分析使用者在各個階段的行為轉化率，協助管理者或營運等角色通過轉化率來衡量每個階段的轉化情況，從而達到最佳化產品，提升轉化率的目的。

函數說明
漏鬥函數（windowFunnel）可以搜尋滑動時間視窗中的事件列表，並計算條件匹配的事件列表的最大長度。
windowFunnel會從第一個事件開始匹配，依次做最長、有序匹配，返回匹配的最大長度。一旦匹配失敗，結束整個匹配。
假設在視窗足夠大的條件下：
- 條件事件為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。

函數文法

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

參數說明

參數	說明
window	統計的視窗時間大小，即從指定的第一個事件開始的時間，根據視窗時間提取後續的相關事件資料。
mode	模式。支援default和strict兩種模式，預設為default。 default：在同一個視窗期內，從第一個事件開始匹配，盡量匹配多的事件。例如條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回3。 strict：`windowFunnel()`僅對唯一值應用匹配條件，即遇到相同的事件就停止繼續匹配。例如條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回2。
timestamp	事件發生的時間範圍，支援timestamp、int、bigint類型。
cond	事件類型，代表事件的每個步驟。

使用樣本

以前置情境中的GitHub公開資料集分析為例，分析一段時間內，使用者按照固定轉化路徑的漏鬥情況，使用如下SQL進行分析，SQL中的各個條件如下：

統計間隔：30分鐘（即1800秒）
統計時間段：2023-07-28 10:00:00+08至2023-07-31 10:00:00+08
轉化路徑：共3個事件，依次發生的步驟為：CreateEvent>PushEvent>IssuesEvent

--計算每個使用者的漏鬥情況
SELECT
    actor_id,
    windowFunnel (1800, 'default', created_at,  type = 'CreateEvent',type = 'PushEvent',type = 'IssuesEvent') AS level
FROM
    hologres_dataset_github_event.hologres_github_event
WHERE
    created_at >= TIMESTAMP '2023-07-28 10:00:00+08'
    AND created_at < TIMESTAMP '2023-07-31 10:00:00+08'
GROUP BY
    actor_id

部分顯示結果如下，其中：

level=0表示使用者在視窗期內沒有匹配到第一個事件。
level=1表示使用者在視窗期內匹配到第一個事件。
level=2表示使用者在視窗期內匹配到第二個事件。
level=3表示使用者在視窗期內三個事件都匹配到。

 actor_id	| level
----------+------
69438111	| 0
62698183  | 0
139085682	| 1
1765075 	| 1
132836353	| 2
122081842	| 2
89815869	| 3

為了提高結果的閱讀性，您可以通過如下SQL查看每一個步驟分別轉化的使用者數。

WITH level_detail AS (
    SELECT
        level,
        COUNT(1) AS count_user
    FROM (
        SELECT
            actor_id,
            windowFunnel (1800, 'default', created_at, type = 'CreateEvent', type = 'PushEvent',type = 'IssuesEvent') AS level
        FROM
            hologres_dataset_github_event.hologres_github_event
        WHERE
            created_at >= TIMESTAMP '2023-07-28 10:00:00+08'
            AND created_at < TIMESTAMP '2023-07-31 10:00:00+08'
        GROUP BY
            actor_id) AS basic_table
    GROUP BY
        level
    ORDER BY
        level ASC
)
SELECT  CASE level    WHEN 0 THEN 'total'
                      WHEN 1 THEN 'CreateEvent'
                      WHEN 2 THEN 'PushEvent'
                      WHEN 3 THEN 'IssuesEvent'
                      END
        ,SUM(count_user) over ( ORDER BY level DESC )
FROM
    level_detail
GROUP BY
    level,
    count_user
ORDER BY
    level ASC;

結果如下：

  case	    |   sum
------------+------
total	      | 864120
CreateEvent |	275053
PushEvent	  | 120242
IssuesEvent	| 2652

區間漏鬥函數（range_funnel）

業務在做漏鬥分析時，通常會進行分組統計，比如按照天、小時等自訂時間視窗統計。為了更好的滿足業務需求，Hologres從V2.1版本開始，在漏鬥函數（windowFunnel）的基礎上，額外擴充了一個區間漏鬥函數（range_funnel）。

函數說明
- 該函數用於計算時間視窗的分組統計結果及彙總計算總結果，匹配邏輯如下：
  - 如果條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回3。
  - 如果有相同事件如c1，c1，c1，使用者資料為c1，c2，c1，c3，則返回2。
- windowFunnel函數與range_funnel函數的區別如下：
  - windowFunnel函數只能對輸入的事件數目據做一次彙總計算，結果為完整的時間區間。range_funnel函數不僅支援輸出總的彙總計算結果，還能輸出自訂時間視窗的分組統計結果，結果為一個數組。
  - windowFunnel函數不支援提取多個相同事件，而range_funnel函數支援提取多個相同事件。

函數文法

range_funnel(window, event_size, range_begin, range_end, interval, event_ts, event_bits)

參數說明

參數	類型	說明
window	interval	統計的視窗時間大小，從第一個事件開始，往後推移一個視窗的區間內的事件。單位：秒。如果window=0，會根據每個區間的起止位置截斷，如果正好是每天0點，就變成了自然日。
event_size	int	要分析的事件總數。
range_begin	timestamptz/timestamp/date	分析時段的起始時間，以第一個事件開始計算。
range_end	timestamptz/timestamp/date	分析時段的終止時間，以第一個事件開始計算。
interval	interval	分析時段會以interval為粒度切分成多個連續的區間，分別進行漏鬥分析併產出結果。單位：秒。
event_ts	timestamp/timestamptz	事件的發生時間欄位。支援timestamp、timestamptz類型。說明該參數從零點開始計算，因此可能會與真即時間存在一定的時差，通常用於觀察天和周的趨勢。
event_bits	bitmap	事件類型欄位。以int32類型的bitmap表示，從低位至高位按順序依次代表一個事件。因此，最多支援32個事件的漏鬥分析。

返回結果
range_funnel輸出一個int64類型的數組，bigint[]。需要注意數組的結果是一個編碼值，該值按區間展示，由區間的起始時間（56bit）和提取到的事件數目（8bit）兩部分拼接組成，所以輸出結果之後還需要對數組的內容進行解碼，才能得到最終的匹配資料。
使用樣本
以前置情境中的GitHub公開資料集為例，分析一段時間內，使用者按照固定轉化路徑的漏鬥情況，並且按天分組展示。使用如下SQL進行分析，SQL中的各條件如下：
- 統計時間間隔：1小時（3600秒）
- 統計時間段：2023-07-23至2023-07-25號，共計3天
- 轉化路徑：共2個事件，依次發生步驟為：CreateEvent>PushEvent
- 分組時間：1天（86400秒），即按天分組展示每一天的漏鬥情況
- 由於事件類型type欄位的類型為text，而range_funnel函數中事件類型event_bits欄位的類型必須為32位的bitmap，因此使用bit_construct函數將事件類型轉換為bitmap
```
--這是沒有解碼的結果
SELECT
        actor_id,
        range_funnel (3600, 2, '2023-07-23', '2023-07-25', 86400, created_at::timestamp, bits) AS result
    FROM (
        SELECT
            actor_id,
            created_at::timestamp,
            type,
            bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
    FROM
        hologres_dataset_github_event.hologres_github_event where ds >= '2023-07-23' AND ds <='2023-07-25') tt group by actor_id order by actor_id ;
```
部分查詢結果如下：
```
actor_id	| result
----------+------
17	      |{432658022400,432680140800,432702259202,9223372036854775554}
47      	|{432658022402,432680140800,432702259200,9223372036854775554}
235       |{432658022401,432680140800,432702259200,9223372036854775553}
```
其中result欄位說明如下：
- 結果為空白代表該使用者沒有匹配到任何時間。
- 結果中包括總的漏鬥結果，以及按天分組展示的漏鬥結果，是一個沒有解碼的數組資料。

區間漏鬥解碼函數

區間漏鬥函數的結果是經過編碼的，結果可閱讀性不強，需要用SQL進行解碼，為了對結果做更方便的解碼，從Hologres 2.1.6版本開始，支援range_funnel_time和range_funnel_level兩個函數對區間漏鬥函數解碼。

函數說明
- range_funnel_time：對區間漏鬥函數（range_funnel）返回int64結果中的事件時間進行解碼。
- range_funnel_level：對區間漏鬥函數（range_funnel）返回int64結果中的事件等級進行解碼。

函數文法

range_funnel_time(range_funnel())
range_funnel_level(range_funnel())

參數說明
range_funnel()：range_funnel()函數輸出的int64結果。

使用樣本

為了提高結果的閱讀性，使用range_funnel_time和range_funnel_level函數對區間漏鬥函數樣本中的result結果進行解碼。使用如下SQL：

SELECT actor_id,
to_timestamp(range_funnel_time(result)) AS res_time, --時間解碼
range_funnel_level(result) AS res_level --事件level解碼
FROM (
    SELECT actor_id, result, count(1) AS cnt FROM (
        SELECT actor_id, 
        unnest(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 86400, created_at::timestamp, bits)) as result from (
            SELECT actor_id,  created_at::timestamp, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits from hologres_dataset_github_event.hologres_github_event where ds >= '2023-07-23' AND ds <='2023-07-25'
        ) a
        GROUP BY actor_id
    ) a
    GROUP BY actor_id ,result
) a
ORDER BY actor_id ,res_time LIMIT 10000;

部分查詢結果如下所示，由此可知每個使用者每天匹配的level和次數：

actor_id	| res_time	          | res_level	
----------+---------------------+-----------
17      	|2023-07-23 08:00:00	| 0	        
17      	|2023-07-24 08:00:00	| 0        
17      	|2023-07-24 08:00:00  | 2	 
17        |\N	                  | 0
47	      |2023-07-23 08:00:00	| 0
47	      |2023-07-24 08:00:00	| 0
47	      |2023-07-25 08:00:00	| 2
47	      |\N	                  | 2 
235	      |2023-07-23 08:00:00	| 1
235	      |2023-07-24 08:00:00	| 0
235	      |2023-07-25 08:00:00	| 0
235	      |\N	                  | 1

查出每個使用者的分天漏鬥結果後，您也可以根據業務需求做進一步的資料探查。

樣本：查看每天步長匯總以及總的摘要資料，其中下一個level一定包含上一個level。

SELECT res_time, res_level, sum(cnt) over (partition by res_time ORDER BY res_level desc) AS res_cnt  FROM (
    SELECT 
    to_timestamp(range_funnel_time(result)) AS res_time, --時間解碼
    range_funnel_level(result) AS res_level, --事件level解碼
        cnt 
    FROM (
        SELECT result, count(1) AS cnt FROM (
            SELECT actor_id, 
            unnest(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 86400, created_at::timestamp, bits)) AS result FROM (
                SELECT actor_id,  created_at::timestamp, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits FROM hologres_dataset_github_event.hologres_github_event WHERE ds >= '2023-07-23' AND ds <='2023-07-25'
            ) a
            GROUP BY actor_id
        ) a
        GROUP BY result
    ) a
)a 
WHERE res_level > 0
GROUP BY res_time, res_level, cnt ORDER BY res_time, res_level;

查詢結果如下，其中：

結果中\N：代表多天匯總的結果。
cnt欄位包括每個level的摘要資料，下一個level一定包含上一個level的資料。例如res_level為2，cnt為1，代表經歷過步驟1，步驟2的使用者數只有1個。

res_time	          |res_level	|  cnt
--------------------+-----------+------
2023-07-23 08:00:00	|1	        |114455
2023-07-23 08:00:00	|2	        |56155
2023-07-24 08:00:00	|1	        |154616
2023-07-24 08:00:00	|2	        |71200
2023-07-25 08:00:00	|1	        |178662
2023-07-25 08:00:00	|2	        |81779
\N	                |1	        |390262
\N	                |2	        |188125

留存函數（retention）

留存分析用於分析使用者的整體參與程度、活躍程度等，常用來分析一段時間內的使用者保留率，從而輔助業務做進一步的產品決策。

函數說明
該函數將一組條件作為參數，類型為1到32個UInt8類型的參數，用來表示事件是否滿足特定條件。
函數文法
```
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)

range_retention_sum

range_retention_sum(range_retention_count)

參數說明

參數	類型	說明
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可取得每日留存明細。

使用樣本

以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