基本ファネル関数 (windowFunnel) - Hologres - Alibaba Cloud ドキュメントセンター

基本ファネル関数 windowFunnel は、特定のタイムウィンドウ内のイベントのファネル結果を計算するために使用できます。このトピックでは、この関数の使用方法について説明します。

制限事項

Hologres V0.9 以降のみが windowFunnel 関数をサポートしています。

注意事項

ファネル関数を使用するには、スーパーユーザーとして次のステートメントを実行して拡張機能をインストールする必要があります。

CREATE extension flow_analysis; --拡張機能をインストールします。

拡張機能はデータベースレベルでインストールされます。各データベースについて、拡張機能を一度だけインストールする必要があります。
デフォルトでは、拡張機能は public スキーマにロードされます。拡張機能を他のスキーマにロードすることはできません。他のスキーマで拡張機能を使用するには、public.windowFunnel など、public.function 形式で関数名を指定する必要があります。

シナリオの説明

このトピックのすべての例は、GitHub の公開イベントデータセットに基づいています。

データセットの説明
多数の開発者が GitHub でオープンソースプロジェクトを開発し、開発プロセス中に多くのイベントを生成します。 GitHub は、各イベントの種別と詳細、開発者、コードリポジトリ、その他の情報を記録します。お気に入りにアイテムを追加したり、コードを送信したりするときに生成されるイベントなど、公開イベントを開きます。イベントタイプの詳細については、「Webhook イベントとペイロード」をご参照ください。
データセットのインポート
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 関数を使用すると、スライド式タイムウィンドウ内のイベントをクエリし、指定された条件に一致するイベントの最大数を計算できます。

この関数は、最初のイベントから始まる順序付けられたイベントシーケンスの最大長を返します。一致に失敗した場合、関数は一致を停止します。

タイムウィンドウが十分に長い例:

関数はイベント 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	タイムウィンドウの長さ。 windowFunnel 関数は、最初に一致したイベントが発生した時刻をタイムウィンドウの開始点として使用し、タイムウィンドウの長さに基づいて後続のイベントのデータを抽出します。
mode	モード。 default と strict の 2 つのモードがサポートされています。デフォルト値: default。 default: windowFunnel 関数は、指定されたタイムウィンドウ内の最初のイベントから始めて、できるだけ多くのイベントを一致させます。たとえば、イベント 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 ステートメントで次の条件を指定できます。

統計間隔: 1,800 秒 (30 分)
統計期間: 2024 年 1 月 28 日 10:00:00 から 2024 年 1 月 31 日 10:00:00 (UTC + 08:00)
ユーザー変換パス: 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 は、タイムウィンドウ内でユーザーの 2 番目のイベントが一致することを示します。
level=3 は、タイムウィンドウ内でユーザーの 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