sql计算连续三天活跃用户_sql查询连续三天活跃用户

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

–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的就是三天活跃用户