Hologres:Path analysis functions-Hologres-Alibaba Cloud

Fungsi analisis jalur menghitung jalur perilaku pengguna berdasarkan data urutan event dan mengembalikan hasil terstruktur yang dapat divisualisasikan dalam Diagram Sankey. Hologres menyediakan dua kategori fungsi: fungsi detail jalur, yang mengagregasi data event mentah menjadi struktur jalur serialisasi, dan fungsi penguraian jalur, yang mengekstrak bidang yang dapat dibaca dari struktur tersebut.

Catatan penggunaan

Fungsi analisis jalur didukung mulai Hologres V2.2 dan versi lebih baru. Jika instans Anda menjalankan V2.1 atau versi sebelumnya, hubungi dukungan teknis Hologres untuk melakukan upgrade.
Sebelum menggunakan fungsi analisis jalur, buat ekstensi flow_analysis. Ekstensi ini berlaku dalam cakupan database—cukup dibuat satu kali per database.
Catatan
Jangan membuat ekstensi duplikat dalam database yang sama.
```
CREATE extension flow_analysis;
```

Cara kerja

Semua kueri analisis jalur mengikuti alur dua langkah:

Panggil fungsi detail jalur (path_analysis_detail atau pad_funnel) dalam kueri GROUP BY untuk mengagregasi baris event mentah per pengguna menjadi struktur jalur serialisasi.
Teruskan hasilnya ke fungsi penguraian jalur (pad_full_path atau pad_session_path_array) untuk mengekstrak bidang yang dapat dibaca—seperti jalur lengkap, batas subjalur, durasi, dan ID session.

Output serialisasi dari langkah 1 (misalnya, {"",\x01a\x01b<,\x01b\x01c<,""}) tidak dapat dibaca secara langsung. Selalu alirkan ke fungsi penguraian.

Fungsi detail jalur

path_analysis_detail

path_analysis_detail mengagregasi baris event per pengguna menjadi struktur jalur serialisasi. Fungsi ini menangkap rangkaian event lengkap, hubungan induk-anak antar-event, serta selisih waktu antara event berurutan.

Sintaks

path_analysis_detail(
  event,                   -- (kolom data) nama event
  event_time,              -- (kolom data) timestamp event
  start_event,             -- (konfigurasi) event awal atau akhir sebagai jangkar jalur
  session_interval_sec,    -- (konfigurasi) detik ketidakaktifan sebelum membagi session
  path_depth,              -- (konfigurasi) jumlah maksimum event yang diikuti dari jangkar
  path_offset,             -- (konfigurasi) jumlah event yang dilewati dari jangkar sebelum pelacakan dimulai
  is_reverse,              -- (konfigurasi) false = maju dari awal; true = mundur dari akhir
  split_session_by_event   -- (konfigurasi) apakah session dipisah berdasarkan kemunculan ulang event jangkar
)

Argumen data (kolom dari tabel Anda)

Argumen	Tipe data	Deskripsi
`event`	text	Nama event yang akan dianalisis
`event_time`	timestamp, timestamptz, atau bigint	Timestamp setiap event

Configuration parameters (nilai literal)

Parameter	Tipe data	Deskripsi
`start_event`	text	Event yang menjadi jangkar jalur. Saat `is_reverse` bernilai `false`, ini adalah event awal; saat `true`, ini adalah event akhir.
`session_interval_sec`	bigint	Ambang batas ketidakaktifan dalam detik. Jika jeda antara dua event berurutan melebihi nilai ini, Hologres membaginya menjadi session terpisah. Atur ke `-1` untuk membagi session hanya berdasarkan pengulangan `start_event`.
`path_depth`	bigint	Jumlah maksimum event yang dilacak maju (atau mundur) dari event jangkar.
`path_offset`	bigint	Jumlah event yang dilewati dari jangkar sebelum pelacakan dimulai. `0` berarti tanpa offset. Misalnya, dengan event `a → b → c` dan `path_offset = 1`, pelacakan dimulai dari `b`.
`is_reverse`	bool	`false`: lacak event dalam urutan kronologis dari event awal. `true`: lacak event dalam urutan kronologis terbalik dari event akhir.
`split_session_by_event`	bool	`true`: buat session baru setiap kali event jangkar muncul ulang dalam jendela waktu. `false` (default): bagi session hanya berdasarkan `session_interval_sec`.

Nilai kembalian

Mengembalikan array serialisasi TEXT, contohnya: {"",\x01a\x01b<,\x01b\x01c<,""}. Teruskan output ini ke pad_full_path atau pad_session_path_array—output ini tidak dapat dibaca secara langsung.

Perilaku `split_session_by_event`

Parameter ini mengontrol cara jalur berulang dalam satu jendela waktu dihitung.

Nilai	Perilaku	Kapan digunakan
`false` (default)	Jalur berulang `a → b → c` yang terjadi 10 kali dalam satu session dihitung sebagai satu konversi untuk session tersebut. Catatan Jika Anda mengatur `session_interval_sec` ke `-1`, sistem membagi session berdasarkan event awal atau akhir yang ditentukan, terlepas dari pengaturan ini.	Gunakan ketika Anda ingin satu perjalanan agregat per jendela session.
`true`	Setiap kemunculan ulang event jangkar memulai session baru, sehingga 10 pengulangan dihitung sebagai 10 konversi terpisah.	Gunakan ketika setiap jalur yang dipicu merupakan perjalanan pengguna independen, seperti melacak pembelian berulang dalam satu kunjungan.

pad_funnel

pad_funnel menyaring output serialisasi dari path_analysis_detail ke urutan event target tertentu (subjalur), membuang event di luar urutan tersebut.

Sintaks

pad_funnel(path_analysis_detail(), target_path)

Parameter

Parameter	Tipe data	Deskripsi
`path_analysis_detail()`	text	Output serialisasi agregat dari `path_analysis_detail`
`target_path`	text	Urutan event yang akan difilter, dalam bentuk literal array (misalnya, `array['Log on', 'Purchase']`)

Nilai kembalian

Mengembalikan array serialisasi TEXT, contohnya: text []{"",\x01a\x01b<,\x01b\x01c<,""}. Teruskan hasilnya ke fungsi penguraian jalur.

Fungsi penguraian jalur

pad_full_path

pad_full_path mengurai output serialisasi dari path_analysis_detail (atau pad_funnel) menjadi bidang terstruktur: jalur lengkap per session, event awal dan akhir setiap subjalur, posisinya dalam jalur lengkap, serta selisih waktu di antaranya.

Sintaks

pad_full_path(path_analysis_detail())

Parameter

Parameter	Tipe data	Deskripsi
`path_analysis_detail()`	text	Output serialisasi agregat dari `path_analysis_detail`

Nilai kembalian

pad_full_path mengembalikan sebuah array. Gunakan UNNEST untuk mengembangkannya menjadi baris, lalu ekstrak bidang individual dengan sub-fungsi berikut:

Sub-fungsi	Tipe kembalian	Deskripsi
`pad_sub_path_left(result)`	text	Event awal subjalur
`pad_sub_path_right(result)`	text	Event akhir subjalur
`pad_sub_index_left(result)`	bigint	Posisi event awal dalam jalur lengkap (berbasis 0)
`pad_sub_index_right(result)`	bigint	Posisi event akhir dalam jalur lengkap (berbasis 0)
`pad_sub_cost(result)`	bigint	Durasi pengalihan subjalur, dalam detik
`pad_sub_session(result)`	bigint	Indeks session valid tempat subjalur ini berada (berbasis 0)

pad_session_path_array

pad_session_path_array mengekstrak rangkaian event terurut untuk session tertentu dari output serialisasi path_analysis_detail.

Sintaks

pad_session_path_array(path_analysis_detail(), session_idx)

Parameter

Parameter	Tipe data	Deskripsi
`path_analysis_detail()`	text	Output serialisasi agregat dari `path_analysis_detail`
`session_idx`	bigint	Indeks session yang akan diekstrak (berbasis 0)

Nilai kembalian

Mengembalikan ARRAY nama event dalam urutan kejadiannya dalam session tersebut.

Contoh

Menyiapkan data sampel

-- Buat ekstensi (sekali per database).
CREATE extension flow_analysis;

-- Buat tabel event sampel.
CREATE TABLE path_demo(
    uid        text,
    event      text,
    event_time timestamptz
);

-- Masukkan event sampel untuk empat pengguna.
INSERT INTO path_demo VALUES
('1', 'Register',          '2023-11-24 16:01:23+08'),
('1', 'Log on',            '2023-11-24 16:02:10+08'),
('1', 'Browse',            '2023-11-24 16:02:15+08'),
('1', 'View live streams', '2023-11-24 16:03:10+08'),
('1', 'Browse',            '2023-11-24 16:03:15+08'),
('1', 'Add to favorites',  '2023-11-24 16:04:20+08'),
('1', 'Browse',            '2023-11-24 16:07:21+08'),
('1', 'Purchase',          '2023-11-24 16:08:23+08'),
('1', 'Exit',              '2023-11-24 16:09:05+08'),

('2', 'Log on',            '2023-11-24 16:10:23+08'),
('2', 'Purchase',          '2023-11-24 16:12:23+08'),

('3', 'Log on',            '2023-11-24 16:02:23+08'),
('3', 'Browse',            '2023-11-24 16:02:23+08'),
('3', 'Add to favorites',  '2023-11-24 16:03:53+08'),
('3', 'View live streams', '2023-11-24 16:04:53+08'),

('4', 'Log on',            '2023-11-24 16:02:23+08'),
('4', 'Browse',            '2023-11-24 16:03:53+08'),
('4', 'Purchase',          '2023-11-24 16:04:23+08'),
('4', 'View live streams', '2023-11-24 16:05:53+08'),
('4', 'Cancel the order',  '2023-11-24 16:06:53+08');

Semua contoh berikut menggunakan tabel path_demo ini.

Contoh 1: Dapatkan jalur lengkap untuk setiap pengguna

Dua contoh berikut sama-sama menggunakan path_depth = 7 dan session_interval_sec = 180. Perbedaannya terletak pada apakah session dibagi hanya berdasarkan ketidakaktifan atau berdasarkan ketidakaktifan dan pengulangan event jangkar.

Pisahkan session hanya berdasarkan waktu (`split_session_by_event = false`)

Jadikan Log on sebagai jangkar. Session berakhir ketika jeda antara event berurutan melebihi 180 detik.

SELECT
    uid,
    pad_full_path(
        path_analysis_detail(
            event,
            event_time,
            'Log on',  -- start_event: jadikan 'Log on' sebagai jangkar jalur
            180,        -- session_interval_sec: pisahkan setelah 180 detik ketidakaktifan
            7,          -- path_depth: lacak hingga 7 event dari jangkar
            0,          -- path_offset: tanpa offset
            false       -- is_reverse: urutan kronologis
                        -- split_session_by_event: dihilangkan, default false
        )
    ) AS ret
FROM path_demo
GROUP BY uid;

Hasil:

 uid |                        ret
-----+---------------------------------------------------
 3   | {Log on -> Add to favorites -> View live streams}
 4   | {Log on -> Browse -> Purchase -> View live streams -> Cancel the order}
 1   | {Log on -> Browse -> View live streams -> Browse -> Add to favorites}
 2   | {Log on -> Purchase}
(4 rows)

Pisahkan session berdasarkan waktu dan pengulangan event jangkar (`split_session_by_event = true`)

Jadikan Browse sebagai jangkar. Session baru dimulai baik ketika ketidakaktifan melebihi 180 detik maupun ketika Browse muncul ulang.

SELECT
    uid,
    pad_full_path(
        path_analysis_detail(
            event,
            event_time,
            'Browse',  -- start_event: jadikan 'Browse' sebagai jangkar jalur
            180,        -- session_interval_sec
            7,          -- path_depth
            0,          -- path_offset
            false,      -- is_reverse: urutan kronologis
            true        -- split_session_by_event: session baru setiap 'Browse'
        )
    ) AS ret
FROM path_demo
GROUP BY uid;

Hasil:

 uid |                            ret
-----+-----------------------------------------------------------
 1   | {Browse -> View live streams, Browse -> Add to favorites, Browse -> Purchase -> Exit}
 2   | {}
 4   | {Browse -> Purchase -> View live streams -> Cancel the order}
 3   | {Browse -> Log on -> Add to favorites -> View live streams}

Pengguna 1 memiliki tiga session terpisah karena Browse terjadi tiga kali. Pengguna 2 tidak memiliki hasil karena tidak ada event Browse untuk pengguna tersebut.

Contoh 2: Ekspansi hasil jalur menjadi baris

Gunakan UNNEST untuk mengembangkan array jalur menjadi satu baris per jalur session, yang berguna untuk agregasi lanjutan.

SELECT
    uid,
    unnest(
        pad_full_path(
            path_analysis_detail(
                event,
                event_time,
                'Log on',  -- start_event
                180,        -- session_interval_sec
                7,          -- path_depth
                0,          -- path_offset
                false       -- is_reverse
            )
        )
    ) AS ret
FROM path_demo
GROUP BY uid;

Hasil:

 uid |                       ret
-----+-------------------------------------------------
 3   | Log on -> Add to favorites -> View live streams
 1   | Log on -> Browse -> View live streams -> Browse -> Add to favorites
 2   | Log on -> Purchase
 4   | Log on -> Browse -> Purchase -> View live streams -> Cancel the order
(4 rows)

Untuk informasi lebih lanjut, lihat Klausa UNNEST.

Contoh 3: Ekspansi subjalur dan dapatkan waktu per event

UNNEST pada output mentah path_analysis_detail (tanpa pad_full_path) menghasilkan satu baris per subjalur. Teruskan setiap baris ke sub-fungsi pad_sub_* untuk mengekstrak event awal, event akhir, posisi, dan selisih waktu.

SELECT
    uid,
    pad_sub_session(ret)       AS session_id,
    pad_sub_path_left(ret)     AS sub_path_left,
    pad_sub_path_right(ret)    AS sub_path_right,
    pad_sub_index_left(ret)    AS sub_index_left,
    pad_sub_index_right(ret)   AS sub_index_right,
    pad_sub_cost(ret)          AS sub_cost
FROM (
    SELECT
        uid,
        unnest(
            path_analysis_detail(
                event,
                event_time,
                'Log on',  -- start_event
                180,        -- session_interval_sec
                7,          -- path_depth
                0,          -- path_offset
                false       -- is_reverse
            )
        ) AS ret
    FROM path_demo
    GROUP BY uid
) a;

Hasil:

 uid | session_id | sub_path_left    | sub_path_right    | sub_index_left | sub_index_right | sub_cost
-----+------------+------------------+-------------------+----------------+-----------------+----------
 1   |          0 |                  | Log on            |             -1 |               0 |        0
 1   |          0 | Log on           | Browse            |              0 |               1 |        5
 1   |          0 | Browse           | View live streams |              1 |               2 |       55
 1   |          0 | View live streams| Browse            |              2 |               3 |        5
 1   |          0 | Browse           | Add to favorites  |              3 |               4 |       65
 2   |          0 |                  | Log on            |             -1 |               0 |        0
 2   |          0 | Log on           | Purchase          |              0 |               1 |      120
 3   |          0 |                  | Log on            |             -1 |               0 |        0
 3   |          0 | Log on           | Add to favorites  |              0 |               1 |       90
 3   |          0 | Add to favorites | View live streams |              1 |               2 |       60
 4   |          0 |                  | Log on            |             -1 |               0 |        0
 4   |          0 | Log on           | Browse            |              0 |               1 |       90
 4   |          0 | Browse           | Purchase          |              1 |               2 |       30
 4   |          0 | Purchase         | View live streams |              2 |               3 |       90
 4   |          0 | View live streams| Cancel the order  |              3 |               4 |       60
(15 rows)

Baris pertama untuk setiap pengguna (di mana sub_path_left kosong dan sub_index_left = -1) merepresentasikan titik masuk virtual sebelum event jangkar.

Contoh 4: Dapatkan rangkaian event untuk session tertentu

pad_session_path_array mengekstrak event terurut untuk indeks session 0 (session pertama untuk setiap pengguna).

SELECT
    uid,
    pad_session_path_array(
        path_analysis_detail(
            event,
            event_time,
            'Log on',  -- start_event
            180,        -- session_interval_sec
            7,          -- path_depth
            0,          -- path_offset
            false       -- is_reverse
        ),
        0   -- session_idx: ambil session pertama (berbasis 0)
    ) AS ret
FROM path_demo
GROUP BY uid;

Hasil:

 uid |                      ret
-----+-----------------------------------------------
 1   | {Log on,Browse,View live streams,Browse,Add to favorites}
 2   | {Log on,Purchase}
 3   | {Log on,Add to favorites,View live streams}
 4   | {Log on,Browse,Purchase,View live streams,Cancel the order}
(4 rows)

Contoh 5: Hitung transisi per subjalur

Kueri berikut menghitung berapa kali setiap transisi subjalur terjadi di seluruh pengguna (tampilan halaman). Untuk menghitung pengunjung unik, hapus duplikat berdasarkan uid sebelum agregasi.

SELECT
    sub_index,
    sub_path_left,
    sub_path_right,
    count(uid)
FROM (
    SELECT
        uid,
        pad_sub_path_left(ret)    AS sub_path_left,
        pad_sub_path_right(ret)   AS sub_path_right,
        pad_sub_index_right(ret)  AS sub_index
    FROM (
        SELECT
            uid,
            unnest(
                path_analysis_detail(
                    event,
                    event_time,
                    'Log on',  -- start_event
                    180,        -- session_interval_sec
                    7,          -- path_depth
                    0,          -- path_offset
                    false       -- is_reverse
                )
            ) AS ret
        FROM path_demo
        GROUP BY uid
    ) a
) a
GROUP BY sub_index, sub_path_left, sub_path_right
ORDER BY sub_index, sub_path_left, sub_path_right;

Hasil:

 sub_index | sub_path_left    | sub_path_right    | count
-----------+------------------+-------------------+-------
         0 |                  | Log on            |     4
         1 | Log on           | Add to favorites  |     1
         1 | Log on           | Browse            |     2
         1 | Log on           | Purchase          |     1
         2 | Add to favorites | View live streams |     1
         2 | Browse           | View live streams |     1
         2 | Browse           | Purchase          |     1
         3 | View live streams| Browse            |     1
         3 | Purchase         | View live streams |     1
         4 | Browse           | Add to favorites  |     1
         4 | View live streams| Cancel the order  |     1
(11 rows)

Contoh 6: Hitung durasi rata-rata per subjalur

SELECT
    sub_path_left,
    sub_path_right,
    avg(sub_cost)
FROM (
    SELECT
        uid,
        pad_sub_path_left(ret)  AS sub_path_left,
        pad_sub_path_right(ret) AS sub_path_right,
        pad_sub_cost(ret)       AS sub_cost
    FROM (
        SELECT
            uid,
            unnest(
                path_analysis_detail(
                    event,
                    event_time,
                    'Log on',  -- start_event
                    180,        -- session_interval_sec
                    7,          -- path_depth
                    0,          -- path_offset
                    false       -- is_reverse
                )
            ) AS ret
        FROM path_demo
        GROUP BY uid
    ) a
) a
GROUP BY sub_path_left, sub_path_right
ORDER BY sub_path_left, sub_path_right;

Hasil:

 sub_path_left    | sub_path_right    |    avg
------------------+-------------------+------------
 Add to favorites | View live streams |  60.000000
 Browse           | Add to favorites  |  65.000000
 Browse           | View live streams |  55.000000
 Browse           | Purchase          |  30.000000
 Log on           | Add to favorites  |  90.000000
 Log on           | Browse            |  47.500000
 Log on           | Purchase          | 120.000000
 View live streams| Cancel the order  |  60.000000
 View live streams| Browse            |   5.000000
 Purchase         | View live streams |  90.000000
                  | Log on            |   0.000000
(11 rows)

Contoh 7: Gabungkan jalur lengkap dan subjalur dalam satu kueri

Kueri berikut menghubungkan setiap subjalur dengan jalur session lengkap tempat ia berada, menggunakan pad_full_path dan UNNEST secara bersamaan.

SELECT
    uid,
    pad_sub_session(item)                    AS session_id,
    full_path[pad_sub_session(item) + 1]     AS full_path,
    pad_sub_path_left(item)                  AS sub_path_left,
    pad_sub_path_right(item)                 AS sub_path_right,
    pad_sub_index_right(item)                AS sub_idx,
    pad_sub_cost(item)                       AS sub_cost
FROM (
    SELECT
        uid,
        unnest(ret)        AS item,
        pad_full_path(ret) AS full_path
    FROM (
        SELECT
            uid,
            path_analysis_detail(
                event,
                event_time,
                'Log on',  -- start_event
                180,        -- session_interval_sec
                7,          -- path_depth
                0,          -- path_offset
                false       -- is_reverse
            ) AS ret
        FROM path_demo
        GROUP BY uid
    ) a
) a;

Hasil:

 uid | session_id |                        full_path                        | sub_path_left    | sub_path_right    | sub_idx | sub_cost
-----+------------+---------------------------------------------------------+------------------+-------------------+---------+----------
 3   |          0 | Log on -> Add to favorites -> View live streams         |                  | Log on            |       0 |        0
 3   |          0 | Log on -> Add to favorites -> View live streams         | Log on           | Add to favorites  |       1 |       90
 3   |          0 | Log on -> Add to favorites -> View live streams         | Add to favorites | View live streams |       2 |       60
 1   |          0 | Log on -> Browse -> View live streams -> Browse -> Add to favorites     |                  | Log on            |       0 |        0
 1   |          0 | Log on -> Browse -> View live streams -> Browse -> Add to favorites     | Log on           | Browse            |       1 |        5
 1   |          0 | Log on -> Browse -> View live streams -> Browse -> Add to favorites     | Browse           | View live streams |       2 |       55
 1   |          0 | Log on -> Browse -> View live streams -> Browse -> Add to favorites     | View live streams| Browse            |       3 |        5
 1   |          0 | Log on -> Browse -> View live streams -> Browse -> Add to favorites     | Browse           | Add to favorites  |       4 |       65
 2   |          0 | Log on -> Purchase                                      |                  | Log on            |       0 |        0
 2   |          0 | Log on -> Purchase                                      | Log on           | Purchase          |       1 |      120
 4   |          0 | Log on -> Browse -> Purchase -> View live streams -> Cancel the order|                  | Log on            |       0 |        0
 4   |          0 | Log on -> Browse -> Purchase -> View live streams -> Cancel the order| Log on           | Browse            |       1 |       90
 4   |          0 | Log on -> Browse -> Purchase -> View live streams -> Cancel the order| Browse           | Purchase          |       2 |       30
 4   |          0 | Log on -> Browse -> Purchase -> View live streams -> Cancel the order| Purchase         | View live streams |       3 |       90
 4   |          0 | Log on -> Browse -> Purchase -> View live streams -> Cancel the order| View live streams| Cancel the order  |       4 |       60
(15 rows)

Contoh 8: Filter ke jalur funnel tertentu

Gunakan pad_funnel untuk membatasi hasil hanya pada pengguna yang mengikuti urutan event tertentu. Kueri berikut mencari pengguna yang melalui Log on → Purchase.

SELECT
    uid,
    pad_full_path(
        pad_funnel(
            path_analysis_detail(
                event,
                event_time,
                'Log on',  -- start_event
                180,        -- session_interval_sec
                7,          -- path_depth
                0,          -- path_offset
                false       -- is_reverse
            ),
            array['Log on', 'Purchase']  -- target_path: filter ke funnel ini
        )
    ) AS ret
FROM path_demo
GROUP BY uid;

Hasil:

 uid |         ret
-----+----------------------
 3   | {Log on}
 4   | {Log on -> Purchase}
 1   | {Log on}
 2   | {Log on -> Purchase}
(4 rows)

Pengguna 4 dan 2 menyelesaikan funnel lengkap Log on → Purchase. Pengguna 1 dan 3 mencapai Log on tetapi tidak melanjutkan ke Purchase dalam kedalaman jalur yang dilacak.