HQL - 分析用户会话行为轨迹

水善利万物而不争，处众人之所恶，故几于道💦

1. 数据描述

有如下用户行为数据，第一列是用户id（user_id），第二列是访问时间（action_time），第三列是访问页面（page），列分割符是制表符（\t）。

1001	2020-09-10 10:21:21	home.html
1001	2020-09-10 10:28:10	good_list.html
1001	2020-09-10 10:35:05	good_detail.html
1001	2020-09-10 10:42:55	cart.html
1001	2020-09-10 11:35:21	home.html
1001	2020-09-10 11:36:10	cart.html
1001	2020-09-10 11:38:12	trade.html
1001	2020-09-10 11:38:55	payment.html
1002	2020-09-10 09:40:00	home.html
1002	2020-09-10 09:41:00	mine.html
1002	2020-09-10 09:42:00	favor.html
1003	2020-09-10 13:10:00	home.html
1003	2020-09-10 13:15:00	search.html

2. 需求描述

分析用户每个会话的行为轨迹（如果上一次访问与本次访问时间超过半小时，则认为是新的会话；行为轨迹就在一个会话里标出每个页面的访问顺序就行了）

3. 思路分析

上面的四个步骤分别对应下面代码的 t1、t2、t3 临时表，SQL里面同样也写了注释

4. 完整代码

with t1 as (

    select user_id,
           action_time,
           page,
           -- 1. 先增加一列，用来表示上次访问的时间（以user_id分区，访问时间排序，取前一条数据的时间）
           lag(action_time) over (partition by user_id order by action_time) before_time
    from action_session
),
     t2 as (
         select user_id,
                action_time,
                page,
                -- 2. 找出每个新会话的边界，如果上次访问时间是null或者上次访问时间和这次访问时间间隔大于30分钟就是新会话的开始
                --     找到开始的话，搞一个session_id之类的东西，就用user_id和时间戳拼一下算了，如果不是新会话的开始就给个null
                --        unix_timestamp(date[, pattern]) 参数是：时间，格式
                if(before_time is null or unix_timestamp(action_time) - unix_timestamp(before_time) > 30 * 60,
                   concat(user_id, '_', unix_timestamp(action_time)), null) session_point
         from t1
     ),
     t3 as (
         select user_id,
                action_time,
                page,
                -- 3. 经过上面的处理，session_point那列里 每个会话开始已经有一个字符串了
                --    下面就是要把一个会话里的session_point都赋值为一样的，用last_value(a,b)取最后一个值，第一个参数是哪列，第二个参数是是否跳过null值
                --     以user_id分区，action_time排序 跳过null，取最后一个值为每行数据赋值
                last_value(session_point, true) over (partition by user_id order by action_time) session_id
         from t2
     )
select session_id,
       user_id,
       action_time,
       page,
       -- 4，编号，体现出每个会话的访问顺序（行为轨迹）  这里要以session_id分区了 ，因为是每个会话中的行为轨迹
       row_number() over (partition by session_id order by action_time) rn
from t3

5. Hive函数总结

lag() 访问前一行的数据。

if(a or b, c ,d)多条件判断，a满足或者b满足，取c值，否则取d值

unix_timestamp()将指定的时间转换为自1970-01-01以来的秒数，可以有两个参数第二个参数是时间格式 yyyy-MM-dd HH:mm:ss

from_unixtime(3423211234,‘yyyy-MM-dd HH:mm:ss’)将时间戳转换为时间类型

last_value(session_point,true)返回窗口内的最后一个值，第二个参数的含义是：是否跳过NULL值，默认不跳过