实时数仓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)