沧州 中企动力提供网站建设,html代码翻译,南海区住房和城乡建设部网站,外贸型网站建设目录 问题#xff1a;SQL解答#xff1a; 问题#xff1a;
如下为某电商公司用户访问网站的数据#xff0c;包括用户id和访问时间两个字段。现有如下规则#xff1a;如果某个用户的连续的访问记录时间间隔小于60秒#xff0c;则属于同一个会话#xff0c;现在需要计算每… 目录 问题SQL解答 问题
如下为某电商公司用户访问网站的数据包括用户id和访问时间两个字段。现有如下规则如果某个用户的连续的访问记录时间间隔小于60秒则属于同一个会话现在需要计算每个用户有多少个会话。比如A用户在第1秒60秒200秒230秒有三次访问记录则该用户有2个会话其中第一个会话是第1秒和第60秒的记录第二个会话是第200秒和230秒的记录。 user_id ts
1001 16920000000
1001 16920000050
1002 16920000065
1002 16920000080
1001 16920000150
1002 16920000160SQL解答
先按用户分组、时间排序后取每行数据的前一行的时间然后判断当前行的时间与前一行时间的差值看是否在给定的范围内然后再做开窗累加就可以得到每个用户不同的会话编号了。思路如下图
user_idts判断与上一行差值是否小于60开窗累加当做会话编号A100A6000A20011A23001
with tmp as (select 1001 as user_id,16920000000 as tsunion allselect 1001 as user_id,16920000050 as tsunion allselect 1002 as user_id,16920000065 as tsunion allselect 1002 as user_id,16920000080 as tsunion allselect 1001 as user_id,16920000150 as tsunion allselect 1002 as user_id,16920000160 as ts
)selectuser_id,count(distinct user_group) as user_group_cnt
from
(selectuser_id,ts-- 开窗做累加,sum(flag) over(partition by user_id order by ts) as user_groupfrom(
selectuser_id,ts-- 判断当前行的时间与上一行的差值,if(ts-last_ts60,0,1) as flagfrom(
selectuser_id,ts-- 取当前行的上一个时间没有上一行就给自身的时间,lag(ts,1,ts) over(partition by user_id order by ts) as last_tsfrom tmp
)t1
)t1
)t1
group by user_id;
