Hologres實戰：路徑分析函數

前言

Hologres提供了一套高效的路徑分析函數，包括路徑明細計算和結果解析功能，能夠幫助用戶深入理解用戶行為路徑，并通過桑基圖實現數據可視化。

一、核心功能

路徑明細計算：精確記錄用戶在產品或功能中的完整訪問路徑
結果解析：對路徑數據進行深度分析和指標計算
可視化支持：生成桑基圖展示路徑流量分布和轉化情況

二、路徑明細函數

1. 路徑明細函數（`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)

參數說明

在這里插入圖片描述

2. 路徑漏斗函數（`pad_funnel`）

pad_funnel函數用于獲取特定事件組合構成的子路徑信息。

語法

pad_full_path(path_analysis_detail())

參數說明

path_analysis_detail()：路徑明細函數返回的路徑明細的聚合結果數組。

返回值說明

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)

在這里插入圖片描述

3. `pad_session_path_array`

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

語法

pad_session_path_array(path_analysis_detail(), session_idx)

參數說明

path_analysis_detail()：路徑明細函數返回的路徑明細的聚合結果數組。
session_idx：指定的會話序號。

三、使用示例

1. 準備數據

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

2. 使用案例

示例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;

在這里插入圖片描述

按照時間和事件切分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;

在這里插入圖片描述

示例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;

在這里插入圖片描述

示例3：展開子路徑并獲取每一步的路徑明細

--展開子路徑
SELECTuid,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 (SELECTuid,unnest( path_analysis_detail (event, event_time, '登錄', 180, 7, 0, FALSE)) AS retFROMpath_demo GROUP BYuid) a ;

在這里插入圖片描述
同時我們也可以結合可視化工具，例如DataV的Echarts 桑基圖，將計算結果形成可視化的桑基圖，示例如下：

在這里插入圖片描述

示例4：計算每個子路徑的PV、UV（未去重）

--計算每個子路徑的uv/pv，未去重，如果需要去重，可以對uid做
SELECTsub_index,sub_path_left,sub_path_right,count(uid)
FROM (SELECTuid,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_indexFROM (SELECTuid,unnest(path_analysis_detail (event, event_time, '登錄', 180, 7, 0, FALSE)) AS retFROMpath_demoGROUP BYuid) a) a
GROUP BYsub_index,sub_path_left,sub_path_right
ORDER BYsub_index,sub_path_left,sub_path_right;

在這里插入圖片描述

示例5：計算每個子路徑的平均耗時

--計算子路徑的平均耗時
SELECTsub_path_left,sub_path_right,avg(sub_cost)
FROM (SELECTuid,pad_sub_path_left (ret) AS sub_path_left,pad_sub_path_right (ret) AS sub_path_right,pad_sub_cost (ret) AS sub_costFROM (SELECTuid,unnest(path_analysis_detail (event, event_time, '登錄', 180, 7, 0, FALSE)) AS retFROMpath_demoGROUP BYuid) a) a
GROUP BYsub_path_left,sub_path_right
ORDER BYsub_path_left,sub_path_right;

在這里插入圖片描述

示例6：會話路徑與子路徑明細關聯

`--會話路徑與子路徑關聯
selectuid,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(selectuid,unnest(ret) as item,pad_full_path(ret) as full_pathfrom(selectuid,path_analysis_detail(event, event_time, '登錄', 180, 7, 0, false) as retfrompath_demogroup byuid) a) a;

在這里插入圖片描述

示例7：查看指定的部分路徑明細

--通過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;