- 1.筛选出七天内数据,group by id,date
- 2.rank() over() as rk 根据id排序id,date
- 3.date -rk 为同一个数,select date -rk .group by id date-rk having count(*)
- 4.group id
具体代码:
select t4.id
from
(
select id, date_add(t2.dt-t2.rk) as rk2
from
(
select t1.id, t1.dt, rank() over(partition by t1.id order by t1.dt) as rk
from
(
select id, dt
from a
where dt>=date_add(currentdt,-6)
group by id, dt
)t1
)t2
)t3
group by id,rk2
havcing count(*)>=3
)t4
group by t4.id
–id dt rk
–1 11.26 1
–2 11.27 2
–3 11.28 3
–这样26-1,27-2,28-3都是25,这样把这个25group by起来,having ----coun(1)>=3的就是三天活跃用户