题目1
现在有一张用户活跃全量表,列有id,活跃日期(全部),分区
如何求得最大连续活跃天数的ID
way1
with t1 as
(select id,row_number() over(partition by id order by lastactivetime asc) as ranks,
lastactivetime from table)
select id,ate_sub(lastactivetime,ranks) as difftime,
min(lastactiveimte) as min,max(lastactiveimte) as max
from t1
group by id,ate_sub(lastactivetime,ranks) as difftime
可以很好的将多次连续值分开
t2 as
(select id,difftime,min,max from t1
group by id,difftime,min,max)
select distinct id, max(datediff(max,min)+1) over(partition by id) as max_continuous_days
from t2
way2
with t1 as
(select id,row_number() over(partition by id order by lastactivetime asc) as ranks,
lastactivetime from table)
select t1.id as id,
datediff(t2.lastactiveitime) as datediff,
(t2.rank-t1.rank) as rankdiff
from t1 cross join t1 as t2
on t1.id = t2.id
having datediff = rankdiff
and datediff != 0