Fungsi corong dasar (windowFunnel) - Hologres

Fungsi corong dasar windowFunnel digunakan untuk menghitung hasil corong dari peristiwa dalam jendela waktu tertentu. Topik ini menjelaskan cara menggunakan fungsi ini.

Batasan

Hanya Hologres V0.9 dan versi lebih baru yang mendukung fungsi windowFunnel.

Perhatian

Untuk menggunakan fungsi corong, Anda harus mengeksekusi pernyataan berikut sebagai superuser untuk memasang ekstensi:

CREATE extension flow_analysis; --Pasang ekstensi.

Ekstensi dipasang pada tingkat database. Untuk setiap database, Anda hanya perlu memasang ekstensi satu kali.
Secara default, ekstensi dimuat ke skema publik. Ekstensi tidak dapat dimuat ke skema lain. Untuk menggunakan ekstensi di skema lain, Anda harus menentukan nama fungsi dalam format public.function, seperti public.windowFunnel.

Deskripsi skenario

Semua contoh dalam topik ini didasarkan pada dataset peristiwa publik di GitHub.

Deskripsi dataset
Sejumlah besar pengembang mengembangkan proyek open source di GitHub dan menghasilkan banyak peristiwa selama proses pengembangan. GitHub mencatat jenis dan detail setiap peristiwa, pengembang, repositori kode, dan informasi lainnya. GitHub membuka peristiwa publik, seperti peristiwa yang dihasilkan ketika Anda menambahkan item ke favorit atau mengirimkan kode. Untuk informasi lebih lanjut tentang jenis peristiwa, lihat Webhook events and payloads.
Impor dataset
Anda dapat menggunakan Hologres untuk mengimpor dataset github_event ke dalam instance Hologres hanya dengan beberapa klik. Untuk informasi lebih lanjut, lihat Impor dataset publik dengan beberapa klik.

Pernyataan untuk membuat tabel untuk peristiwa publik 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;

Sintaks fungsi

Fungsi windowFunnel dapat digunakan untuk menanyakan peristiwa dalam jendela waktu geser dan menghitung jumlah maksimum peristiwa yang sesuai dengan kondisi yang ditentukan.

Fungsi ini mengembalikan panjang maksimum urutan peristiwa terurut mulai dari peristiwa pertama. Jika pencocokan gagal, fungsi berhenti mencocokkan.

Contoh di mana jendela waktu cukup lama:

Fungsi menentukan peristiwa c1, c2, dan c3, dan data pengguna adalah c1, c2, c3, dan c4. Dalam hal ini, peristiwa c1, c2, dan c3 cocok. Fungsi mengembalikan 3.
Fungsi menentukan peristiwa c1, c2, dan c3, dan data pengguna adalah c4, c3, c2, dan c1. Dalam hal ini, peristiwa c1 cocok. Fungsi mengembalikan 1.
Fungsi menentukan peristiwa c1, c2, dan c3, dan data pengguna adalah c4 dan c3. Dalam hal ini, tidak ada peristiwa yang cocok. Fungsi mengembalikan 0.

Sintaks fungsi windowFunnel:

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

Parameter:

Parameter	Deskripsi
window	Panjang jendela waktu. Fungsi windowFunnel menggunakan waktu saat peristiwa pertama yang cocok terjadi sebagai titik awal jendela waktu dan mengekstrak data peristiwa berikutnya berdasarkan panjang jendela waktu.
mode	Mode. Dua mode didukung: default dan strict. Nilai default: default. default: Fungsi windowFunnel mencocokkan sebanyak mungkin peristiwa mulai dari peristiwa pertama dalam jendela waktu yang ditentukan. Misalnya, jika peristiwa c1, c2, dan c3 ditentukan sebagai kondisi dan data pengguna adalah c1, c2, c1, dan c3, fungsi ini mengembalikan 3. strict: `windowFunnel()` menerapkan kondisi pencocokan hanya pada nilai unik. Fungsi berhenti mencocokkan ketika menemui peristiwa yang sama. Misalnya, jika peristiwa c1, c2, dan c3 ditentukan sebagai kondisi dan data pengguna adalah c1, c2, c1, dan c3, fungsi ini mengembalikan 2.
timestamp	Rentang waktu terjadinya peristiwa. Tipe data TIMESTAMP, INT, dan BIGINT didukung.
cond	Tipe peristiwa, yang menunjukkan setiap tahap peristiwa.

Contoh

Data dalam dataset peristiwa publik di GitHub dalam deskripsi skenario di atas digunakan dalam contoh-contoh. Jika Anda ingin menganalisis data corong pengguna yang mengikuti jalur konversi tetap selama periode waktu tertentu, Anda dapat menentukan kondisi berikut dalam pernyataan SQL:

Interval statistik: 1.800 detik (30 menit)
Periode statistik: dari 10:00:00 pada 28 Januari 2024 hingga 10:00:00 pada 31 Januari 2024 (UTC+8)
Jalur konversi pengguna: CreateEvent > PushEvent > IssuesEvent

--Hitung data corong setiap pengguna.
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;

Berikut adalah bagian dari hasil yang dikembalikan, di mana:

level=0 menunjukkan bahwa peristiwa pertama tidak cocok untuk pengguna dalam jendela waktu.
level=1 menunjukkan bahwa peristiwa pertama cocok untuk pengguna dalam jendela waktu.
level=2 menunjukkan bahwa peristiwa kedua cocok untuk pengguna dalam jendela waktu.
level=3 menunjukkan bahwa semua tiga peristiwa cocok untuk pengguna dalam jendela waktu.

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

Untuk meningkatkan keterbacaan hasil, Anda dapat mengeksekusi pernyataan SQL berikut untuk melihat jumlah pengguna yang dikonversi di setiap tahap:

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;

Hasil berikut dikembalikan:

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