SQL连续登录判断算法二

#原创文章,转载请注明出处

 1.创建表,要关注的字段是用户id和登录时间.创建试验数据:

--Oracle
CREATE TABLE "TIM"."LOGIN_INFO" 
   (	"USERID" VARCHAR2(5), 
	"LOGIN_DATE" DATE
   )
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('01-03-2024 07:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('01-03-2024 15:41:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('03-03-2024 10:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('05-03-2024 12:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('06-03-2024 10:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('07-03-2024 09:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('09-03-2024 10:30:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('11-03-2024 09:53:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('12-03-2024 11:45:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('13-03-2024 15:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('14-03-2024 14:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('15-03-2024 16:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('16-03-2024 09:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('17-03-2024 10:08:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('19-03-2024 19:05:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('21-03-2024 06:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('22-03-2024 21:47:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('25-03-2024 08:40:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('25-03-2024 10:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('26-03-2024 10:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('27-03-2024 12:30:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('28-03-2024 08:20:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into LOGIN_INFO (userid, login_date)
values ('00001', to_date('29-03-2024 09:00:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;

2.分组去重并排序

select distinct USERID,trunc(LOGIN_DATE) td from login_info  order by userid,trunc(LOGIN_DATE)

3.连续登录7天,去重的数据里:从开始的一天往后数7天总共有7条数据.这里用子查询实现

with t as(select distinct USERID,trunc(LOGIN_DATE) td from login_info order by userid,trunc(LOGIN_DATE)) --去重的表取别名 t
select * from t t1 where exists(select 1 from t t2 where t1.userid=t2.userid and t2.td<t1.td+7 and t1.td<=t2.td having count(*)=7 )  --往后数7天,有7条数据

--7日连续登录起始日期

with t as(select distinct USERID,trunc(LOGIN_DATE) td from login_info order by userid,trunc(LOGIN_DATE)) --去重的表取别名 t
select * from t t1 where exists(select 1 from t t2 where t1.userid=t2.userid and t2.td+7>t1.td and t1.td>=t2.td having count(*)=7) --往前数7天,有7条数据

--7日连续登录终止日期

有数据则说明有连续登录7天