|
用个排序的窗口函数即可实现：
select distinct user_id
from (
select user_id
, continue_date
, count(login_date) as continue_day_cnt
from (
select user_id
, login_date
, login_date - row_number() over(partition by user_id order by login_date) as continue_date
from (
select user_id
, substr(login_time, 1, 10) as login_date
from dw.dw_user_login_log
where 1 = 1
group by user_id
, substr(login_time, 1, 10)
) t
) m
where 1 = 1
group by user_id
, continue_date
having count(login_date) >= 3 -- 连续3天登录，根据业务场景调整这一数值
) x ;
| 
转播
