全部產品
Search

搜尋本產品

    Hologres：區間漏鬥函數（range_funnel）

    文件中心

    Hologres：區間漏鬥函數（range_funnel）

    更新時間：Dec 13, 2024

    區間漏鬥函數支援在特定的視窗期內計算事件的漏鬥結果，且支援按照時間欄位對結果進行分組展示，本文為您介紹區間漏鬥函數的使用。

    背景資訊

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

    • windowFunnel函數只能對輸入的事件數目據做一次彙總計算，結果為完整的時間區間。range_funnel函數不僅支援輸出總的彙總計算結果，還能輸出自訂時間視窗的分組統計結果，結果為一個數組。

    • windowFunnel函數不支援提取多個相同事件，而range_funnel函數支援提取多個相同事件。range_funnel的匹配邏輯如下：

      • 如果條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回3。

      • 如果有相同事件如c1，c1，c1，使用者資料為c1，c2，c1，c3，則返回2。

    使用限制

    僅支援Hologres V2.1及以上版本的執行個體使用該函數。

    注意事項

    漏鬥函數均需要由具備Superuser許可權的帳號執行以下語句開啟Extension。

    CREATE extension flow_analysis; --開啟Extension

    • Extension是資料庫層級的函數，一個資料庫只需開啟一次即可。

    • Extension預設載入到public Schema下，且不支援載入到其他Schema。

    函數文法

    區間漏鬥函數（range_funnel）

    • 函數文法

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

    • 參數說明

      參數

      類型

      說明

      window

      INTERVAL

      統計的視窗時間大小，從第一個事件開始，往後推移一個視窗區間內的事件。單位：秒。

      如果window=0，會根據每個區間的起止位置截斷，如果正好是每天0點，就變成了自然日，但只能是一個自然日。

      • 當use_interval_window=false時，按照原語義執行，單位：秒。

      • 當use_interval_window=true時，表示視窗為n個區間（包含當前區間）。如果是按天作為漏鬥，1、2、3代表多少個自然日。

      event_size

      INT

      要分析的事件總數。

      range_begin

      TIMESTAMPTZ/TIMASTAMP/DATE

      分析時段的起始時間，以第一個事件開始計算。

      range_end

      TIMESTAMPTZ/TIMASTAMP/DATE

      分析時段的終止時間，以第一個事件開始計算。

      interval

      INTERVAL

      分析時段會以interval參數值為粒度切分成多個連續的區間，分別進行漏鬥分析併產出結果。單位：秒。

      event_ts

      TIMASTAMP/TIMESTAMPTZ

      事件的發生時間欄位。

      說明

      該參數從零點開始計算，因此可能會與真即時間存在一定的時差，通常用於觀察天和周的趨勢，具體時間可以忽略。

      event_bits

      Bitmap

      事件類型欄位。以INT32類型的Bitmap表示，從低位至高位按順序依次代表一個事件。因此，最多支援32個事件的漏鬥分析。

      use_interval_window

      TEXT

      選填，是否使用區間分界計算視窗，預設為false。

      重要

      僅Hologres V2.2.30及以上版本、V3.0.17及以上版本支援該參數。

      mode

      TEXT

      選填。

      • mode='0'（預設）：相同時間發生的不同事件只會被隨機提取一個事件作為轉化，其餘事件丟棄。

      • mode='1'：相同時間發生的不同事件都會被作為轉化。

      重要

      • mode='1'時，不支援相同事件。

      • 僅Hologres V2.2.30及以上版本、V3.0.17及以上版本支援該參數。

    • 返回結果

      range_funnel輸出一個INT64類型的數組，BIGINT[]。需要注意數組的結果是一個編碼值，該值按區間展示，由區間的起始時間（56bit）和提取到的事件數目（8bit）兩部分拼接組成，所以輸出結果之後還需要對數組的內容進行解碼，才能得到最終的匹配資料。

      同時，區間漏鬥函數的結果是經過編碼的，結果的可閱讀性不強，因此需要使用SQL進行解碼。為了更方便地對結果進行解碼，Hologres從V2.1.6版本開始，支援使用range_funnel_TIME和range_funnel_LEVEL兩個函數對區間漏鬥函數進行解碼。

    區間漏鬥解碼函數

    range_funnel_time

    對區間漏鬥函數（range_funnel）返回INT64結果中的事件時間進行解碼。

    • 函數文法

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

    • 參數說明

      range_funnel()：range_funnel函數輸出的INT64類型的結果。

    • 返回結果

      解碼後的事件時間，類型為TIMESTAMPTZ。

    函數名

    描述

    輸入參數

    輸出參數

    range_funnel_time

    對區間漏鬥函數（range_funnel）返回INT64結果中的事件時間進行解碼。

    range_funnel函數輸出的INT64結果。

    事件時間，類型為TIMESTAMPTZ。

    range_funnel_level

    對區間漏鬥函數（range_funnel）返回INT64結果中的事件等級進行解碼。

    range_funnel函數輸出的INT64結果。

    事件等級，類型為BIGINT。

    range_funnel_level

    對區間漏鬥函數（range_funnel）返回INT64結果中的事件等級進行解碼。

    • 函數文法

      range_funnel_level(range_funnel())

    • 參數說明

      range_funnel()：range_funnel函數輸出的INT64類型的結果。

    • 返回結果

      解碼後的事件等級，類型為BIGINT。

    使用樣本

    按天展示每天漏鬥情況

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

    • 統計時間間隔：1小時（3600秒）。

    • 統計時間段：2024-01-29至2024-01-29號，共計3天。

    • 轉化路徑：共2個事件，依次發生步驟為：CreateEvent>PushEvent。

    • 分組時間：1天（86400秒），即按天分組展示每一天的漏鬥情況。

    • 由於事件類型type欄位的類型為TEXT，而range_funnel函數中事件類型event_bits欄位的類型必須為32位的Bitmap，因此使用bit_construct函數將事件類型轉換為Bitmap。

    --這是沒有解碼的結果
SELECT
        actor_id,
        range_funnel (3600, 2, '2024-01-29', '2024-01-31', 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 >= '2024-01-29' AND ds <='2024-01-31') tt GROUP BY actor_id ORDER BY actor_id ;

    部分查詢結果如下：

    actor_id  | result
----------+------------------------------------------------------------
17	  |{436860518400,436882636800,9223372036854775552}
47        |{436860518400,436882636800,9223372036854775552}
235       |{436860518401,436882636800,9223372036854775553}

    其中result欄位說明如下：

    • 結果為空白代表該使用者沒有匹配到任何時間。

    • 結果中包括總的漏鬥結果，以及按天分組展示的漏鬥結果，是一個沒有解碼的數組資料。

    為了使結果更具有閱讀性，我們使用range_funnel_time和range_funnel_level兩個函數對result的結果進行解碼，同時按照使用者ID展開。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, '2024-01-29', '2024-01-31', 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 >= '2024-01-29' AND ds <='2024-01-31'
        ) 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      	|2024-01-29 08:00:00+08	| 0	        
17      	|2024-01-30 08:00:00+08	| 0        
75      	|2024-01-29 08:00:00+08 | 2	 
75              |\N	                | 2
76	        |2024-01-29 08:00:00+08	| 0
76	        |2024-01-30 08:00:00+08	| 1
141	        |2024-01-29 08:00:00+08	| 2
141	        |\N	                | 2 
211	        |2024-01-30 08:00:00+08	| 1
235	        |2024-01-30 08:00:00+08	| 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, '2024-01-28', '2024-01-31', 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 >= '2024-01-28' AND ds <='2024-01-30'
            ) 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：代表多天匯總的結果。

    • res_cnt欄位的內容代表每個level的摘要資料，下一個level一定包含上一個level的資料，例如res_level為2，res_cnt為1，代表經歷過步驟1、步驟2的使用者數只有1個。

    res_time	        |res_level	|res_cnt
------------------------+---------------+------
2024-01-28 08:00:00+08	|1	        |131212
2024-01-28 08:00:00+08	|2	        |62371
2024-01-29 08:00:00+08	|1	        |172505
2024-01-29 08:00:00+08	|2	        |79667
2024-01-30 08:00:00+08	|1	        |198585
2024-01-30 08:00:00+08	|2	        |90291
\N	                |1	        |440332
\N	                |2	        |208942

    相同時間發生不同事件作為轉化

    當range_funnel函數的輸入參數mode='1'時，可以將相同時間發生的不同事件都作為一個轉化。樣本如下：

    建立funnel_test表並插入資料：

    CREATE TABLE funnel_test (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test VALUES 
(11, 'login', '2024-09-26 16:15:28+08'), 
(11, 'watch', '2024-09-26 16:15:28+08'), 
(11, 'buy', '2024-09-26 16:16:28+08'), 
(22, 'login', '2024-09-26 16:15:28+08'), 
(22, 'watch', '2024-09-26 16:16:28+08'),
(22, 'buy', '2024-09-26 16:17:28+08');

    執行如下查詢：

    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 uid, 
            UNNEST(range_funnel (3600, 3, '2024-09-26', '2024-09-27', 86400, create_time::TIMESTAMP, bits,false,'1')) AS result FROM (
                SELECT uid,  create_time::TIMESTAMP, event, BIT_CONSTRUCT (a := event = 'login', b := event = 'watch',c := event = 'buy') AS bits FROM funnel_test
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a
)a 
GROUP BY res_time, res_level, cnt ORDER BY res_time, res_level;

    輸出結果如下，可以看到相同時間發生的不同事件都被作為了轉化。

            res_time        | res_level | res_cnt 
------------------------+-----------+---------
 2024-09-26 08:00:00+08 |         3 |       2
                        |         3 |       2
(2 rows)

    按天分組統計，視窗期是多個自然日

    在實際情境中，可能需要價區間分析轉化的情況，Hologres的range_funnel函數通過輸入參數use_interval_window=true可以實現區間分界計算漏鬥。樣本：多個自然日作為漏鬥視窗計算轉化情況。

    --按天分組統計，漏鬥視窗支援多個自然日
CREATE TABLE funnel_test_2 (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test_2 VALUES 
(11, 'login', '2024-09-24 16:15:28+08'), 
(11, 'watch', '2024-09-25 16:15:28+08'), 
(11, 'buy', '2024-09-26 16:16:28+08'), 
(22, 'login', '2024-09-24 16:15:28+08'), 
(22, 'watch', '2024-09-25 16:16:28+08'),
(22, 'buy', '2024-09-26 16:17:28+08');

    多個自然日作為漏鬥視窗轉化：

    --多個自然日作為視窗，3天作為一個視窗
    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 uid, 
            UNNEST(range_funnel (3, 3, '2024-09-24', '2024-09-27', 86400, create_time::TIMESTAMP, bits,true,'1')) AS result FROM (
                SELECT uid,  create_time::TIMESTAMP, event, BIT_CONSTRUCT (a := event = 'login', b := event = 'watch',c := event = 'buy') AS bits FROM funnel_test_2
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a;

    輸出結果如下，可以看到每天的轉化情況：

    res_time                | res_level | cnt 
------------------------+-----------+-----
 2024-09-26 08:00:00+08 |         0 |   2
                        |         3 |   2
 2024-09-24 08:00:00+08 |         3 |   2
 2024-09-25 08:00:00+08 |         0 |   2
(4 rows)