假设有如下表tb_user，现输出连续登陆7天的用户id

方法一思路:

1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。

-- 方法一
-- 第一步:用户登录日期去重
select distinct date(date) as 日期,id from tb_user;
-- 第二步:用row_number()计数
select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from tb_user)a;
-- 第三步:日期减去计数值得到结果
select *,date(日期)-cum as 结果 from (select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from tb_user)a)b;
-- 第四步:根据id和结果分组并计算总和,大于等于7的即为连续登录7天的用户
select id,count(*) from (select *,date(日期)-cum as 结果 from (select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from tb_user)a)b)c GROUP BY id,结果 having count(*)>=7;
select * from tb_user;

-- 方法二
select id,count(date2) as 连续天数
from (select *,date_sub(date1,interval r day) date2
			from(select distinct id,date(date) date1,
					 dense_rank()over(partition by id ORDER BY date(date)) as r
					 from tb_user) v ) w
group by id,date2
having count(date2) >= 7;

-- 方法三
select id,max(h) '登录天数'from(
select id ,count(e) h from (
select *,a-b as e from (
select *,row_number() over(order by id) b from(
select *,date_format(date,'%Y%m%d') a from (
select distinct id,date(date) date from tb_user order by id,date(date)) as c
 )as d) as f)as g 
group by e,id)as i group by id having max(h) >= 7;

-- 方法五
select id,max(sort1) as 最多登录天数
from(
select *,
dense_rank()over(partition by id,datesub order by id,date) as sort1
from
(select *,
date_sub(date,interval sort day) as datesub
from
(select id,date(date) date,
dense_rank()over(partition by id order by id,date(date)) as sort
from tb_user) a  # 按id,date 不跳越排序
) b   #计算 date - sort  日期差
) c group by id having max(sort1) >= 7;   #再次排序 按id和 日期差

-- 方法六
SELECT id,count(date-t2) `连续登录天数`
from(SELECT DISTINCT date(date) date,id,row_number()over(PARTITION by id)t2 
			from tb_user GROUP BY id,date(date)) a 
group by id,date-t2 having count(date-t2) >= 7;

-- 方法七
-- 排序+去重
select distinct id,date(date) date from tb_user order by id,date(date);
-- 给上表中添加row_number num 和 日期-num n
select id,date,row_number()over(partition by id) num,day(date) - row_number()over(partition by id) n from (select distinct id,date(date) date from tb_user order by id,date(date)) a;
-- 相同id且相同n的记录数超过7即可
select id,count(n) from (select id,date,row_number()over(partition by id) num,day(date)-row_number()over(partition by id) n from (select distinct id,date(date) date from tb_user order by id,date(date)) a) a group by id,n having count(n) >= 7;

-- 方法八
select distinct a.id 
from (select distinct id,day(date) day1,dense_rank()over(partition by id order by day(date)) dayno from tb_user) a 
join (select distinct id,day(date) day1,dense_rank()over(partition by id order by day(date)) dayno from tb_user) b 
on a.id = b.id 
where a.day1 + 6 = b.day1 and a.dayno + 6 = b.dayno 
group by a.id,a.day1;