基础漏斗函数（windowFunnel）_实时数仓 Hologres(Hologres)-阿里云帮助中心

基础漏斗函数支持在特定的窗口期内计算事件的漏斗结果，本文为您介绍基础漏斗函数的使用。

使用限制

仅Hologres V0.9及以上版本支持windowFunnel函数。

注意事项

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

CREATE extension flow_analysis; --开启Extension

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

前置场景说明

本文中的示例全部基于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