SQL-获取至少连续登录三天以上的用户_使用开窗函数找出30天分区内连续三天产生数据的用户

首先准备一下数据

create table login_record (
  userid int ,
  time varchar2(20)
);
 
insert into login_record values (1,'2020-04-18');
insert into login_record values (1,'2020-04-19');
insert into login_record values (1,'2020-04-20');
insert into login_record values(1,'2020-04-21');
insert into login_record values(2,'2020-04-16');
insert into login_record values(2,'2020-04-17');
insert into login_record values(2,'2020-04-18');
insert into login_record values(3,'2020-04-18');
insert into login_record values(4,'2020-04-18');
insert into login_record values(5,'2020-04-18');
insert into login_record values(6,'2020-04-18');
insert into login_record values(7,'2020-04-18');

数据样子

用户每天可能登录多次，现在的数据是进行过去重的，所以目前是用户每天只登陆一次的

现在我们可以使用lead() over()开窗函数 用于获取当前行的下一行 展示如下

代码如下

select userid from (
    select userid,time,lead(time) over (partition by userid order by time) as time1
from login_record
                       )
group by userid,to_date(time,'yyyy-mm-dd')- to_date(time1,'yyyy-mm-dd')
having count(*) >= 2