SqlBoy:分组问题

公众号（阿龙学堂）：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) lagtsfrom test2; 记为 t1得到：1001    17523641234 01001    17523641256 175236412341001    17523641334 175236412561001    17523641534 175236413341001    17523641544 175236415341001    17523641638 175236415441001    17523641654 175236416381002    17523641278 01002    17523641434 175236412781002    17523641634 17523641434

第二步：将当前行时间数据减去上一行时间数据，得到两行数据的时间差

select    id,    ts,    ts-lagts tsdifffrom t1; 记为 t2得到：1001    17523641234 175236412341001    17523641256 221001    17523641334 781001    17523641534 2001001    17523641544 101001    17523641638 941001    17523641654 161002    17523641278 175236412781002    17523641434 1561002    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     11001    17523641256     11001    17523641334     21001    17523641534     31001    17523641544     31001    17523641638     41001    17523641654     41002    17523641278     11002    17523641434     21002    17523641634     3

第四步：最终将SQL拼接在一起

select    id,    ts,    sum(if(tsdiff>=60,1,0)) over(partition by id order by ts) groupidfrom(    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;