户行为记录分割
其实这个计算方法主要用在我们计算用户在我们的网站上或者应用上的停留时间，或者是计算用户在某一个页面上的停留时间，其实在前面学习Hive语法之窗口函数lead和lag 窗口函数的时候我们也介绍过如何计算用户在某一个页面上的停留时间，就是通过用户进入下一个页面的时间减去用户进入当前页面的时间即可，因为我们前面主要讲的是窗口函数的应用，所以就没有考虑这样计算到底合适吗这个问题，今天我们就来看一下这个问题
session 分割的背景
我们先看一下为什么要进行session 分割，假设我们现在要计算用户在我们的APP 上的停留时间，我们假设我们不做session 分割，那么我们的停留时间就是用户最后一次的访问时间减去第一次的访问时间，其实这是不合理，因为用户可能在中间时间离开了我们的应用，其实如果真的是这种情况也好说，只要我们能捕捉到用户离开的事件，然后计算每次离开的和上次进来之间的时间差，我们可以将其称之为会话，从而将多个会话的时长进行相加即可。
但是很多时候我们捕捉不到用户的离开事件，或者用户不是正常离开，例如用户直接清理了后台这种情况下我们就需要构建会话，从而进行计算，也就是将用户的一系列上报上来的数据进行session 分割，从而计算用户在平台上的停留时长。
构建session 的核心是我们可以将用户行为按照一定的时间进行划分，和flink 的会话窗口有点像，这里我们的规则就是如果两个行为之间的时候超过30 分钟我们，认为这两条数据是属于两个不同的会话的，我们将这个时间间隔称之为会话超时时间，一般我们APP 的会话超时时间是30分钟，web 端的话是10分钟
准备数据
首先我们需要准备一批用户访问的数据，也就是一些列的用户行为数据，因为我们是通过自己构建session 的，所以我们不关注这些数据的事件是什么，当然你要是能将数据的行为事件和物理分割相结合那么构建出来的session 就会更准确
1 2020-11-20 10:01:51 url1
1 2020-11-20 10:05:51 url1
1 2020-11-20 10:43:51 url2
1 2020-11-20 10:46:51 url1
1 2020-11-20 10:49:51 url2
1 2020-11-20 10:50:51 url2
复制代码你可以将这些数据导入到hive 你也可以像下面这样进行使用
/*
用户ID：uid
页面url：url
访问时间：event_time
*/
WITH user_log AS(
SELECT
uid,
datetime(event_time) event_time,
event_key
FROM
(
SELECT '1' AS uid, '2020-11-20 10:01:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:05:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:43:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:46:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:49:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:50:51' AS event_time, 'url2' AS url
)tmp
)
SELECT * FROM user_log;
复制代码构建session
有了数据之后我们就可以构建会话了，首先为了计算两个行为之间的时间间隔，我们需要将用户数据按照访问时间进行排序，从而计算两个相邻行为之间的时间间隔，从而判断是不是属于不同会话
按照访问时间进行排序并且编号
需要注意的实我们是按照用户先进行分区再排序的，因为会话是针对单个用户而言的
SELECT
*，
ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) AS act_id
FROM
user_log;
复制代码uid event_time url act_id
+----+-----------+----+-------+
1 2020-11-20 10:01:51 url1 1
1 2020-11-20 10:05:51 url1 2
1 2020-11-20 10:43:51 url2 3
1 2020-11-20 10:46:51 url1 4
1 2020-11-20 10:49:51 url2 5
1 2020-11-20 10:50:51 url2 6
复制代码计算会话窗口
接下来我们在上面的基础上按照我们的计算规则计算如果当前行为的时间和它的上一个行为的时间间隔如果超过了30 分钟我们将当前行为可以看做是会话的开始，如果当前行为没有上一个行为也就是第一条数据我们也将其看作是会话的开始，需要注意的是我们的
会话是针对单个用户而言的
select
a.uid,a.url,a.event_time,
if(b.event_time is null or datediff(a.event_time,b.event_time,'mi')>10,a.act_id,null) as session_id
from
order_data a
left join
order_data b
on
a.uid=b.uid and a.act_id=b.act_id+1
复制代码这里我们简单解释一下，如果session_id 不是null 则证明当前事件就是会话的开始，如果session_id 是null 则证明当前事件和上一个事件的时间间隔不超过30分钟，所以也就不是会话的开始，所以这里我们还要过滤出session_id不是null 的数据才是我们的会话的开始事件
select * from(
select
a.uid,a.act_id,a.event_time,
if(b.event_time is null or datediff(a.event_time,b.event_time,'mi')>10,a.act_id,null) as session_id
from
order_data a
left join
order_data b
on
a.uid=b.uid and a.act_id=b.act_id+1
)
where
session_id is not null
;
复制代码其实这一步从order_date 也就是排序后的数据计算session_id 我们也可以使用我们以前学习的窗口函数，这样就不需要自关联了,但是还是需要进行一次过滤的
select
uid,act_id,event_time
if(last_time is null or datediff(event_time,last_time,'mi')>10,act_id,null) as session_id
from(
select
uid,event_time,act_id,lag(event_time,1) over(partition by uid order by event_time ) as last_time
from
order_data
)
;
复制代码将每一个行为划分到特定的session 中去
前面我们为每一个行为都排了一个序号，这里我们将窗口的第一个行为的需要称为session_id,并且我们已经计算出了全部的seeion_id，session_id 就代表了每一个session ，同时也是每一个session 中的第一个行为数据。
接下来我们要做的就是将每一个行为划分到特定的session 中去，开始之前我们需要知道如何划分，我们已经知道session_id 代表的是这个session 中的act_id 最小值，那么我们就知道这个session 中的其他数据的act_id 肯定大于等于这个session 的session_id，等于是因为我们要将这个行为本身也算进去，因为某一个session 中可能只有一条数据就是它本身。
其实只有大于还不够，举个例子来说第三个session 中的数据的act_id 肯定是比第二个session 的session_id 大的，但是我们知道这些数据是属于第三个session 的，而不是第二个，这样我们就找到正确的限制条件当前数据所属的session_id 就是小于当前数据的act_id 的最大的session_id 。
可能比较难理解，直接上代码
select
a.*,b.session_id
from
order_data a
left join
session_data b
on
a.uid=b.uid
and a.act_id>=b.session_id
复制代码
这里我们只选出了a.act_id>=b.session_id 的数据例如act_id=6 的数据同时属于了 1 和3 这个session ，所以接下来我们要找出小于act_id并且最大的session_id 作为当前数据所属的session
其实我们只需要对上面计算出来的数据按照session_id倒序进行row_number 排序，选出序号为1 的数据即可
select
*
from (
select
uid,url,event_time,act_id,session_id,session_start_time,
row_number() over(partition by uid,act_id order by session_id desc ) as rn
from (
select
a.*,b.session_id,b.event_time as session_start_time
from
order_data a
left join
session_data b
on
a.uid=b.uid
and a.act_id>=b.session_id
)
)
where
rn=1
复制代码
其实到这里我们就将为我们全部的数据找到了它所属的session ,以及session 的开始时间,接下来我们就可以计算用户在平台上的停留时间了也就是多个session 的时间相加，其实我们可以看到一个session 的时长就是这个session 最后一条记录的时间减去第一条记录的时间
计算用户的停留时长
我们只需要分别计算出每一个session 的时长，然后将全部session 时长 相加即可
select
uid,sum(session_time) as page_duration
from(
select
uid,session_id,max(event_time) as session_end_time,min(event_time) as session_start_time,datediff(max(event_time),min(event_time),'ss') as session_time
from
user_session
group by
uid,session_id
)
group by
uid
复制代码uid page_duration
+----+--------------+
1 660
复制代码完整SQL
需要说明的是为了演示，我将sql 都按照我们的演示流程拆分成一段段的，你可以在使用的时候将其合并
WITH user_log AS(
SELECT
uid,
datetime(event_time) event_time,
url
FROM
(
SELECT '1' AS uid, '2020-11-20 10:01:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:05:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:43:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:46:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:49:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:50:51' AS event_time, 'url2' AS url
)tmp
),
order_data as(
SELECT
*，
ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) AS act_id
FROM
user_log
),
session_data as (
select * from(
select
a.uid,a.act_id,a.event_time,
if(b.event_time is null or datediff(a.event_time,b.event_time,'mi')>10,a.act_id,null) as session_id
from
order_data a
left join
order_data b
on
a.uid=b.uid and a.act_id=b.act_id+1
)
where
session_id is not null
),
user_session as(
select
*
from (
select
uid,url,event_time,act_id,session_id,session_start_time,
row_number() over(partition by uid,act_id order by session_id desc ) as rn
from (
select
a.*,b.session_id,b.event_time as session_start_time
from
order_data a
left join
session_data b
on
a.uid=b.uid
and a.act_id>=b.session_id
)
)
where
rn=1
)
select
uid,sum(session_time) as page_duration
from(
select
uid,session_id,max(event_time) as session_end_time,min(event_time) as session_start_time,datediff(max(event_time),min(event_time),'ss') as session_time
from
user_session
group by
uid,session_id
)
group by
uid
;
复制代码计算每个页面上的停留时长
其实计算每个页面的停留时长我们前面计算过，这次不同的是我们引入了session ,也就是是我们计算每个页面的停留时长是基于session 的，就是先在每个session 里计算出每个页面上的停留时长，再将其相加则是每个页面的停留时长，建议先看一下我们前面讲窗口函数lead 和lag 的文章
计算用户的离开当前页面的时间
需要注意的是我们的计算规则是基于一个特定的session 的
select
uid,url,event_time,session_id,act_id,next_actid,next_time,datediff(next_time,event_time,'ss') as pageview_duration
from (
select
uid,url,event_time,session_id,act_id,
lead(event_time,1) over(partition by uid,session_id order by act_id) as next_time,
lead(act_id,1) over(partition by uid,session_id order by act_id) as next_actid
from
user_session
)
where
next_time is not null
复制代码这里的next_time 就是离开当前页面的时间，这里我们过滤next_time 不为空是因为session 中的最后一条数据没有next_time
pageview_duration 就是在一个url 上的停留时间，但是需要注意的是一个session 内一个url 可能有多个时间
计算用户在每个页面上的停留时长
我们只需要将上面的pageview_duration 按照用户进行求和即可
select
uid,url,sum(pageview_duration) as pageview_duration
from (
select
uid,url,event_time,session_id,act_id,next_actid,next_time,datediff(next_time,event_time,'ss') as pageview_duration
from (
select
uid,url,event_time,session_id,act_id,
lead(event_time,1) over(partition by uid,session_id order by act_id) as next_time,
lead(act_id,1) over(partition by uid,session_id order by act_id) as next_actid
from
user_session
)
where
next_time is not null
)
group by
uid,url
复制代码完整SQL
需要说明的是为了演示，我将sql 都按照我们的演示流程拆分成一段段的，你可以在使用的时候将其合并
WITH user_log AS(
SELECT
uid,
datetime(event_time) event_time,
url
FROM
(
SELECT '1' AS uid, '2020-11-20 10:01:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:05:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:43:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:46:51' AS event_time, 'url1' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:49:51' AS event_time, 'url2' AS url
UNION ALL
SELECT '1' AS uid, '2020-11-20 10:50:51' AS event_time, 'url2' AS url
)tmp
),
order_data as(
SELECT
*，
ROW_NUMBER() OVER(PARTITION BY uid ORDER BY event_time) AS act_id
FROM
user_log
),
session_data as (
select * from(
select
a.uid,a.act_id,a.event_time,
if(b.event_time is null or datediff(a.event_time,b.event_time,'mi')>10,a.act_id,null) as session_id
from
order_data a
left join
order_data b
on
a.uid=b.uid and a.act_id=b.act_id+1
)
where
session_id is not null
),
user_session as(
select
*
from (
select
uid,url,event_time,act_id,session_id,session_start_time,
row_number() over(partition by uid,act_id order by session_id desc ) as rn
from (
select
a.*,b.session_id,b.event_time as session_start_time
from
order_data a
left join
session_data b
on
a.uid=b.uid
and a.act_id>=b.session_id
)
)
where
rn=1
)
select
uid,url,sum(pageview_duration) as pageview_duration
from (
select
uid,url,event_time,session_id,act_id,next_actid,next_time,datediff(next_time,event_time,'ss') as pageview_duration
from (
select
uid,url,event_time,session_id,act_id,
lead(event_time,1) over(partition by uid,session_id order by act_id) as next_time,
lead(act_id,1) over(partition by uid,session_id order by act_id) as next_actid
from
user_session
)
where
next_time is not null
)
group by
uid,url
;
复制代码总结
今天我们主要介绍了session 分割，整个过程并不复杂，但是需要搞清楚每一步的目的，然后我们基于session 开始进行我们的计算
![[桜井宁宁]COS和泉纱雾超可爱写真福利集-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/4d3cf227a85d7e79f5d6b4efb6bde3e8.jpg)
![[桜井宁宁] 爆乳奶牛少女cos写真-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/d40483e126fcf567894e89c65eaca655.jpg)
