sql查询连续登录天数？如何用sql判断用户连续登录几天
数据表 login_log
1、数据去重,一个用户每天只保留一条登录记录
select id,userId,operateTime,FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') as dt
from login_log
group by FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') order by operateTime ASC;
2、用row_number() over()函数计数。进行用户id分组并按照日期进行排序（获取排序序号rn，窗口函数）
select id,userId,operateTime,dt,
row_number() over(partition by a.userId order by a.dt) as rn
from (
select id,userId,operateTime,FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') as dt
from login_log
group by FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') order by operateTime ASC;
) a
3、base_dt ，再用登录日期dt和排序序号rn进行差值计算（DATE_SUB），并按照userId和差值进行分组计数（这就是用户的连续登录天数）
select id,userId,operateTime,rn,dt,DATE_SUB(dt,INTERVAL rn DAY) as base_dt from ( select id,userId,operateTime,dt,
row_number() over(partition by a.userId order by a.dt) as rn from ( select id,userId,operateTime,FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') as dt
from login_log
group by FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') ) a) b GROUP BY userId, DATE_SUB(dt,INTERVAL rn DAY)
4、按base_dt分组，计算出连续登录天数continue_days，continue_days 最大值就是用户最大连续登录天数
select id,count(*) as continue_days,userId,operateTime,rn,dt,base_dt from ( select id,userId,operateTime,rn,dt,DATE_SUB(dt,INTERVAL rn DAY) as base_dt from ( select id,userId,operateTime,dt,
row_number() over(partition by a.userId order by a.dt) as rn from ( select id,userId,operateTime,FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') as dt
from login_log
group by FROM_UNIXTIME(operateTime/1000,'%Y-%m-%d') ) a ) b) c GROUP BY base_dt
