使用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,辅助日期列;

最后结果如下：

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

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()两个窗口函数，大家如果之前被此类问题困扰，可以详细看看。如果还有其他相关问题，可以留言