MySQL求用户的连续登陆次数

#数据准备
create table users(uname varchar(10),stadate varchar(10));
insert into users values('张三','20220302');
insert into users values('张三','20220303');
insert into users values('张三','20220304');
insert into users values('张三','20220304');
insert into users values('张三','20220305');
insert into users values('张三','20220307');

insert into users values ('李四','20220302');
insert into users values ('李四','20220303');
insert into users values ('李四','20220304');
insert into users values ('李四','20220304');

insert into users values ('王五','20220302');
insert into users values ('王五','20220304');
insert into users values ('王五','20220306');

#1、用户与登陆日期去重
select uname,stadate as stadate from users group by uname,stadate;

#2、算出登陆日期与参照日期差的值，和按用户的登陆日期排序
select a.*
		,datediff(stadate,'20220101') num1 #登陆日期与参照日期差
		,row_number() over(partition by uname order by stadate) num2 #排序
from (select uname,stadate as stadate from users group by uname,stadate) a

#3、如果用户连续登陆，则日期差的值 - 排序 的值是相等的
select b.*,num1 - num2 as num3
from 
	(select a.*
			,datediff(stadate,'20220101') num1 #登陆日期与参照日期差
			,row_number() over(partition by uname order by stadate) num2 #排序
	from (select uname,stadate as stadate from users group by uname,stadate) a
	) b

#4、最后算出用户用户连续登陆的次数
select uname,count(*)
from 
(select b.*,num1 - num2 as num3
from 
	(select a.*
			,datediff(stadate,'20220101') num1 #登陆日期与参照日期差
			,row_number() over(partition by uname order by stadate) num2 #排序
	from (select uname,stadate as stadate from users group by uname,stadate) a
	) b
) c
group by uname,num3
#having count(*) >= 3  #如果要连续登陆几次，这加盖条件

#5、优化算连续登陆3次以上的用户
select uname,count(*)
from 
	(select a.*
		,datediff(stadate,'20220101') - row_number() over(partition by uname order by stadate) field
		from (select uname,stadate as stadate from users group by uname,stadate) a
	) b
group by uname,field
having count(*) >=3;