SQL—用户连续登录问题

前言

如果想从事数据相关工作,SQL毋庸置疑是必备技能,而“连续登录”问题作为经典sql面试题,一直是高频考点,本文将用两种不同的窗口函数,解答该类问题,如果有不清楚什么窗口函数的小伙伴,可以看看我之前写过窗口函数的文章。
SQL干货|为你打开一扇窗—窗口函数

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

一、构造"用户登录表"数据
  • 字段说明
  • 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没有速成的路径,唯有结合业务场景多练习才能熟练掌握,同时解题时尝试用不同方式实现,有利于知识的巩固和提高。

以上,欢迎小伙伴们一起交流学习,共同进步,谢谢!

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐