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

vlambda
2020-09-21

使用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;

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

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

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

步骤一：首先对用户登录数据进行排序，这里使用窗口函数rank()，为了后续代码简洁，将执行的结果存到新表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一样的分到一个窗口里，每个窗口在针对登录日期升序排列，每个窗口内进行排序后最后拼接在一起，结果如下：

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

步骤二：用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() 是个日期运算函数，第一个参数是起始日期，第二个参数是起始日期减去几天，结果如下：

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

步骤三：根据上面得到的结果，用user_id和辅助日期列做分组字段，user_id和辅助日期列完全一样的被分到一个组，这个组是连续登录的，针对login_date取最小值就是起始登陆日期，取最大值就是连续登录的最后一天日期,每个组进行计数得到的是连续登录的天数。代码如下：

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

最后结果如下：

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

把所有代码整合在一起，不需要创建临时表的代码如下：

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()。

步骤一：使用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的窗口里，第四行是不能往后移动四行的，数据行数不够，所以返回空值。结果如下：

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

步骤二：用第五次登录日期 - login_date+1，如果等于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;

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

步骤三：筛选相差天数=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;