AI 助理
备案控制台
文档
输入文档关键字查找
首页 实时数仓 Hologres 开发参考 函数参考 流量分析函数 漏斗函数 区间漏斗函数（range_funnel）

区间漏斗函数（range_funnel）

更新时间：
产品详情
我的收藏

区间漏斗函数支持在特定的窗口期内计算事件的漏斗结果，且支持按照时间字段对结果进行分组展示，本文为您介绍区间漏斗函数的使用。

背景信息

业务在做漏斗分析时，通常会进行分组统计，比如按照天、小时等自定义时间窗口统计。为了更好地满足业务需求，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)
上一篇：基础漏斗函数（windowFunnel）下一篇：属性关联漏斗函数（finder_funnel）
文档推荐