公众号(阿龙学堂):SqlBoy:经典面试题-分组问题
一、需求
比如说,进入一个网站以后,可以连续的点击很多个页面,后台会记录用户的行为日志;如果T日上午连续点击几个页面后退出了网站,直到第二天的下午才再次进入网站,单单从时间线上来看,昨天退出的那条日志跟今天进入的那条日志是连在一起的,但这两条数据实际上并不是一个会话产生的,如果需要对这样的数据进行分组,将其分在两个不同的会话当中,应该怎么做呢?组与组之间的时间间隔应该是多少呢?
如下为电商公司用户访问时间数据
二、分析
这个问题可以看做:判断连续的两条数据是否属于同一个组(时间有序),这就涉及到当前行数据及前一行数据或者后一行数据的时间差是否在60秒以内,如果是就属于同一组,反之就不是同一组。
我们应该想到有两个窗口函数,用来获取当前行数据的前N行或者后N行数据:
返回位于当前行的前n行的expr的值:LAG(expr,n,defval);
返回位于当前行的后n行的expr的值:LEAD(expr,n,defval)
三、解法
第一步:按照id分组,将上一行时间数据下移,即将当前行的上一行时间移到当前行,如果前面没有数据,取默认值0
select
id,
ts,
lag(ts,1,0) over(partition by id order by ts) lagts
from test2; 记为 t1
得到:
1001 17523641234 0
1001 17523641256 17523641234
1001 17523641334 17523641256
1001 17523641534 17523641334
1001 17523641544 17523641534
1001 17523641638 17523641544
1001 17523641654 17523641638
1002 17523641278 0
1002 17523641434 17523641278
1002 17523641634 17523641434
第二步:将当前行时间数据减去上一行时间数据,得到两行数据的时间差
select
id,
ts,
ts-lagts tsdiff
from t1; 记为 t2
得到:
1001 17523641234 17523641234
1001 17523641256 22
1001 17523641334 78
1001 17523641534 200
1001 17523641544 10
1001 17523641638 94
1001 17523641654 16
1002 17523641278 17523641278
1002 17523641434 156
1002 17523641634 200
第三步:计算每个用户范围内从第一行到当前行tsdiff大于等于60的总个数(分组号)
select
id,
ts,
sum(if(tsdiff >= 60,1,0)) over(partition by id order by ts) groupid -- 这一行将得到从第一行到当前行的 sum(if(tsdiff >= 60,1,0)) 值
from t2;
得到:
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3
第四步:最终将SQL拼接在一起
select
id,
ts,
sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupid
from(
select
id,
ts,
ts-lagts tsdiff
from(
select
id,
ts,
lag(ts,1,0) over(partition by id order by ts) lagts
from test2
)t1
)t2;