SQL的小测_表数据为用户uid和用户登录时间ts(单位:秒),对用户登录进行分组,两次登录时间不小-CSDN博客

本文链接：https://blog.csdn.net/Minervar/article/details/84660019

SQL小测

今天博主面试了字节跳动的商业分析岗。面试官手动出了一个SQL的小测试，当场博主没有做出来。面试完了复盘一下。
题面：有两列数据，用户Id和登录时间戳，原则是同一个ID相邻两次访问时间小于两分钟，记录为一次作弊行为。要求统计出用户ID和对于ID的作弊次数。

思路
首先，利用rank()函数对同一个用户ID进行登录时间的排序；
其次，在同一张表中匹配出下一次用户登录的时间，并计算出两次时间差；
最后，按照时间差小于两分钟的原则，使用count(*)函数记录用户作弊次数。
操作

数据来源：使用Excel中的randbetween()和floor函数生成用户ID，用rand()和now()生成登录时间保留数值和日期格式，存成作弊.csv文件，将数据导入到mySQL。
mySQL代码实现如下:

select User_id, count(*) from (select a.User_id, b.time - a.time as gap from
(select *, rank() over(partition by User_id order by time ASC) as timerank0 from 作弊) as a,
(select *, rank() over(partition by User_id order by time ASC) as timerank1 from 作弊) as b
where a.User_id = b.User_id and a.timerank0 = b.timerank1 - 1) as c where gap<=2 group by User_id

最近学习到了另外一个函数,lead这个窗口函数也是可以解决这个问题的。

select user_id, count(*)
from (select user_id, lead(time,1) over (partition by user_id order by time asc)  - time as gap ) tmp
where tmp.gap <=2
group by 1