超详细解析:用sql查找连续登陆7天或者登陆天数最多的用户id

 

# 方法一:查询连续登陆7天的用户id和登陆天数
-- 第一步:用户登录日期去重
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
order by 连续天数 desc
limit 1;

# 方法三:登陆天数最多
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 order by max(h) desc LIMIT 1;

# 方法四:登陆天数最多
select d.id,max(e)
from
(select id,a,count(id) as e from 
(select distinct id,day(date) as c,row_number() over(partition by id order by date) a 
from tb_user
group by id,day(date)) b
where c = a  
group by id 
order by id) d;

# 方法五:登陆天数最多
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;   #再次排序 按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
order by `连续登录天数` desc
limit 1;