維度分組漏鬥函數（finder_group_funnel） - 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)