题解 | 查询连续登陆的用户

--连续登录问题可以采用“连续值-连续值，结果一致”的原理
with temp as (
select
    t1.user_id,t1.reg_time,t1.reg_port,date(t2.log_time) as log_date,t2.log_port
    ,row_number() over(partition by t1.user_id order by date(t2.log_time) asc) as rn
from register_tb t1
inner join login_tb t2
on t1.user_id=t2.user_id
)
select
    user_id
from
(
select
    user_id,log_date_new,count(1) as lianxu_days
from
(
select
    user_id,log_date,date_sub(log_date,interval rn day) as log_date_new
from temp
) t
group by user_id,log_date_new
) t
where lianxu_days>=3
group by user_id
order by user_id asc
;