全部产品
Search
文档中心

实时数仓Hologres:路径分析函数

更新时间:Apr 24, 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)