维度分组漏斗函数（finder_group_funnel）_实时数仓 Hologres(Hologres)-阿里云帮助中心

维度分组漏斗函数支持按照不同的维度对结果进行分组展示，并指定事件的关联属性。例如按天分组，按国家、IP分组等，以实现更细粒度的漏斗分析。一个用户只能出现在一个分组中，如果不属于任何一个分组，则会被分配到“unreach”组。

使用限制

仅支持Hologres V2.2.32及以上版本的实例使用该函数。

注意事项

漏斗函数均需要由具备Superuser权限的账号执行以下语句开启Extension。

CREATE extension flow_analysis; --开启Extension

Extension是数据库级别的函数，一个数据库只需开启一次即可。
Extension默认加载到public Schema下，且不支持加载到其他Schema。

finder_group_funnel

指定事件按照选择的维度进行分组，计算漏斗结果。

函数语法

语法

finder_group_funnel(window, start_timestamp, step_interval, step_numbers, num_events, attr_related, group_event_index,time_zone,is_relative_window, server_timestamp, client_timestamp, group_dimension, prop1, prop2, ..., check_event1, check_event2...)

参数说明

参数名	是否必填	描述
window	是	分析统计的窗口大小，单位为毫秒。
start_timestamp	是	分析开始的时间，支持TIMESTAMP和TIMESTAMPTZ类型。
step_interval	是	一个步长的持续时间，指多大粒度内来计算转化分析，单位为秒。
step_numbers	是	需要分析的步长数，例如：`step_interval=86400（1天），step_number=7`表示从start_timestamp开始分析7天内，每天的漏斗的情况。
num_events	是	需要分析的事件数。
attr_related	是	表示事件是否具备关联属性。UINT8类型数字，用二进制表示后，第`i`位为1，表示第`i+1`个事件具备关联属性。通常情况下，attr_realted需要与prop联合使用，当`attr_realted=1`时，设置了多少个1，就需要填写多少个prop表达式。
group_event_index	是	即按照哪个事件进行分组。例如：`group_event_index = 1`表示只要到达第一个事件，就会开始分组。而如果指定`group_event_index = 2`，表示只有到达了第二个事件时才会分组，未到达第二个事件的转化会被统一归入unreach分组。
time_zone	是	TEXT类型，表示输入时间对应的时区，必须采用标准的时区格式，例如`Asia/Shanghai`。仅当`is_relative_window`设置为true时，才会对输出结果产生影响（即按照时区展示结果）。
is_relative_window	是	表示是否为多自然日窗口，默认值为false，若设置为true，对其他参数有以下几个限制： window：参数值必须为86400000的整数倍。 `step_interval`：参数值必须为86400，即一个观察步长是一天。说明自然日是指每天的`00:00:00~23:59:59`，其中第一个自然日是`事件发生时间~23:59:59`，后面的自然日是整天。通常按照自然日作为窗口可以用来观察每天的漏斗情况，以实现业务的进一步精细化运营。
server_timestamp	是	事件发生的服务器时间，支持TIMESTAMP和TIMESTAMPTZ类型，用于函数运行时，计算事件所属的事件槽位/步进。
client_timestamp	是	事件发生的客户端时间，支持TIMESTAMP和TIMESTAMPTZ类型，其类型需要和start_timestamp保持一致，用于函数运行时对数据进行排序。
group_dimension	是	分组的维度选择，如选择`channel_id`字段进行维度分组，按照`channel_id`的值展示分组后的数据。多个维度则使用concat_ws连接，即concat，目前仅支持TEXT类型的字段进行分组。
prop	否	表示事件的关联属性，每个prop类型必须相同，否则无法进行比较。
check_event	是	需要分析的转化事件列表，在window定义的时长范围内，满足条件的事件将会被认为有效事件，参与转化分析。如有3个事件，则输入`EventName = 'E0001', EventName = 'E0002', EventName = 'E0003'`。

返回结果
返回一个编码后的BINARY类型结果，需要使用finder_group_funnel_res函数进行解码。

示例

此处以一个简单示例为您展示finder_group_funnel函数的用法。

准备finder_group_funnel_test表并插入数据。

CREATE TABLE finder_group_funnel_test(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);

INSERT INTO finder_group_funnel_test VALUES 
(1111, '2024-01-02 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-02 00:00:01', '登录', '北京','北京'), 
(1111, '2024-01-02 00:00:02', '付费', '北京','北京'), 
(1111, '2024-01-02 00:00:03', '退出', '北京','北京'), 

(1111, '2024-01-03 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-03 00:00:01', '登录', '北京','北京'), 
(1111, '2024-01-03 00:00:02', '付费', '北京','北京'), 

(1111, '2024-01-04 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-04 00:00:01', '登录', '北京','北京'), 


(2222, '2024-01-02 00:00:00', '注册', '浙江','杭州'), 
(2222, '2024-01-02 00:00:00', '登录', '浙江','杭州'), 
(2222, '2024-01-02 00:00:01', '付费', '浙江','杭州'), 
(2222, '2024-01-02 00:00:03', '付费', '浙江','杭州');

按照province字段进行分组展示，命令如下：

SELECT
    id,
    UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = '注册', event = '登录', event = '付费', event = '退出')) AS result
FROM
    finder_group_funnel_test
GROUP BY
    id;

返回结果如下：result值为编码后的结果，需要使用下述的finder_group_funnel_res函数进行解码，详情请参见finder_group_funnel_res。

  id  | result      
------+-----------------
 2222 | 浙江
 2222 | unreach
 1111 | 北京
 1111 | unreach
(4 rows)

finder_group_funnel_res

对finder_group_funnel函数返回的BINARY结果中的漏斗详情进行解码。

函数语法

语法

finder_group_funnel_res(finder_group_funnel())

参数说明
finder_group_funnel()：指定事件按照选择维度进行分组后计算得到的漏斗结果。详情请参见finder_group_funnel。
返回结果
返回解码后的结果。

示例

本示例基于finder_group_funnel示例的结果进行解码，展示每个用户的详细漏斗情况，命令如下：

SELECT
    id,
    finder_group_funnel_res (result) AS res
FROM (
    SELECT
        id,
        UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = '注册', event = '登录', event = '付费', event = '退出')) AS result
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a;

返回结果如下：

 id  |    res    
------+-----------
 1111 | {4,4,3,2}
 1111 | {0,0,0,0}
 2222 | {3,3,0,0}
 2222 | {0,0,0,0}
(4 rows)

finder_group_funnel_text_group

对finder_group_funnel函数返回的BINARY类型结果中的分组字段进行解码，通常和finder_group_funnel_res函数组合使用。

函数语法

语法

finder_group_funnel_text_group(finder_group_funnel())

参数说明
finder_group_funnel()：指定事件按照选择维度进行分组后计算得到的漏斗结果。详情请参见finder_group_funnel。
返回结果
返回解码后的结果。

示例

本示例基于finder_group_funnel示例的结果进行解码，为您展示每个用户分组后的漏斗结果、最终到达的事件，及每个步长内最终到达的事件。命令如下：

SELECT
    id,
    finder_group_funnel_text_group (result) AS key,
    finder_group_funnel_res (result) AS res
FROM (
    SELECT
        id,
        UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = '注册', event = '登录', event = '付费', event = '退出')) AS result
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a;

返回结果如下：

  id  |   key   |    res    
------+---------+-----------
 2222 | 浙江    | {3,3,0,0}
 2222 | unreach | {0,0,0,0}
 1111 | 北京    | {4,4,3,2}
 1111 | unreach | {0,0,0,0}
(4 rows)

漏斗结果聚合函数（funnel_rep）

用于聚合FINDER_FUNNEL和finder_group_funnel的计算结果，形成漏斗每一层所有用户的汇总结果。

函数语法

语法

funnel_rep(step_number, num_events, funnel_res)

参数说明

参数名	是否必填	描述
step_number	是	UINT类型，包含的时间槽位数量，通常和finder_funnel函数中的`step_numbers`值保持一致。例如：`step_numbers=7`表示观察7个时间槽位。
num_events	是	UINT类型值，表示参与转化的事件总数，通常和finder_funnel函数中`check_event`的事件数量保持一致。
funnel_res	是	表示每一个用户产生的所有转化步骤明细，是finder_funnel的输出结果。

返回结果
返回一个元素类型为字符串的一维数组，格式为{"n1,...,nn","m1,...,mn"}，表示总的漏斗情况（第1-N个事件匹配到的用户数）以及每个步长内的漏斗情况（第1-N个事件匹配到的用户数）。

示例

本示例基于示例中的数据，为您展示在窗口期为3天、步长为3天的情况下，如何计算每个用户到达的事件。命令如下：

--3天的窗口期，一个步长为3天，计算每个用户到达的事件。
SELECT
    funnel_rep (3, 4, funnel_res)
FROM (
    SELECT
        id,
        FINDER_FUNNEL (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 'Asia/Shanghai', FALSE, event_time, event_time, event = '注册', event = '登录', event = '付费', event = '退出') AS funnel_res
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a;

返回结果如下：

                funnel_rep                 
-------------------------------------------
 {"2,2,2,1","2,2,2,1","1,1,1,0","1,1,0,0"}
(1 row)

完整使用示例

场景1：按照多天为窗口分组展示用户的漏斗结果

以省份为维度，依次发生4个事件的情况下，分析3天内的漏斗情况及每天的漏斗情况。

准备数据，命令如下。

CREATE TABLE finder_group_funnel_test_1(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);

INSERT INTO finder_group_funnel_test_1 VALUES 
(1111, '2024-01-02 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-02 00:00:01', '登录', '北京','北京'), 
(1111, '2024-01-02 00:00:02', '付费', '北京','北京'), 
(1111, '2024-01-02 00:00:03', '退出', '北京','北京'), 

(1111, '2024-01-03 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-03 00:00:01', '登录', '北京','北京'), 
(1111, '2024-01-03 00:00:02', '付费', '北京','北京'), 

(1111, '2024-01-04 00:00:00', '注册', '北京','北京'), 
(1111, '2024-01-04 00:00:01', '登录', '北京','北京'), 

(2222, '2024-01-02 00:00:00', '注册', '浙江','杭州'), 
(2222, '2024-01-02 00:00:00', '登录', '浙江','杭州'), 
(2222, '2024-01-02 00:00:01', '付费', '浙江','杭州'), 
(2222, '2024-01-02 00:00:03', '付费', '浙江','杭州'),

(3333, '2024-01-02 00:00:00', '注册', '上海','上海'), 
(3333, '2024-01-02 00:00:00', '登录', '上海','上海'), 
(3333, '2024-01-02 00:00:01', '付费', '上海','上海'), 
(3333, '2024-01-02 00:00:03', '付费', '上海','上海'),
(3333, '2024-01-02 00:00:04', '退出', '上海','上海');

以省份为维度，依次发生4个事件，分析3天内的漏斗情况及每天的漏斗情况。命令如下：

SELECT
    key,
    funnel_rep (3, 4, res) AS ans
FROM (
    SELECT
        id,
        finder_group_funnel_text_group (result) AS key,
        finder_group_funnel_res (result) AS res
    FROM (
        SELECT
            id,
            UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = '注册', event = '登录', event = '付费', event = '退出')) AS result
        FROM
            finder_group_funnel_test_1
        GROUP BY
            id) a) b
GROUP BY
    key;

返回结果如下：

   key   |                    ans                    
---------+-------------------------------------------
 北京    | {"1,1,1,1","1,1,1,1","1,1,1,0","1,1,0,0"}
 unreach | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
 上海    | {"1,1,1,1","1,1,1,1","0,0,0,0","0,0,0,0"}
 浙江    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
(4 rows)

场景2：多个自然日作为窗口，按自然日分组展示漏斗结果

准备数据，命令如下。

CREATE TABLE finder_group_funnel_test_2(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);

INSERT INTO finder_group_funnel_test_2 VALUES 
(1111, '2024-01-02 00:00:02', '注册', '北京','北京'), 
(1111, '2024-01-02 00:00:03', '登录', '北京','北京'), 

(1111, '2024-01-03 00:00:04', '付费', '北京','北京'), 

(1111, '2024-01-05 00:00:01', '退出', '北京','北京'), 


(2222, '2024-01-02 00:00:00', '注册', '浙江','杭州'), 
(2222, '2024-01-02 00:00:00', '登录', '浙江','杭州'), 
(2222, '2024-01-02 00:00:01', '付费', '浙江','杭州'), 
(2222, '2024-01-02 00:00:03', '付费', '浙江','杭州');

以省份为维度，依次发生4个事件，分析3天内的漏斗情况以及每个自然日的漏斗情况。命令如下：

SELECT
    key,
    funnel_rep (3, 4, res) AS ans
FROM (
    SELECT
        id,
        finder_group_funnel_text_group (result) AS key,
        finder_group_funnel_res (result) AS res
    FROM (
        SELECT
            id,
            UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', TRUE, event_time, event_time, province, event = '注册', event = '登录', event = '付费', event = '退出')) AS result
        FROM
            finder_group_funnel_test_2
        GROUP BY
            id) a) b
GROUP BY
    key;

返回结果如下：

   key   |                    ans                    
---------+-------------------------------------------
 unreach | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
 浙江    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
 北京    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
(3 rows)