基礎漏鬥函數（windowFunnel） - Hologres

基礎漏鬥函數支援在特定的視窗期內計算事件的漏鬥結果，本文為您介紹基礎漏鬥函數的使用。

使用限制

僅Hologres V0.9及以上版本支援windowFunnel函數。

注意事項

漏鬥函數均需要由具備Superuser許可權的帳號執行以下語句開啟Extension。

CREATE extension flow_analysis; --開啟Extension

Extension是資料庫層級的函數，一個資料庫只需開啟一次即可。
Extension預設載入到public Schema下，且不支援載入到其他Schema。如需在其他Schema下使用該Extension，請在函數名前增加“public.”，例如public. windowFunnel。

前置情境說明

本文中的樣本全部基於GitHub公開事件數目據集。

資料集介紹
大量開發人員在GitHub上進行開源專案的開發工作，並在專案的開發過程中產生海量事件。GitHub會記錄每次事件的類型及詳情、開發人員、代碼倉庫等資訊，並開放其中的公開事件，包括加星標、提交代碼等，具體事件類型請參見Webhook events and payloads。
資料集資料匯入
通過Hologres將github_event資料集一鍵匯入至Hologres執行個體，詳情請參見一鍵匯入公用資料集。

GitHub公開事件的建表語句如下：

BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
    id BIGINT,
    actor_id BIGINT,
    actor_login TEXT,
    repo_id BIGINT,
    repo_name TEXT,
    org_id BIGINT,
    org_login TEXT,
    type TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    action TEXT,
    iss_or_pr_id BIGINT,
    number BIGINT,
    comment_id BIGINT,
    commit_id TEXT,
    member_id BIGINT,
    rev_or_push_or_rel_id BIGINT,
    ref TEXT,
    ref_type TEXT,
    state TEXT,
    author_association TEXT,
    language TEXT,
    merged BOOLEAN,
    merged_at TIMESTAMP WITH TIME ZONE,
    additions BIGINT,
    deletions BIGINT,
    changed_files BIGINT,
    push_size BIGINT,
    push_distinct_size BIGINT,
    hr TEXT,
    month TEXT,
    year TEXT,
    ds TEXT
);

CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');

COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
END;

函數文法

漏鬥函數（windowFunnel）可以搜尋滑動時間視窗中的事件列表，並計算條件匹配的事件列表的最大長度。

windowFunnel會從第一個事件開始匹配，依次做最長、有序匹配，返回匹配的最大長度。一旦匹配失敗，結束整個匹配。

假設在視窗足夠大的條件下：

條件事件為c1，c2，c3，而使用者資料為c1，c2，c3，c4，最終匹配到c1，c2，c3，函數傳回值為3。
條件事件為c1，c2，c3，而使用者資料為c4，c3，c2，c1，最終匹配到c1，函數傳回值為1。
條件事件為c1，c2，c3，而使用者資料為c4，c3，最終沒有匹配到事件，函數傳回值為0。

windowFunnel函數的文法如下：

windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)

參數說明：

參數	說明
window	統計的視窗時間大小，即從指定的第一個事件開始的時間，根據視窗時間提取後續的相關事件資料。
mode	模式。支援default和strict兩種模式，預設為default。 default：在同一個視窗期內，從第一個事件開始匹配，盡量匹配多的事件。例如條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回3。 strict：`windowFunnel()`僅對唯一值應用匹配條件，即遇到相同的事件就停止繼續匹配。例如條件事件為c1，c2，c3，而使用者資料為c1，c2，c1，c3，則返回2。
timestamp	事件發生的時間範圍，支援TIMESTAMP、INT、BIGINT類型。
cond	事件類型，代表事件的每個步驟。

使用樣本

以前置情境中的GitHub公開資料集分析為例，分析一段時間內，使用者按照固定轉化路徑的漏鬥情況，使用如下SQL進行分析，SQL中的各個條件如下：

統計間隔：1800秒（即30分鐘）
統計時間段：2024-01-28 10:00:00+08至2024-01-31 10:00:00+08
轉化路徑：共3個事件，依次發生的步驟為：CreateEvent>PushEvent>IssuesEvent

--計算每個使用者的漏鬥情況
SELECT
    actor_id,
    windowFunnel (1800, 'default', created_at,  type = 'CreateEvent',type = 'PushEvent',type = 'IssuesEvent') AS level
FROM
    hologres_dataset_github_event.hologres_github_event
WHERE
    created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
    AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
GROUP BY
    actor_id;

部分顯示結果如下，其中：

level=0表示使用者在視窗期內沒有匹配到第一個事件。
level=1表示使用者在視窗期內匹配到第一個事件。
level=2表示使用者在視窗期內匹配到第二個事件。
level=3表示使用者在視窗期內三個事件都匹配到。

 actor_id	| level
----------+------
143037332	| 0
38708562        | 0
157624788	| 1
137850795 	| 1
69616418 	| 2
158019532	| 2
727125  	| 3

為了提高結果的閱讀性，您可以通過如下SQL查看每一個步驟分別轉化的使用者數。

WITH level_detail AS (
    SELECT
        level,
        COUNT(1) AS count_user
    FROM (
        SELECT
            actor_id,
            windowFunnel (1800, 'default', created_at, type = 'CreateEvent', type = 'PushEvent',type = 'IssuesEvent') AS level
        FROM
            hologres_dataset_github_event.hologres_github_event
        WHERE
            created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
            AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
        GROUP BY
            actor_id) AS basic_table
    GROUP BY
        level
    ORDER BY
        level ASC
)
SELECT  CASE level    WHEN 0 THEN 'total'
                      WHEN 1 THEN 'CreateEvent'
                      WHEN 2 THEN 'PushEvent'
                      WHEN 3 THEN 'IssuesEvent'
                      END AS type
        ,SUM(count_user) over ( ORDER BY level DESC )
FROM
    level_detail
GROUP BY
    level,
    count_user
ORDER BY
    level ASC;

結果如下：

  type	    |  sum
------------+------
total	    | 1338166
CreateEvent | 461088
PushEvent   | 202221
IssuesEvent | 4727