Hologres:Fungsi corong rentang (range_funnel)

Fungsi corong rentang (range_funnel)

• Sintaksis

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

• Parameter

  Parameter	Tipe	Deskripsi
  window	INTERVAL	Panjang jendela waktu. Fungsi range_funnel menggunakan peristiwa pertama dalam kondisi yang ditentukan sebagai titik awal jendela waktu. Kemudian, fungsi ini menentukan daftar peristiwa berdasarkan panjang jendela waktu. Unit: detik. Jika Anda mengatur parameter window ke 0, fungsi melakukan pemotongan sesuai dengan titik awal dan titik akhir setiap periode waktu. Jika hasil pemotongan adalah 0:00 setiap hari, data difilter berdasarkan hari kalender. Jika Anda mengatur parameter use_interval_window ke false, semantik asli digunakan. Unit: detik. Jika Anda mengatur parameter use_interval_window ke true, n periode waktu (termasuk periode waktu saat ini) disertakan dalam jendela. Anda juga dapat mengatur parameter window dalam unit hari untuk menyaring data berdasarkan hari kalender.
  event_size	INT	Jumlah total peristiwa yang ingin Anda analisis.
  range_begin	TIMESTAMPTZ/TIMASTAMP/DATE	Waktu mulai periode waktu untuk analisis, dihitung dari peristiwa pertama.
  range_end	TIMESTAMPTZ/TIMASTAMP/DATE	Waktu akhir periode waktu untuk analisis, dihitung dari peristiwa pertama.
  interval	INTERVAL	Panjang interval. Periode waktu untuk analisis dibagi menjadi beberapa interval berturut-turut berdasarkan parameter ini. Analisis corong jendela dilakukan pada setiap interval untuk mendapatkan hasil. Unit: detik.
  event_ts	TIMASTAMP/TIMESTAMPTZ	Waktu terjadinya suatu peristiwa. null Parameter ini dihitung dari 00:00. Waktu mungkin berbeda dari waktu sebenarnya. Dalam banyak kasus, parameter ini digunakan untuk menganalisis tren harian atau mingguan. Oleh karena itu, waktu spesifik dapat diabaikan.
  event_bits	Bitmap	Tipe peristiwa. Nilainya harus berupa bitmap tipe INT32. Setiap bit dari bit paling tidak signifikan hingga bit paling signifikan mewakili peristiwa tertentu. Oleh karena itu, analisis corong jendela mendukung hingga 32 peristiwa.
  use_interval_window	TEXT	Opsional. Menentukan apakah akan menggunakan interval untuk membagi jendela waktu. Nilai default: false. null Hanya Hologres V2.2.30 atau lebih baru dan Hologres V3.0.17 atau lebih baru yang mendukung parameter ini.
  mode	TEXT	Opsional. Jika Anda mengatur parameter mode ke '0', hanya satu peristiwa yang diekstraksi secara acak dari peristiwa yang terjadi pada waktu yang sama dan dihitung sebagai konversi, dan peristiwa lainnya dibuang. Ini adalah nilai default. Jika Anda mengatur parameter mode ke '1', peristiwa berbeda yang terjadi pada waktu yang sama dihitung sebagai konversi berbeda. null Jika Anda mengatur parameter mode ke '1', peristiwa identik tidak didukung. Hanya Hologres V2.2.30 atau lebih baru dan Hologres V3.0.17 atau lebih baru yang mendukung parameter ini.

• Hasil yang Dikembalikan

  Fungsi range_funnel mengembalikan array tipe INT64, dilambangkan sebagai BIGINT[]. Hasil array tersebut dikodekan. Data ditampilkan berdasarkan interval, terdiri dari waktu mulai interval (56 bit) dan jumlah peristiwa yang diekstraksi (8 bit). Setelah hasil diperoleh, isi array harus didekode untuk mendapatkan data akhir.

  Hasil yang dikembalikan oleh fungsi range_funnel dikodekan. Anda perlu mengeksekusi pernyataan SQL untuk mendekode hasil tersebut. Hologres V2.1.6 dan versi setelahnya memperkenalkan fungsi range_funnel_time dan range_funnel_level untuk mendekode hasil yang dikembalikan oleh fungsi range_funnel.

Fungsi dekode corong rentang

Tampilkan informasi corong berdasarkan hari

Data dalam dataset peristiwa publik GitHub yang dijelaskan pada bagian Deskripsi skenario digunakan dalam contoh ini. Contoh ini menunjukkan cara menganalisis data corong, di mana peristiwa pengguna sesuai dengan urutan peristiwa tertentu dalam periode waktu tertentu, serta melihat hasil pengelompokan berdasarkan hari. Kondisi berikut ditetapkan dalam pernyataan SQL untuk analisis corong:

• Jendela waktu: 1 jam, yaitu 3.600 detik.

• Periode waktu: 2024-01-29 hingga 2024-01-29, total 3 hari.

• Jalur konversi: CreateEvent > PushEvent.

• Interval pengelompokan: 1 hari, yaitu 86.400 detik. Hasil analisis corong jendela ditampilkan berdasarkan hari.

• Nilai bidang type adalah tipe TEXT, sedangkan nilai bidang event_bits dalam fungsi range_funnel harus berupa bitmap 32-bit. Oleh karena itu, gunakan fungsi bit_construct untuk mengonversi nilai bidang type menjadi bitmap.

-- Pernyataan SQL berikut mengembalikan hasil tanpa dekode.
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 ;

Berikut adalah bagian dari hasil yang dikembalikan:

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

Penjelasan bidang hasil:

• Jika nilai bidang hasil kosong, perilaku pengguna tidak cocok dengan kondisi dalam interval apa pun.

• Jika nilai bidang hasil berisi data, data tersebut adalah array yang belum didekode, mencakup hasil corong keseluruhan berdasarkan hari.

Dalam contoh ini, fungsi range_funnel_time dan range_funnel_level digunakan untuk mendekode hasil yang dikembalikan oleh fungsi range_funnel dalam contoh sebelumnya. Hasil dekode ditampilkan berdasarkan ID pengguna. Contoh pernyataan SQL:

SELECT actor_id,
TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- Dekode waktu peristiwa.
range_funnel_level(result) AS res_level -- Dekode level peristiwa.
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;

Berikut adalah bagian dari hasil yang dikembalikan, dari mana Anda dapat memperoleh level dan jumlah kecocokan per pengguna per hari:

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

Setelah memperoleh hasil corong setiap pengguna berdasarkan hari, Anda dapat mengeksplorasi data lebih lanjut sesuai kebutuhan bisnis. Misalnya, Anda dapat mengeksekusi pernyataan SQL berikut untuk melihat ringkasan ukuran langkah harian dan data ringkasan total, di mana level berikutnya berisi data dari level sebelumnya:

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, -- Dekode waktu peristiwa.
    range_funnel_level(result) AS res_level, -- Dekode level peristiwa.
        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;

Berikut adalah hasil kueri:

• \N menunjukkan hasil agregat untuk beberapa hari.

• Bidang res_cnt mencakup data ringkasan setiap level. Level berikutnya berisi data dari level sebelumnya. Misalnya, jika nilai bidang res_level adalah 2 dan nilai bidang res_cnt adalah 1, hanya satu pengguna yang melewati langkah 1 dan 2.

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

Hitung peristiwa berbeda yang terjadi pada waktu yang sama sebagai konversi berbeda

Jika parameter mode dalam fungsi range_funnel diatur ke '1', peristiwa berbeda yang terjadi pada waktu yang sama dihitung sebagai konversi berbeda.

Eksekusi pernyataan berikut untuk membuat tabel bernama funnel_test dan menyisipkan data ke dalam tabel:

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');

Eksekusi pernyataan kueri berikut:

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, -- Dekode waktu peristiwa.
    range_funnel_level(result) AS res_level, -- Dekode level peristiwa.
        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;

Keluaran berikut menunjukkan bahwa peristiwa berbeda yang terjadi pada waktu yang sama dihitung sebagai konversi berbeda.

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

Lakukan pengelompokan berdasarkan hari dengan jendela waktu diatur ke beberapa hari kalender

Dalam skenario nyata, Anda mungkin perlu menganalisis konversi lintas interval. Dalam hal ini, atur parameter use_interval_window dalam fungsi range_funnel ke true. Dalam contoh ini, jendela waktu diatur ke beberapa hari kalender.

-- Lakukan pengelompokan berdasarkan hari dengan jendela waktu diatur ke beberapa hari kalender.
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');

Hitung hasil konversi ketika jendela waktu diatur ke beberapa hari kalender.

-- Jendela waktu diatur selama tiga hari kalender.
    SELECT 
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time, -- Dekode waktu kejadian.
    range_funnel_level(result) AS res_level, -- Dekode level kejadian.
        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;

Keluaran berikut menunjukkan status konversi harian.

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)