需求

用户路径分析，顾名思义，就是指用户在APP或网站中的访问路径。为了衡量网站优化的效果或营销推广的效果，以及了解用户行为偏好，时常要对访问路径进行分析。
用户访问路径的可视化通常使用桑基图。如下图所示，该图可真实还原用户的访问路径，包括页面跳转和页面访问次序。
桑基图需要我们提供每种页面跳转的次数，每个跳转由source/target表示，source指跳转起始页面，target表示跳转终到页面。

桑基图

用户路径分析，顾名思义，就是指用户在APP或网站中的访问路径。为了衡量网站优化的效果或营销推广的效果，以及了解用户行为偏好，时常要对访问路径进行分析。
用户访问路径的可视化通常使用桑基图。如下图所示，该图可真实还原用户的访问路径，包括页面跳转和页面访问次序。
桑基图需要我们提供每种页面跳转的次数，每个跳转由source/target表示，source指跳转起始页面，target表示跳转终到页面。

建表语句

CREATE EXTERNAL TABLE ads_page_path
(
    `dt`          STRING COMMENT '统计日期',
    `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `source`      STRING COMMENT '跳转起始页面ID',
    `target`      STRING COMMENT '跳转终到页面ID',
    `path_count`  BIGINT COMMENT '跳转次数'
) COMMENT '页面浏览路径分析'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_page_path/';

完整sql

 insert overwrite table ads_page_path
 select * from ads_page_path
 union
select
        '2020-06-14',
        recent_days,source,target,
       count(*) path_count
from (
         select
             -- 为了避免产生闭环，为每个起点和终点按照session的访问顺序，添加序号
             concat('step-', rn, ':', source)     source,
             concat('step-', rn + 1, ':', target) target,
             recent_days

         from (
                  select
                      -- source就是 page_id
                      -- target要取 下一行的page_id
                      page_id                                                                        source,
                      lead(page_id, 1) over (partition by session_id,recent_days order by view_time) target,
                      session_id,
                      recent_days,
                      row_number() over (partition by session_id,recent_days order by view_time)     rn
-- 一个页面的一次访问是一行
                  from dwd_traffic_page_view_inc
                           lateral view explode(`array`(1, 7, 30)) tmp as recent_days
-- 只能统计截止到6-14日，当前1日的统计指标
                  where dt = '2020-06-14'
                    and dt > date_sub('2020-06-14', recent_days)
              ) t1
     ) t2
group by recent_days,source,target;

前提

页面日志表 ：dwd_traffic_page_view_inc :一次页面的一次访问是一行
今天是 2020-06-14

步骤

1. 获取2020-06-14的数据
2. 复制三份数据,排除 不合格的数据

lateral view explode(`array`(1, 7, 30)) tmp as recent_days

...

and dt > date_sub('2020-06-14', recent_days)

sql
运行效果

3. 构造 source target
   跨行取值（运用窗口函数）

   
   窗口函数，跨行取值

运行结果

4. 存在问题 路径中存在闭环

   
   image.png
   

   解决方式：加行号

   
   sql
   运行结果

sql
运行结果

5. 分组

   
   分组sql
6. 写插入语句
   见上方完整sql