求用户登录流水表中的用户连续登录天数

有一个用户登录流水表结构如下：

create table hy_login_flow(
    id number(4,0) not null primary key,
    name nvarchar2(20) not null,
    logindate date not null)

这样给它充值：

insert into hy_login_flow(id,name,logindate) values('1','A001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('2','A001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('11','A001',to_date('2020-01-03','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('3','A001',to_date('2020-01-05','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('4','A001',to_date('2020-01-06','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('5','B001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('6','B001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('7','B001',to_date('2020-01-05','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('8','C001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('9','C001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('10','D001',to_date('2020-01-07','yyyy-MM-dd'));

然后我们观察这些记录：

可以发现，A001用户从2020-1-1～2020-1-3连续登录了三天，从2020-01-05～2020-01-06连续登录了两天；

B001用户从2020-1-1-2020-1-2连续登录了两天，在2020-01-05只登录了一天；

C001用户从2020-1-01～2020-01-02连续登录了两天；

D001用户只在2020-01-07登录了一天。

我们的任务是编写sql把用户连续登录的天数找出来。

 

首先，我们按用户分组，按登录时间升序排列一下，因为不同用户的记录毫无关系。

 

观察上表，我们可以发现，连续登录的用户，他的logindate减去seq是一个定数，这是解决问题的关键所在。

于是我们再查了一次：

多出的BasicDate列就是用logindate减去seq的差值，我们可以发现连续登录的记录，basicdate都是一样的。

接下来就好办了，按name和basicdate分组，求记录数就可以了。

最终SQL：

select b.name,count(*) from
(select a.*,a.logindate-a.seq as basicdate from
  (select row_number() over (partition by name order by logindate) as seq,name,logindate from hy_login_flow) a) b
group by b.name,b.basicdate
order by b.name

查询结果：

 

本文用到的全部SQL：

create table hy_login_flow(
    id number(4,0) not null primary key,
    name nvarchar2(20) not null,
    logindate date not null)
    
insert into hy_login_flow(id,name,logindate) values('1','A001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('2','A001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('11','A001',to_date('2020-01-03','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('3','A001',to_date('2020-01-05','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('4','A001',to_date('2020-01-06','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('5','B001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('6','B001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('7','B001',to_date('2020-01-05','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('8','C001',to_date('2020-01-01','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('9','C001',to_date('2020-01-02','yyyy-MM-dd'));
insert into hy_login_flow(id,name,logindate) values('10','D001',to_date('2020-01-07','yyyy-MM-dd'));

commit;

select * from hy_login_flow
    
select row_number() over (partition by name order by logindate) as seq,name,logindate from hy_login_flow

select a.*,a.logindate-a.seq as basicdate from
(select row_number() over (partition by name order by logindate) as seq,name,logindate from hy_login_flow) a


select b.name,count(*) from
(select a.*,a.logindate-a.seq as basicdate from
  (select row_number() over (partition by name order by logindate) as seq,name,logindate from hy_login_flow) a) b
group by b.name,b.basicdate
order by b.name

 

参考资料：

1.https://bbs.csdn.net/topics/396162708?page=1#post-411014646 引发问题的帖子。

2.https://blog.csdn.net/padluo/article/details/81113438  受启发的文章，在此感谢作者无意中的指点。

--2020-03-19--