Hologres：漏鬥函數

按天展示每天漏鬥情況

以前置情境中的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欄位說明如下：

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

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

為了使結果更具有閱讀性，我們使用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, '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

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

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

CREATE extension flow_analysis;

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)