mysql 登录_使用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()，为了后续代码简洁，将执行的结果存到新表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取最小值就是起始登陆日期，取最大值就是连续登录的最后一天日期,每个组进行计数得到的是连续登录的天数。代码如下：

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

步骤一：使用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+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;

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

结果如下：

END

连续登录的相关问题就写到这里了，主要是用到rank()和lead()两个窗口函数，大家如果之前被此类问题困扰，可以详细看看。如果在数据分析上有任何疑问，欢迎交流讨论，下方扫码进群~