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

with log as (
select
    *
    ,date_format(log_time,'%Y-%m-%d') as dt,row_number()over(partition by user_id order by log_time) as rk
from
    login_tb
where user_id in (select user_id from register_tb)
)
select user_id
from (select user_id,tmp,count(*)
from(select *,date_sub(dt,interval rk day) as tmp 
from log) a 
group by user_id,tmp
having count(*)>=3)c
order by user_id

首先就是从2表中筛选出我们需要的数据，也就是于1表进行inner join， 因为只要1表中的用户的行为数据，因为1表中是新注册的用户。 后面就是正常的处理连续登陆的思路。

我这么写比较墨迹， 更简洁一点应该这么写

，WITH log AS (

SELECT user_id,

DATE(log_time) AS login_date, -- 直接用DATE()更简洁

ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_time) AS rn

FROM login_tb

WHERE user_id IN (SELECT user_id FROM register_tb)

)

SELECT user_id

FROM log

GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY)

HAVING COUNT(*) >= 3

ORDER BY user_id;