sql语句实现最大连续登陆天数

天数"/>

sql语句实现最大连续登陆天数

sql语句实现最大连续登陆天数：

问题描述：user_login 表中存储着不同用户的登陆时间，请用1条sql语句求出每位用户的最大连续登陆天数。

1.我们先建表，为了方便，我们直接用sysdate+n来填充数据：

create table user_login(user_id number,visit_date date
);insert into user_login values(1, sysdate);
insert into user_login values(1, sysdate + 1);
insert into user_login values(2, sysdate + 2);
insert into user_login values(2, sysdate + 3);
insert into user_login values(1, sysdate + 4);
insert into user_login values(1, sysdate + 5);
insert into user_login values(1, sysdate + 6);
insert into user_login values(1, sysdate + 7);
commit;select * from user_login;

执行 select * from user_login;语句查看数据：

 2.按照用户id分组、登陆时间升序 ，使用排名函数row_number（）来给每个用户的登陆时间一个连续升序的序号，同时输出用户登陆时间减去row_number。

select a.*,row_number() over(partition by a.user_id order by visit_date) rk,a.visit_date -(row_number() over(partition by a.user_id order by visit_date)) subfrom user_login a;

运行代码结果如下：

 我们可以观察到，如果用户连续登陆，那么在连续登陆的那一段时间，用户登陆时间减去row_number的值，也就是sub值，是相同的，那么接下来使用user_id,sub分组计数就能得到连续登陆时间了。这里是解题的关键，原理是这样的：如果用户连续登陆，那么相邻的两天date数值一定是相差1的，而row_number()排名函数的公差也是1，如果在一段连续时间内登陆，那么他们之间的差值一定是相等的。

这里实际上有一些可以探讨的地方，笔者在这里走了一些弯路，visit_date - row_number()这里，笔者一开始想到的是把visit_date用to_char()转换成字符型，再用to_number（）转换成数字型，再去相减，如果使用这种方法会出现一个问题：在月末时，date型转换到number型之后，数字就不是连续增加了，如：

所以这里是不能使用强制类型转换的，只能使用date - number = date这种形式。

3.按 user_id , sub 同时分组进行计数：

select b.user_id, count(*) cntfrom (select a.*,row_number() over(partition by a.user_id order by visit_date) rk,a.visit_date -(row_number() over(partition by a.user_id order by visit_date)) subfrom user_login a) bgroup by user_id, sub;

 结果如下：

可以看出 1用户有两次连续登陆，1次连续登陆2天，1次连续登陆4天；2用户有一次连续登陆，连续登录2天。

4.按user_id 分组求出最大值即可。 

select c.user_id, max(cnt) 最大连续登陆天数from (select b.user_id, count(*) cntfrom (select a.*,row_number() over(partition by a.user_id order by visit_date) rk,a.visit_date -(row_number()over(partition by a.user_id order by visit_date)) subfrom user_login a) bgroup by user_id, sub) cgroup by user_id;

结果：

5.一些探究

我们可不可以使用（登陆时间-上次登陆时间）和（row_number - 上一次的 row_number）相比来判断用户是否连续登陆呢？ 

 想要实现上述想法，需要使用lag位移函数来进行操作：

select b.*,lag(b.visit_date, 1) over(partition by user_id order by visit_date) last_date,lag(b.rk, 1) over(partition by user_id order by visit_date) last_rk,b.visit_date - lag(b.visit_date, 1) over(partition by user_id order by visit_date) date_sub,b.rk - lag(b.rk, 1) over(partition by user_id order by visit_date) rk_subfrom (select a.*,row_number() over(partition by user_id order by visit_date) rkfrom user_login a) b;

结果：

 可以看到，如果date_sub 和 rk_sub相等的话，就代表这一天和前一天的日期是相连的，即连续登陆，问题好像解决了。

但是到这一步之后，我们实际上是没有办法按照user_id 和 差值相等 这两个一起分组的，因为没有办法区分出不同的时间段，如果我们和第一种方法一样，按照user_id 和 date_sub - rk_sub 来进行分组计数，实际上是把每个用户所有连续登陆天数加起来（少了n次，n代表连续登陆的次数，如两次连续登陆，一次2天，一次3天，那么n就等于2。）

其实想想，这样的实现方式或许也有其一定的意义，我记得最早的时候QQ的连续登陆天数计算就是第一种，中间断了就不算，现在好像是第二种，即使中间断了，还是算连续登陆，不过也有可能是现在QQ一直手机在线，所以才一直算连续吧。