使用MySQL窗口函数解决用户连续登录天数的问题

我们在工作中或者面试时,可能会遇到求出用户连续登录天数、连续签到天数等问题,这篇文章就是用窗口函数比较巧妙的解决这个问题的。文章比较长,建议先收藏后观看+_+

关于窗口函数的排名函数的使用方法,在之前的文章介绍过:窗口函数-排名函数

数据处理

如下代码是创建用户登录表,插入用户登录数据

create table user_login(
    user_id varchar(100),
    login_time datetime);

insert into user_login values
(1,'2016-11-25 13:30:45'),  
(1,'2016-11-24 13:30:45'),  
(1,'2016-11-24 10:30:45'),  
(1,'2016-11-24 09:30:45'),  
(1,'2016-11-23 09:30:45'),  
(1,'2016-11-10 09:30:45'),  
(1,'2016-11-09 09:30:45'),  
(1,'2016-11-01 09:30:45'),  
(1,'2016-10-31 09:30:45'),  
(2,'2016-11-25 13:30:45'),  
(2,'2016-11-24 13:30:45'),  
(2,'2016-11-23 10:30:45'),  
(2,'2016-11-22 09:30:45'),  
(2,'2016-11-21 09:30:45'),  
(2,'2016-11-20 09:30:45'),  
(2,'2016-11-19 09:30:45'),  
(2,'2016-11-02 09:30:45'),  
(2,'2016-11-01 09:30:45'),  
(2,'2016-10-31 09:30:45'),  
(2,'2016-10-30 09:30:45'),  
(2,'2016-10-29 09:30:45'); 

因为计算用户连续登录天数,需要先对登录时间进行处理,先转为日期格式后再去重(因为用户可能某天多次登录),为了后续代码方便,将处理的结果保存到新表user_login_date中

create table user_login_date(
    select distinct user_id, date(login_time) login_date from user_login);

#处理后的数据如下:
select * from  user_login_date;

在这里插入图片描述

计算连续登录天数

第一种情况:查看每位用户连续登陆的情况

没有具体需求,用户在某一段日期内可能出现多次连续登录,比如出现这周连续登录三天,上周连续登录四天的情况,需要将这些信息全部输出,最后结果输出四个字段,分别是用户ID、首次登录日期、结束登录日期、连续登录天数

首先对用户登录数据进行排序,这里使用窗口函数rank(),如果不会用的可以先看这篇文章:https://zhuanlan.zhihu.com/p/137908870

为了后续代码简洁,将执行的结果存到新表user_login_date_1里面,代码如下:

create table user_login_date_1(
    select *,rank() over(partition by user_id order by login_date) 排序
        from user_login_date);

#查看结果
select * from user_login_date_1;

代码解读:针对user_id分区,把user_id一样的分到一个窗口里,每个窗口在针对登录日期升序排列,每个窗口内进行排序后最后拼接再一起,结果如下:
在这里插入图片描述
接下来是最重要的一步,用login_date - 排序。比如1号用户第一次登录是2016-10-31,减去,1天是2016-10-30,第二次登录是2016-11-01,减去2天是2016-10-30,说明这两天是连续登录的;第四次登录是2016-11-10,减去4天是2016-11-6不等于2016-10-30,说明这次登录与第一次登录就不是连续的了,代码如下(结果还是存到新表user_login_date_2里):

create table user_login_date_2(
    select *,date_sub(login_date, interval 排序 day) 辅助日期列
        from user_login_date_1);

#查看结果
select * from user_login_date_2;

date_sub()是个日期运算函数,第一个参数是起始日期,第二个参数是起始日期减去几天,结果如下:
在这里插入图片描述

最后一步是根据上面得到的结果,用user_id和辅助日期列做分组字段,user_id和辅助日期列完全一样的被分到一个组,这个组是连续登录的,针对login_date取最小值就是起始登陆日期,取最大值就是连续登录的最后一天日期,每个组进行计数得到的是连续登录的天数。这样就完成需求了,如果对分组聚合不熟悉的可以看下我之前的文章:https://zhuanlan.zhihu.com/p/106767752

select user_id,
    min(login_date) 起始登录日期,
    max(login_date) 结束登录日期, 
    count(login_date) 连续登录天数 
  from user_login_date_2
    group by user_id,辅助日期列;

最后结果如下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5NBn6Xo6-1592029485950)(attachment:image.png)]

把所有代码整合在一起,不需要创建临时表的代码如下,比较长…

select user_id,
	min(login_date) 起始登录日期, 
	max(login_date) 结束登录日期, 
	count(login_date) 连续登录天数 
  from (select *,date_sub(login_date, interval 排序 day) 辅助日期列
			from (select *,rank() over(partition by user_id order by login_date) 排序
					from (select distinct user_id, date(login_time) login_date 
							from user_login) as a) as b) as c
	group by user_id,辅助日期列;

第二种情况:查出在某个时间段内连续登录天数>=5天的用户

这个需求可以用上面查询的结果进行筛选。但是仅针对这个需求,用上面的代码就特别麻烦,下面介绍一个简单的办法,引用一个新的静态窗口函数lead()

select *,lead(login_date,4) over(partition by user_id order by login_date) 第五次登录日期
 from user_login_date;

lead函数有三个参数,第一个参数是指定的列(这里用登陆日期),第二个参数是当前行向后几行的值,这里用的是4,也就是第五次登录的日期,第三个参数是如果返回的空值可以用指定值替代,这里没有使用第三个参数。 over语句里面是针对user_id分窗,每个窗口针对登录日期升序。代码得到结果如下,可以看到新列的第四行为空值,因为在user_id为1的窗口里,第四行是不能往后移动四行的,数据行数不够,所以返回空值。
在这里插入图片描述

用第五次登录日期 - login_date,如果等于5,说明是连续登录五天的,如果得到空值或者大于5,说明没有连续登录五天,代码和结果如下:

select *,datediff(第五次登录日期,login_date) 相差天数
    from (select *,lead(login_date,4) over(partition by user_id order by login_date) 第五次登录日期
             from user_login_date) as a;

在这里插入图片描述
最后一步,筛选相差天数=5的,针对user_id去重,完成需求

select distinct user_id 
    from (select *,datediff(第五次登录日期,login_date)+1 相差天数
            from (select *,lead(login_date,4) over(partition by user_id order by login_date) 第五次登录日期
                     from user_login_date) as a) as b 
where 相差天数=5;

在这里插入图片描述

连续登录的相关问题就写到这里了,主要是用到rank()和lead()两个窗口函数,大家如果之前被此类问题困扰,可以详细看看。如果还有其他相关问题,可以留言

  • 9
    点赞
  • 46
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值