SQL连续登录判断算法一

Timer_Cooker

已于 2024-04-07 21:51:33 修改

阅读量260

点赞数 7

文章标签： sql 数据库大数据

于 2024-04-05 09:20:31 首次发布

本文链接：https://blog.csdn.net/qq_42911358/article/details/137392027

版权

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

之前有讨论用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) from login_info order by userid,trunc(LOGIN_DATE)

3.与另外一个连续序列作差

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

4.分组统计

select userid,count(td-rownum) from (select distinct USERID,trunc(LOGIN_DATE) td from login_info order by userid,trunc(LOGIN_DATE)) group by userid,td-rownum

5.过滤分组统计的数据

select userid,count(td-rownum) from (select distinct USERID,trunc(LOGIN_DATE) td from login_info order by trunc(LOGIN_DATE)) group by userid,td-rownum having count(td-rownum)>=5 /*如果没有符合连续登录5天的数据,userid为空*/