連續(xù)登錄 減去排序常數(shù)結果應是一致的。

with t1 as (select distinct uid,date(login_time)ymd

from t_login where login_time between timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59:59')

t2 as (select uid,ymd ,

date_sub(ymd,interval row_number() over (partition by uid order by ymd) day )sub_date

from t1)

select uid ,min(ymd),max(ymd),count(*)

from t2 group by uid,sub_date

having count(*) >=5;

（2)連續(xù)登錄大于等于3天

with t1 as (select distinct uid,date(login_time)ymd

from t_login where login_time between timestamp '2022-01-01 00:00:00' and timestamp '2022-01-31 23:59:59'),

t2 as (

select distinct uid,ymd,

datediff (lag(ymd,2) over (partition by uid order by ymd) )diff

from t1)

select uid,date_sub(ymd,interval 2 day)min_date ,ymd

from t2

where diff >=2;