sql 连续三天登陆_连续登录的那些事

   SQL连续登录这个问题在实际工作中遇到的情况比较多，结合窗口函数的使用会使SQL代码简洁易懂，下面我把最近刷题过程中遇到的连续登录这一类问题做一个总结。

首先解决SQL连续登录这一类问题核心的思路如下图所示

  假设用户是连续登陆的情况下，那么用登陆日期减去排名后的日期应该是一样的，连续登录的用户的日期是一个差值为1的等差数列。

下面的题用到的数据为，数据库为Mysql8.0以上的版本

insert into visitlog values('2020-01-02 09:30:05','pc',246731); insert into visitlog values('2020-01-03 09:30:05','pc',246731); insert into visitlog values('2020-02-01 09:30:05','pc',246731); insert into visitlog values('2020-02-02 09:30:05','pc',246731); insert into visitlog values('2020-04-01 09:30:05','pc',246731);insert into visitlog values('2019-12-01 09:30:05','pc',243186); insert into visitlog values('2020-01-01 09:30:05','pc',243186); insert into visitlog values('2020-02-01 09:30:05','pc',243186); insert into visitlog values('2020-02-02 09:30:05','pc',243186);insert into visitlog values('2019-11-01 09:30:05','pc',286325); insert into visitlog values('2019-12-01 09:30:05','pc',286325); insert into visitlog values('2020-01-01 09:30:05','pc',286325); insert into visitlog values('2020-02-01 09:30:05','pc',286325);insert into visitlog values('2020-01-01 09:30:05','pc',321678); insert into visitlog values('2020-01-02 09:30:05','pc',321678); insert into visit_log values('2020-01-03 09:30:05','pc',321678); insert into visitlog values('2020-01-04 09:30:05','pc',321678); insert into visitlog values('2020-01-05 09:30:05','pc',321678);

建表语句为

create table visitlog( logtime datetime not null ,terminal char(10) not null ,uid int not null )

Q1 求每一位用户的最早登陆日、最近登陆日、最大的连续登录天数

1求出每位用户的连续登录天数(第一题会把每一个分解步骤写清楚，便于理解)

  首先用Row_number函数对日期进行排序，最后用DATE_SUB函数计算logtime减去排名之后的日期，从这里可以看出只要是连续登录的用户fuzhulie这一列的日期都是一样的。

运行的结果为

2 以uid和fuzhulie作为分组依据，只要是连续登录的用户都会被分到一组里，在每一组里最小的日期就是用户的最早登录日期，最大的日期就是用户的最近登陆日期。

运行的代码为

运行的结果为

从上图可以看出每一个用户的登录日期和登录次数，

3 求每一个用户的最大登录次数时，只需要在最外面做一层嵌套，MAX(登录次数)即可

 (下面的这个代码没有考虑性能问题，只是为了说明白这个道理)

运行的代码为

运行的结果为

Q2：求解连续登录三天的用户都有哪些(其实用上面的方法可以直接求登陆三天的用户有哪些，只需要加一个where = 3作为限制条件即可)

1 这次用LEAD函数进行操作，对logtime进行向下偏移，偏移后的日期列命名为第三天登陆日期

运行的代码为

运行的结果为

  为什么lead()函数中偏移参数设为2吗？假设他三天连续登录，那么两个日期之间相差的行数为2，当lead( )进行偏移时是不包括它本身这一行的。

2 用date计算出第三次登录日期跟logtime的差值然后再加上1是否等于3，等于3证明该用户是连续登录三天的，为空值或者最后的结果都不是3，就说明用户不是连续三天登录的。

运行的代码为

运行的结果为

Q3 求连续三个月都连续登录的用户(注意是三个月，不是三天了)

在这里需要有一个相对的概念，就是我们需要以谁为基准去进行月份的偏移。如果是1-12月则取month的整数值进行计算即可，不过涉及到跨年就会麻烦一点所以要将yyyy-mm格式转化成连续的整数，以首单的第一个月的年份为基准，某个用户下单月份为201811,201812,201901,201902
其中基准(最小年份)是2018年，进行转化，则有：
201811 -> (2018-2018)\*12+11 = 11
201812 -> (2018-2018)\*12+12 = 12
201901 -> (2019-2018)\*12+1 = 13
201902 -> (2019-2018)\*12+2 = 14
然后再参照**Q1**的思路，对齐基准偏移然后计数。
根据上面的思路，查询到数据中最小的登陆日期为2019年11月

1 第一步，首先对月份进行提取，把月份变成一个相对的月份。运行的代码为

2 然后根据Q2的方法求出连续3个月登录的用户(用Q1的方法也可以)

运行的结果为

总结, Q1和Q2的代码有些啰嗦，只是为了把每一步展示清楚。如date_sub( )函数对日期进行加减时，如下图操作代码就会简洁不少。

拓展 试想一下怎样求解连续3 周登录的用户呢