全部產品
Search
文件中心

Hologres:路徑分析函數

更新時間:Jun 30, 2024

Hologres的路徑分析函數(包括路徑明細函數和路徑解析函數)能夠高效處理路徑計算任務,進行路徑明細計算和結果解析,並將記錄的資料應用於產生桑基圖展示,協助您更好地理解和可視化複雜的路徑資訊。本文為您介紹路徑明細函數和路徑解析函數的使用。

背景資訊

路徑分析會記錄您使用產品或功能時的路徑分布情況,以及每次會話的訪問順序,並產生直觀易懂的行為路徑桑基圖。桑基圖將展示訪問流程中的每一個關鍵節點,以及節點間流量的流入、流出情況,從而有助於業務精細化營運。通過路徑分析,可以清晰地瞭解產品每個關鍵功能的訪問情況,進一步輔助營運和產品進行下一步的業務策略最佳化和產品迭代,協助業務更加健康地成長。Hologres支援路徑明細函數和路徑解析函數,分別用於路徑明細計算和路徑結果解析計算,輸出的結果可以直接用於桑基圖展示。

image

注意事項

  • 僅Hologres V2.2及以上版本支援使用路徑分析相關函數,若您的執行個體為V2.1或以下版本,可聯絡Hologres支援人員升級執行個體。

  • 在使用路徑分析相關函數之前,需要建立Extension。Extension預設載入到public Schema下,且不支援載入到其他Schema。

     --開啟extension
    CREATE extension flow_analysis;
    說明

    Extension是資料庫層級的函數,一個資料庫只需執行一次即可。若當前DB已建立Extension,則無需重複建立。

路徑明細函數

路徑明細函數(path_analysis_detail

path_analysis_detail函數用於將指定的事件數目據深度解析並呈現為詳細的路徑結構。其輸出結果以序列化數組的形式展現,內容詳盡豐富,涵蓋了路徑的完整序列、路徑內各個節點間的父子關係,以及每個路徑步驟的執行耗時等關鍵資訊。

  • 文法

    path_analysis_detail(
      event, 
    	event_time, 
    	start_event, 
    	session_interval_sec, 
    	path_depth, 
    	path_offset, 
    	is_reverse,
    split_session_by_event)
  • 參數說明

    參數名稱

    資料類型

    說明

    event

    text (data)

    待分析的事件。

    event_time

    timestamp、timestamptz、bigint (data)

    事件對應的時間序列。

    start_event

    text (literal)

    指定的開始或結束事件。

    session_interval_sec

    bigint (literal)

    SESSION的間隔時間,單位為:秒(s)。

    當SESSION中相鄰事件的時間間隔超過已設定的SESSION間隔時間,系統將其拆分為兩個會話。例如,若設定間隔時間為30秒(s),相鄰事件的時間間隔超過30秒(s),則會被劃分為兩個會話。

    path_depth

    bigint (literal)

    擷取從開始或結束事件起的事件序列長度。

    path_offset

    bigint (literal)

    從開始或結束事件起的事件位移,0表示不位移。

    例如:a、b和c事件,如果以a為起始事件,位移設定為1,那麼就會從b開始匹配。

    is_reverse

    bool (literal)

    事件序列輸出方式,取值說明如下:

    • false:指定開始事件,事件序列正序輸出。

    • true:指定結束事件,事件序列倒序輸出。

    split_session_by_event

    bool (literal)

    會話切分方式,取值說明如下:

    • true:在順序的事件序列中遇到指定的開始或結束事件,就會切分會話。

    • false(預設):只按照session_interval_sec參數值,切分會話。

      說明

      session_interval_sec值為-1時,不會按照時間切分,按照事件切分。

    例如:若同一個會話中,有重複操作路徑(例如:abcabc...abc,10次),pad_funnel函數匹配機制僅記錄一次轉化。若您希望每次重新開始操作路徑時,都視為一個新的會話,以便獨立計算每一次轉化,可通過配置此參數進行事務切分,在每次開始新操作時,能夠被獨立記錄和統計。

  • 傳回值說明

    path_analysis_detail:資料類型為text,樣本:{"",\x01a\x01b<,\x01b\x01c<,""}

    說明

    傳回值是一個序列化的數組,不可直接讀取,需要通過對應的路徑結果解析函數讀取。

路徑漏鬥函數(pad_funnel)

pad_funnel函數用於擷取特定事件組合構成的子路徑資訊。

  • 文法

    pad_funnel(path_analysis_detail(), target_path)
  • 參數說明

    參數名稱

    資料類型

    說明

    path_analysis_detail()

    text (data)

    路徑明細函數返回的路徑明細的彙總結果數組。

    target_path

    text (literal)

    指定的事件序列。

  • 傳回值說明

    path_analysis_detail:資料類型為text,樣本:text []{"",\x01a\x01b<,\x01b\x01c<,""}

    說明

    傳回值是一個序列化的數組,不可直接讀取,需要通過對應的路徑結果解析函數讀取。

路徑結果解析函數

pad_full_path

pad_full_path路徑結果解析函數,能夠對path_analysis_detail函數的傳回值進行完整的路徑解析,還可以解析每一步的父路徑、子路徑以及對應的耗時,從而有助於業務更直觀地觀測路徑情況。

  • 文法

    pad_full_path(path_analysis_detail())
  • 參數說明

    path_analysis_detail():路徑明細函數返回的路徑明細的彙總結果數組。

  • 傳回值說明

    • pad_full_path函數可返回以下內容。

      pad_sub_path_left(unnested_pad_result)
      pad_sub_path_right(unnested_pad_result)
      pad_sub_index_left(unnested_pad_result)
      pad_sub_index_right(unnested_pad_result)
      pad_sub_cost(unnested_pad_result)
      pad_sub_session(unnested_pad_result)
    • 上述返回參數說明:

      函數

      資料類型

      說明

      pad_sub_path_left

      text

      子路徑的開始事件。

      pad_sub_path_right

      text

      子路徑的結束事件。

      pad_sub_index_left

      bigint

      子路徑的開始事件是所屬會話完整路徑的第幾個。

      pad_sub_index_right

      bigint

      子路徑的結束事件是所屬會話完整路徑的第幾個。

      pad_sub_cost

      bigint

      子路徑的跳轉耗時,單位:秒(s)。

      pad_sub_session

      bigint

      子路徑屬於第幾個有效SESSION。

pad_session_path_array

pad_session_path_array路徑結果解析函數可以根據指定的會話ID,精準提取出該會話內發生的事件序列,並按照路徑首碼進行有序組織。

  • 文法

    pad_session_path_array(path_analysis_detail(), session_idx)
  • 參數說明

    • path_analysis_detail():路徑明細函數返回的路徑明細的彙總結果數組。

    • session_idx:指定的會話序號。

  • 傳回值說明

    返回數群組類型資料。

使用樣本

準備資料

--建立Extension,Extension是DB層級的函數,一個DB只需執行一次即可
CREATE extension flow_analysis;
--準備資料
CREATE TABLE path_demo( 
    uid text,
    event text,
    event_time timestamptz
);

INSERT INTO path_demo VALUES
('1','註冊','2023-11-24 16:01:23+08'),
('1','登入','2023-11-24 16:02:10+08'),
('1','瀏覽','2023-11-24 16:02:15+08'),
('1','看直播','2023-11-24 16:03:10+08'),
('1','瀏覽','2023-11-24 16:03:15+08'),
('1','收藏','2023-11-24 16:04:20+08'),
('1','瀏覽','2023-11-24 16:07:21+08'),
('1','購買','2023-11-24 16:08:23+08'),
('1','退出','2023-11-24 16:09:05+08'),

('2','登入','2023-11-24 16:10:23+08'),
('2','購買','2023-11-24 16:12:23+08'),

('3','登入','2023-11-24 16:02:23+08'),
('3','瀏覽','2023-11-24 16:02:23+08'),
('3','收藏','2023-11-24 16:03:53+08'),
('3','看直播','2023-11-24 16:04:53+08'),

('4','登入','2023-11-24 16:02:23+08'),
('4','瀏覽','2023-11-24 16:03:53+08'),
('4','購買','2023-11-24 16:04:23+08'),
('4','看直播','2023-11-24 16:05:53+08'),
('4','取消下單','2023-11-24 16:06:53+08');

樣本1:記錄事件全部路徑

  • 按照時間切分SESSION:指定起始事件,按照時間切分SESSION,並設定SESSION間隔為180 s,匹配的序列長度為7。

    --按照時間切分:指定開始事件為“登入”,SESSION時間間隔為180 s,匹配序列長度為7,並通過pad_full_path函數對結果解碼
    SELECT uid, pad_full_path(path_analysis_detail(event, event_time, '登入', 180, 7, 0, false)) AS ret FROM path_demo GROUP BY uid;

    返回結果如下。

     uid |                        ret
    -----+---------------------------------------------------
     3   | {登入->收藏->看直播}
     4   | {登入->瀏覽->購買->看直播->取消下單}
     1   | {登入->瀏覽->看直播->瀏覽->收藏}
     2   | {登入->購買}
    (4 rows)
  • 按照時間和事件切分SESSION:指定起始事件,SESSION時間間隔為180 s,匹配序列長度為7。

    --按照時間和事件切分:起始事件為"瀏覽",間隔時間為180 s,序列長度為7,並通過pad_full_path函數對結果解碼
    SELECT uid, pad_full_path(path_analysis_detail(event, event_time, '瀏覽', 180, 7, 0, false,TRUE)) AS ret FROM path_demo GROUP BY uid;

    返回結果如下。

     uid |                            ret
    -----+-----------------------------------------------------------
     1   | {瀏覽->看直播,瀏覽->收藏,瀏覽->購買->退出}
     2   | {}
     4   | {瀏覽->購買->看直播->取消下單}
     3   | {瀏覽->登入->收藏->看直播}

樣本2:展開路徑結果

--將路徑展開
SELECT uid, unnest(pad_full_path(path_analysis_detail(event, event_time, '登入', 180, 7, 0, false))) AS ret FROM path_demo GROUP BY uid;
說明

關於UNNEST函數詳情,請參見UNNEST子句

返回結果如下。

 uid |                       ret
-----+-------------------------------------------------
 3   | 登入->收藏->看直播
 1   | 登入->瀏覽->看直播->瀏覽->收藏
 2   | 登入->購買
 4   | 登入->瀏覽->購買->看直播->取消下單
(4 rows)

樣本3:展開子路徑並擷取每一步的路徑明細

--展開子路徑
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, '登入', 180, 7, 0, FALSE)) AS ret
    FROM
        path_demo 
    GROUP BY
        uid) a ;

返回結果如下。

  uid | session_id | sub_path_left | sub_path_right | sub_index_left | sub_index_right | sub_cost
-----+------------+---------------+----------------+----------------+-----------------+----------
 1   |          0 |               | 登入         |             -1 |               0 |        0
 1   |          0 | 登入        | 瀏覽         |              0 |               1 |        5
 1   |          0 | 瀏覽        | 看直播      |              1 |               2 |       55
 1   |          0 | 看直播     | 瀏覽         |              2 |               3 |        5
 1   |          0 | 瀏覽        | 收藏         |              3 |               4 |       65
 2   |          0 |               | 登入         |             -1 |               0 |        0
 2   |          0 | 登入        | 購買         |              0 |               1 |      120
 3   |          0 |               | 登入         |             -1 |               0 |        0
 3   |          0 | 登入        | 收藏         |              0 |               1 |       90
 3   |          0 | 收藏        | 看直播      |              1 |               2 |       60
 4   |          0 |               | 登入         |             -1 |               0 |        0
 4   |          0 | 登入        | 瀏覽         |              0 |               1 |       90
 4   |          0 | 瀏覽        | 購買         |              1 |               2 |       30
 4   |          0 | 購買        | 看直播      |              2 |               3 |       90
 4   |          0 | 看直播     | 取消下單   |              3 |               4 |       60
(15 rows)

樣本4:擷取指定會話內的事件序列

SELECT
    uid,
    pad_session_path_array (path_analysis_detail (event, event_time, '登入', 180, 7, 0,FALSE), 0) AS ret
FROM
    path_demo
GROUP BY
    uid;

返回結果如下。

 uid |                      ret
-----+-----------------------------------------------
 1   | {登入,瀏覽,看直播,瀏覽,收藏}
 2   | {登入,購買}
 3   | {登入,收藏,看直播}
 4   | {登入,瀏覽,購買,看直播,取消下單}
(4 rows)

樣本5:計算每個子路徑的PV、UV(未去重)

--計算每個子路徑的uv/pv,未去重,如果需要去重,可以對uid做
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, '登入', 180, 7, 0, FALSE)) 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;

返回結果如下。

 sub_index | sub_path_left | sub_path_right | count
-----------+---------------+----------------+-------
         0 |               | 登入         |     4
         1 | 登入        | 收藏         |     1
         1 | 登入        | 瀏覽         |     2
         1 | 登入        | 購買         |     1
         2 | 收藏        | 看直播      |     1
         2 | 瀏覽        | 看直播      |     1
         2 | 瀏覽        | 購買         |     1
         3 | 看直播     | 瀏覽         |     1
         3 | 購買        | 看直播      |     1
         4 | 瀏覽        | 收藏         |     1
         4 | 看直播     | 取消下單   |     1
(11 rows)

樣本6:計算每個子路徑的平均耗時

--計運算元路徑的平均耗時
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, '登入', 180, 7, 0, FALSE)) 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;

返回結果如下。

 sub_path_left | sub_path_right |    avg
---------------+----------------+------------
 收藏        | 看直播      |  60.000000
 瀏覽        | 收藏         |  65.000000
 瀏覽        | 看直播      |  55.000000
 瀏覽        | 購買         |  30.000000
 登入        | 收藏         |  90.000000
 登入        | 瀏覽         |  47.500000
 登入        | 購買         | 120.000000
 看直播     | 取消下單   |  60.000000
 看直播     | 瀏覽         |   5.000000
 購買        | 看直播      |  90.000000
               | 登入         |   0.000000
(11 rows)

樣本7:會話路徑與子路徑明細關聯

--會話路徑與子路徑關聯
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, '登入', 180, 7, 0, false) as ret
                from
                    path_demo
                group by
                    uid
            ) a
    ) a;

返回結果如下。

uid | session_id |                    full_path                    | sub_path_left | sub_path_right | sub_idx | sub_cost
-----+------------+-------------------------------------------------+---------------+----------------+---------+----------
 3   |          0 | 登入->收藏->看直播                       |               | 登入         |       0 |        0
 3   |          0 | 登入->收藏->看直播                       | 登入        | 收藏         |       1 |       90
 3   |          0 | 登入->收藏->看直播                       | 收藏        | 看直播      |       2 |       60
 1   |          0 | 登入->瀏覽->看直播->瀏覽->收藏       |               | 登入         |       0 |        0
 1   |          0 | 登入->瀏覽->看直播->瀏覽->收藏       | 登入        | 瀏覽         |       1 |        5
 1   |          0 | 登入->瀏覽->看直播->瀏覽->收藏       | 瀏覽        | 看直播      |       2 |       55
 1   |          0 | 登入->瀏覽->看直播->瀏覽->收藏       | 看直播     | 瀏覽         |       3 |        5
 1   |          0 | 登入->瀏覽->看直播->瀏覽->收藏       | 瀏覽        | 收藏         |       4 |       65
 2   |          0 | 登入->購買                                  |               | 登入         |       0 |        0
 2   |          0 | 登入->購買                                  | 登入        | 購買         |       1 |      120
 4   |          0 | 登入->瀏覽->購買->看直播->取消下單 |               | 登入         |       0 |        0
 4   |          0 | 登入->瀏覽->購買->看直播->取消下單 | 登入        | 瀏覽         |       1 |       90
 4   |          0 | 登入->瀏覽->購買->看直播->取消下單 | 瀏覽        | 購買         |       2 |       30
 4   |          0 | 登入->瀏覽->購買->看直播->取消下單 | 購買        | 看直播      |       3 |       90
 4   |          0 | 登入->瀏覽->購買->看直播->取消下單 | 看直播     | 取消下單   |       4 |       60
(15 rows)

樣本8:查看指定的部分路徑明細

--通過pad_funnel函數可以查看指定的部分路徑明細,樣本只看Browse>purchase 的轉化情況,查看對應的明細,或者子路徑情況
SELECT uid, pad_full_path(pad_funnel(path_analysis_detail(event, event_time, '登入', 180, 7, 0, false), array['登入', '購買'])) AS ret FROM path_demo GROUP BY uid;

返回結果如下。

 uid |       ret
-----+------------------
 3   | {登入}
 4   | {登入->購買}
 1   | {登入}
 2   | {登入->購買}
(4 rows)