用SQL进行session分析

填充空白 2022-09-25

955

本文包括用SQL进行session分析并作拆解，在下次文中会做下留存分析。

碎碎念：曾经有个面试让我写个session的查询，当时只写了一个简单的逻辑后来想想好像也不是特别对，很长一段时间都觉得是在为难我。当然现在坦然面对之。有时候会觉得，当场写SQL好像并不能检验出一个人会不会写，更多的还是考验人的能力，所以能够把逻辑和思路写好已经很不错了。

以下为原始数据日志图。

图１：原始数据日志图

希望做session分析。切割时长为200s,即一个用户session内相邻2个事件直接时长超过200s认为是一次新的访问。当时长超过200s时为新的session.以下为切割后的图。图中包括每个会话的唯一id，会话时长，会话深度，会话内每个事件的位置，每事件内时长。

图２：希望做切割后的图

有了这些数据后，即可进一步统计session会话数，会话用户数，会话深度分布，会话内每个事件的时长情况。本文仅对从图１如何得到上述图２中的数据做详细的拆解。

希望大家最近都碰到好事儿。

session分析思路

对事件做切割。每个用户的事件按时间升序排列，当事件时长超过200s时，为一个新的session。获取每个session的第一个事件。会话的开始时间，结束时间。

获取每个事件前边一个事件的时间， lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time
当前序事件时间戳为null，或者每个事件的时间戳减去前序事件时间差超过200s,则为1，表示开始了一个新的session。case when a.end_time - a.begin_time<200 then a.end_time-a.begin_time else null ｅｎｄ
当开始一个新的session时获取前一个事件的时间戳作为上一个session的结束时间。则有每个session开始时间结束时间，可对每用户的session进行排序

查找每个session内的所有事件，计算每个session的时长，会话深度，会话内每个事件的位置，事件时长

计算每个事件的时长。获取每个事件的后一个事件的时间戳。每个事件的时间戳减去后续事件时间差小于200s时将作为该事件的时长。
将每个事件与所在session进行匹配。对用户的事件按照所属session进行排序获得其所在位置。
将每个事件与所在session进行匹配后，可获得每个会话的时长，所属会话窗口可计算会话深度，会话时长。所属位置，事件时长

分析思路拆解

第一步：对事件做切割。

获取每个事件前边一个事件的时间，如下SQL代码

SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS end_time,
  lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time
  FROM events WHERE date='2021-12-21'

结果如下：

当前序事件时间戳为null，或者每个事件的时间戳减去前序事件时间差超过200s,则为1，表示开始了一个新的session。

当开始一个新的session时获取前一个事件的时间戳作为上一个session的结束时间。则有每个session开始时间结束时间，可对每用户的session进行排序。

SELECT
user_id,event,time,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,1 , 0) as se,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc),null) as beforesession_endtime,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,EXTRACT(EPOCH FROM time),null) as currentsession_starttime


FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS end_time,
  lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time
  FROM events WHERE date='2021-12-21')a
order by user_id,time asc

结果如下

筛选每个se=1表示，筛选出了每个ｓｅｓｓｉｏｎ的开始的第一个事件，开始时间，获取session结束时间。并给每用户的session排序进行编号。仅为了方便查看。并进行后续统计。

select user_id,event,time,
currentsession_starttime,
lead(beforesession_endtime,1) over (PARTITION BY user_id ORDER BY time asc) as currentsession_endtime,
row_number() over (partition by user_id order by time asc) as session_id
from
(SELECT
user_id,event,time,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,1 , 0) as se,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc),null) as beforesession_endtime,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,EXTRACT(EPOCH FROM time),null) as currentsession_starttime


FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS end_time,
  lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time
  FROM events WHERE date='2021-12-21')a
  order by user_id,time asc
)sess where se=1

结果如下图所示：

至此，每用户不同session有了id，并有了会话开始时间，会话结束时间。

第二步，查找每个session内的所有事件。

首先计算好每个事件的时长。获取每个事件的后一个事件的时间戳。每个事件的时间戳减去后续事件时间差小于200s时将作为该事件的时长。

 SELECT
user_id,event,time,
a.end_time - a.begin_time,EXTRACT(EPOCH FROM time) as time1,
case when a.end_time - a.begin_time<200 then a.end_time-a.begin_time else null end as len
FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS begin_time,
  lead(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS end_time
  FROM events WHERE date='2021-12-21')a
order by user_id,time asc

结果如下：

将每个事件与所在session进行匹配。对用户的事件按照所属session进行排序获得其所在位置。也就是与第一步中的每个sessioｎ匹配。
其实是比较容易的。因为第一步我们知道了每个会话的开始时间结束时间。那么只要在该用户某个会话的时间范围内的事件就是这个会话内的事件。
sess_1st.user_id=a.user_id and a.time1>=sess_1st.currentsession_starttime and a.time1<=sess_1st.currentsession_endtime

‍

其中，这里的sess_1st表为第一步中生成的会话表，包含每个会话的user_id,会话id，会话开始时间，结束时间。这里的ａ表为每个事件计算事件时长后的表。

２表关联后即得到，每个事件所属会话，会话id，会话开始时间，会话结束时间，及该事件的时长。在该会话窗口内计算，可得到该事件在会话中所属位置，会话深度。

＃＃此框内代码不可运行，仅展示上述表述的步骤涉及的内容
sess_1st.currentsession_starttime,
sess_1st.currentsession_endtime,
sess_1st.currentsession_endtime-sess_1st.currentsession_starttime as sess_length,
a.time,a.time1,
row_number() over (partition by sess_1st.user_id,sess_1st.session_id order by a.time asc) as sess_position,
count() over (partition by sess_1st.user_id,sess_1st.session_id) as sess_depth,a.len

完整session切割，从图１得到图２的查询如下，运行即可得到如图２希望的查询结果。

select sess_1st.user_id,
concat(cast(sess_1st.user_id as string),'-',cast(sess_1st.session_id as string)) as session_id,
sess_1st.currentsession_starttime,
sess_1st.currentsession_endtime,
sess_1st.currentsession_endtime-sess_1st.currentsession_starttime as sess_length,
a.time,a.time1,
row_number() over (partition by sess_1st.user_id,sess_1st.session_id order by a.time asc) as sess_position,
count() over (partition by sess_1st.user_id,sess_1st.session_id) as sess_depth,a.len


from
(select user_id,event,time,
currentsession_starttime,
lead(beforesession_endtime,1) over (PARTITION BY user_id ORDER BY time asc) as currentsession_endtime,
row_number() over (partition by user_id order by time asc) as session_id
from
(SELECT
user_id,event,time,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,1 , 0) as se,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc),null) as beforesession_endtime,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>200,EXTRACT(EPOCH FROM time),null) as currentsession_starttime


FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS end_time,
  lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time
  FROM events WHERE date='2021-12-21')a
  order by user_id,time asc
)sess where se=1


)sess_1st




left join
(  SELECT
user_id,event,time,
a.end_time - a.begin_time,EXTRACT(EPOCH FROM time) as time1,
case when a.end_time - a.begin_time<200 then a.end_time-a.begin_time else null end as len
FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS begin_time,
  lead(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS end_time
  FROM events WHERE date='2021-12-21')a
order by user_id,time asc
)a on sess_1st.user_id=a.user_id and a.time1>=sess_1st.currentsession_starttime and a.time1<=sess_1st.currentsession_endtime  


order by a.user_id,a.time asc

祝大家好运～

文章转载自填充空白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

用SQL进行session分析

评论