面试经典sql(大数据)：连续登陆问题

【题目】：要求查询连续3天登录的用户

+v同q：1594007516 一起讨论技术问题。将我6年大数据一线项目（广告，小说，移动数仓）分享给你。适合刚毕业或参加完培训没有实际工作经验的人员。

一、构造"用户登录表"数据

字段说明

use_id：用户id

login_date：登录时间

用户登录日志表

二、思路解析及方法

1、使用排序窗口函数

-- 窗口分组排序 SELECT *, row_number() over(partition by use_id order by login_date) as rn FROM test

窗口分组排序

细心的小伙伴应该发现了，如果某个用户是连续登录，那么他的login_date(登录时间)-rn(排序号)的日期应该是相等的。

SELECT *, DATE_SUB(login_date,INTERVAL rn day) as ds FROM( SELECT *, row_number() over(partition by use_id order by login_date) as rn FROM test) t

连续登录

解析到这里，最后groupby一下，这道题就完成了，小伙伴们可以先不要看下面的完整代码，自己尝试动手把剩余的部分写出来。

2、使用偏移窗口函数

首先思考，求3天连续登录的用户，让用户登录时间往前偏移3天，只要往前偏移3天的日期正好等于3天前日期，就说明该用户是连续3天登录的用户。可能这样表述比较抽象，我们直接看图说话。

-- 这里需要注意orderby需要降序 -- lead偏移2天，因包含开始偏移日期，所以连续三天只需要偏移2天即可 -- （这里需按实际情况定义“连续”） SELECT use_id, login_date, lead(login_date,2) over(partition by use_id order by login_date desc) rn FROM test

偏移2天数据

SELECT * FROM (SELECT use_id, login_date, lead(login_date,2) over(partition by use_id order by login_date desc) rn FROM test) t WHERE DATE_SUB(cast(login_date as date),INTERVAL 2 DAY)=cast(t.rn as date) AND t.rn is not null

连续3天登录的用户

最后需要groupby或者去重即可得到连续登录的用户id

三、完整代码

1、连续登录问题（使用排序窗口函数）

-- 连续登录问题（使用排序窗口函数） SELECT t.use_id, DATE_SUB(t.login_date,INTERVAL t.rn DAY) as date, COUNT(1) as counts FROM (SELECT use_id, login_date, row_number()over(partition by use_id order by login_date ) as rn FROM test) t GROUP BY t.use_id,DATE_SUB(t.login_date,INTERVAL t.rn DAY) HAVING COUNT(1)>=3

连续3天登录的用户

2、连续登录问题（使用偏移窗口函数）

SELECT t.use_id -- t.rn -- count(distinct t.use_id) FROM (SELECT use_id, login_date, lead(login_date,2) over(partition by use_id order by login_date desc) rn FROM test) t WHERE DATE_SUB(cast(login_date as date),INTERVAL 2 DAY)=cast(t.rn as date) AND t.rn is not null GROUP BY use_id

四、总结

通过以上分析，想必大家以后遇到类似问题应该不会手足无措，SQL没有速成的路径，唯有结合业务场景多练习才能熟练掌握，同时解题时尝试用不同方式实现，有利于知识的巩固和提高。+v:1594007516