SqlBoy:分组问题

23 篇文章 1 订阅

公众号(阿龙学堂):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;

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

阿龙学堂

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值