Hologres:範囲ファネル関数 (range_funnel)

範囲ファネル関数 (range_funnel)

• 構文

  range_funnel(window, event_size, range_begin, range_end, interval, event_ts, event_bits, use_interval_window, mode)

• パラメーター

  パラメーター	タイプ	説明
  window	INTERVAL	時間枠の長さです。 range_funnel 関数は、指定された条件の最初のイベントを時間枠の開始点として使用します。次に、この関数は時間枠の長さに基づいてイベントリストを決定します。単位：秒。 window パラメーターを 0 に設定すると、関数は各期間の開始点と終了点に従って切り捨てを実行します。切り捨て結果が毎日 0:00 の場合、データは暦日でフィルタリングされます。 use_interval_window パラメーターを false に設定すると、元のセマンティクスが使用されます。単位：秒。 use_interval_window パラメーターを true に設定すると、n 期間（現在の期間を含む）がウィンドウに含まれます。 window パラメーターを日単位で設定して、暦日でデータをフィルタリングすることもできます。
  event_size	INT	分析するイベントの総数です。
  range_begin	TIMESTAMPTZ/TIMASTAMP/DATE	最初のイベントから計算された、分析対象期間の開始時刻です。
  range_end	TIMESTAMPTZ/TIMASTAMP/DATE	最初のイベントから計算された、分析対象期間の終了時刻です。
  interval	INTERVAL	間隔の長さです。分析対象期間は、このパラメーターに基づいて複数の連続した間隔に分割されます。ウィンドウファネル分析は、各間隔で実行されて結果が得られます。単位：秒。
  event_ts	TIMASTAMP/TIMESTAMPTZ	イベントが発生した時刻です。 説明 このパラメーターは 00:00 から計算されます。時刻は実際の時刻と異なる場合があります。ほとんどの場合、このパラメーターは日または週ごとの傾向を分析するために使用されます。したがって、特定の時刻は無視できます。
  event_bits	Bitmap	イベントタイプです。値は INT32 タイプのビットマップである必要があります。最下位ビットから最上位ビットまでの各ビットは、特定のイベントを表します。したがって、ウィンドウファネル分析は最大 32 イベントをサポートします。
  use_interval_window	TEXT	オプション。間隔を使用して時間枠を分割するかどうかを指定します。デフォルト値：false。 重要 このパラメーターは、Hologres V2.2.30 以降と Hologres V3.0.17 以降でのみサポートされています。
  mode	TEXT	オプション。 mode パラメーターを「0」に設定すると、同時に発生するイベントから 1 つのイベントのみがランダムに抽出されてコンバージョンとしてカウントされ、他のイベントは破棄されます。これがデフォルト値です。 mode パラメーターを「1」に設定すると、同時に発生する異なるイベントが異なるコンバージョンとしてカウントされます。 重要 mode パラメーターを「1」に設定した場合、同一のイベントはサポートされません。 このパラメーターは、Hologres V2.2.30 以降と Hologres V3.0.17 以降でのみサポートされています。

• 戻り値

  range_funnel 関数は、INT64 タイプの配列を返します。これは BIGINT[] で表されます。配列の結果はエンコードされていることに注意してください。間隔ごとに表示され、間隔の開始時刻と抽出されたイベントの数で構成されます。間隔の開始時刻の長さは 56 ビット、抽出されたイベントの数の長さは 8 ビットです。したがって、結果が得られたら、配列の内容をデコードして最終的な一致データを取得する必要があります。

  range_funnel 関数によって返される結果はエンコードされています。結果をデコードするには、SQL ステートメントを実行する必要があります。 Hologres V2.1.6 以降では、range_funnel 関数によって返された結果をデコードするための range_funnel_time 関数と range_funnel_level 関数が導入されています。

範囲ファネルデコード関数

日別にファネル情報を表示する

シナリオの説明 の GitHub の public イベントデータセットのデータがこの例で使用されています。この例では、指定された期間内にユーザーイベントが指定されたイベントの順序と一致するファネルデータを分析し、日ごとのグループ化結果を表示する方法を示します。ファネル分析に使用される SQL ステートメントでは、次の条件が指定されています。

• 時間枠：1 時間、3,600 秒に相当します。

• 期間：2024-01-29 から 2024-01-29 まで、合計 3 日間

• コンバージョンパス：CreateEvent > PushEvent。

• グループ化間隔：1 日、86,400 秒に相当します。ウィンドウファネル分析結果は日ごとに表示されます。

• type フィールドの値は TEXT タイプですが、range_funnel 関数の event_bits フィールドの値は 32 ビットのビットマップである必要があります。したがって、bit_construct 関数を使用して、type フィールドの値をビットマップに変換する必要があります。

-- 次の SQL ステートメントは、デコードせずに結果を返します。
SELECT
        actor_id,
        range_funnel (3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits) AS result
    FROM (
        SELECT
            actor_id,
            created_at::TIMESTAMP,
            type,
            bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
    FROM
        hologres_dataset_github_event.hologres_github_event WHERE ds >= '2024-01-29' AND ds <='2024-01-31') tt GROUP BY actor_id ORDER BY actor_id ;

次の内容は、返された結果の一部です。

actor_id  | result
----------+------------------------------------------------------------
17	  |{436860518400,436882636800,9223372036854775552}
47        |{436860518400,436882636800,9223372036854775552}
235       |{436860518401,436882636800,9223372036854775553}

result フィールドの説明：

• result フィールドの値が空の場合、ユーザーの動作はどの間隔の条件とも一致しません。

• result フィールドの値にデータが含まれている場合、データは日ごとのファネル全体の結果を含むデコードされていない配列です。

この例では、range_funnel_time 関数と range_funnel_level 関数を使用して、前の例で range_funnel 関数によって返された結果をデコードします。デコード結果はユーザー ID ごとに表示されます。SQL ステートメントの例：

SELECT actor_id,
TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- イベント時間をデコードします。
range_funnel_level(result) AS res_level -- イベントレベルをデコードします。
FROM (
    SELECT actor_id, result, COUNT(1) AS cnt FROM (
        SELECT actor_id, 
        UNNEST(range_funnel (3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result FROM (
            SELECT actor_id,  created_at::TIMESTAMP, type, bit_construct (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits from hologres_dataset_github_event.hologres_github_event where ds >= '2024-01-29' AND ds <='2024-01-31'
        ) a
        GROUP BY actor_id
    ) a
    GROUP BY actor_id ,result
) a
ORDER BY actor_id ,res_time limit 10000;

次の内容は、返された結果の一部です。ここから、1 日ごとのユーザーごとのレベルと一致数を取得できます。

actor_id	| res_time	        | res_level	
----------------+-----------------------+-----------
17      	|2024-01-29 08:00:00+08	| 0	        
17      	|2024-01-30 08:00:00+08	| 0        
75      	|2024-01-29 08:00:00+08 | 2	 
75              |\N	                | 2
76	        |2024-01-29 08:00:00+08	| 0
76	        |2024-01-30 08:00:00+08	| 1
141	        |2024-01-29 08:00:00+08	| 2
141	        |\N	                | 2 
211	        |2024-01-30 08:00:00+08	| 1
235	        |2024-01-30 08:00:00+08	| 0
235	        |\N	                | 1

日ごとの各ユーザーのファネル結果を取得したら、ビジネス要件に基づいてデータをさらに詳しく調べることができます。たとえば、次の SQL ステートメントを実行して、日ごとのステップサイズサマリーと合計サマリーデータを表示できます。次のレベルには、前のレベルのデータが含まれています。

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt FROM (
    SELECT 
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- イベント時間をデコードします。
    range_funnel_level(result) AS res_level, -- イベントレベルをデコードします。
        cnt 
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT actor_id, 
            UNNEST(range_funnel (3600, 2, '2024-01-28', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result FROM (
                SELECT actor_id,  created_at::TIMESTAMP, type, BIT_CONSTRUCT (a := type = 'CreateEvent', b := type = 'PushEvent') AS bits FROM hologres_dataset_github_event.hologres_github_event WHERE ds >= '2024-01-28' AND ds <='2024-01-30'
            ) a
            GROUP BY actor_id
        ) a
        GROUP BY result
    ) a
)a 
WHERE res_level > 0
GROUP BY res_time, res_level, cnt ORDER BY res_time, res_level;

次の内容はクエリ結果を示しています。

• \N は、複数日に集計された結果を示します。

• res_cnt フィールドには、各レベルのサマリーデータが含まれています。次のレベルには、前のレベルのデータが含まれています。たとえば、res_level フィールドの値が 2 で、res_cnt フィールドの値が 1 の場合、ステップ 1 と 2 を実行するのは 1 人のユーザーだけです。

res_time	        |res_level	|res_cnt
------------------------+---------------+------
2024-01-28 08:00:00+08	|1	        |131212
2024-01-28 08:00:00+08	|2	        |62371
2024-01-29 08:00:00+08	|1	        |172505
2024-01-29 08:00:00+08	|2	        |79667
2024-01-30 08:00:00+08	|1	        |198585
2024-01-30 08:00:00+08	|2	        |90291
\N	                |1	        |440332
\N	                |2	        |208942

同時に発生する異なるイベントを異なるコンバージョンとしてカウントする

range_funnel 関数の mode パラメーターを「1」に設定すると、同時に発生する異なるイベントが異なるコンバージョンとしてカウントされます。

次のステートメントを実行して、funnel_test という名前のテーブルを作成し、テーブルにデータを挿入します。

CREATE TABLE funnel_test (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test VALUES 
(11, 'login', '2024-09-26 16:15:28+08'), 
(11, 'watch', '2024-09-26 16:15:28+08'), 
(11, 'buy', '2024-09-26 16:16:28+08'), 
(22, 'login', '2024-09-26 16:15:28+08'), 
(22, 'watch', '2024-09-26 16:16:28+08'),
(22, 'buy', '2024-09-26 16:17:28+08');

次のクエリステートメントを実行します。

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt FROM (
    SELECT 
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- イベント時間をデコードします。
    range_funnel_level(result) AS res_level, -- イベントレベルをデコードします。
        cnt 
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT uid, 
            UNNEST(range_funnel (3600, 3, '2024-09-26', '2024-09-27', 86400, create_time::TIMESTAMP, bits,false,'1')) AS result FROM (
                SELECT uid,  create_time::TIMESTAMP, event, BIT_CONSTRUCT (a := event = 'login', b := event = 'watch',c := event = 'buy') AS bits FROM funnel_test
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a
)a 
GROUP BY res_time, res_level, cnt ORDER BY res_time, res_level;

次の出力は、同時に発生する異なるイベントが異なるコンバージョンとしてカウントされることを示しています。

        res_time        | res_level | res_cnt 
------------------------+-----------+---------
 2024-09-26 08:00:00+08 |         3 |       2
                        |         3 |       2
(2 rows)

時間枠を複数暦日に設定して日別にグループ化する

実際のシナリオでは、間隔をまたいでコンバージョンを分析する必要がある場合があります。この場合、range_funnel 関数の use_interval_window パラメーターを true に設定できます。この例では、時間枠は複数暦日に設定されています。

-- 時間枠を複数暦日に設定して日別にグループ化します。
CREATE TABLE funnel_test_2 (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test_2 VALUES 
(11, 'login', '2024-09-24 16:15:28+08'), 
(11, 'watch', '2024-09-25 16:15:28+08'), 
(11, 'buy', '2024-09-26 16:16:28+08'), 
(22, 'login', '2024-09-24 16:15:28+08'), 
(22, 'watch', '2024-09-25 16:16:28+08'),
(22, 'buy', '2024-09-26 16:17:28+08');

時間枠が複数暦日に設定されている場合のコンバージョン結果を計算します。

-- 時間枠は 3 暦日に設定されています。
    SELECT 
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- イベント時間をデコードします。
    range_funnel_level(result) AS res_level, -- イベントレベルをデコードします。
        cnt 
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT uid, 
            UNNEST(range_funnel (3, 3, '2024-09-24', '2024-09-27', 86400, create_time::TIMESTAMP, bits,true,'1')) AS result FROM (
                SELECT uid,  create_time::TIMESTAMP, event, BIT_CONSTRUCT (a := event = 'login', b := event = 'watch',c := event = 'buy') AS bits FROM funnel_test_2
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a;

次の出力は、日ごとのコンバージョン状況を示しています。

res_time                | res_level | cnt 
------------------------+-----------+-----
 2024-09-26 08:00:00+08 |         0 |   2
                        |         3 |   2
 2024-09-24 08:00:00+08 |         3 |   2
 2024-09-25 08:00:00+08 |         0 |   2
(4 rows)