需求描述
有一张用户签到的明细表,需要找出连续签到3天及以上的用户
1:查看表信息
select * from tmp.log_test;
user_id login_time
A 2019-11-30 19:26:55 490
A 2019-11-30 20:26:55 494
A 2019-12-01 20:26:55 495
A 2019-12-02 10:26:55 595
B 2019-12-01 20:26:55 495
B 2019-12-02 10:26:55 595
C 2019-11-30 19:26:55 490
C 2019-11-30 20:26:55 494
C 2019-12-02 10:26:55 595
2:去重排序
select
user_id,
to_date(login_time) as login_date,
rank()over(partition by user_id order by to_date(login_time)) as rk
from tmp.log_test
group by user_id,to_date(login_time)
order by user_id,login_date
得出结果
3:签到日期-序列号得到开始签到时间
select
user_id,
login_date,
rk,
date_sub(login_date,rk) as netdate
from(
select
user_id,
to_date(login_time) as login_date,
rank()over(partition by user_id order by to_date(login_time)) as rk
from tmp.log_test
group by user_id,to_date(login_time)
order by user_id,login_date)a
得到结果
3:根据开始签到时间count,即可知道每次连续签到时间
select
user_id,
netdate,
count(1) as count
from
(
select
user_id,
login_date,
rk,
date_sub(login_date,rk) as netdate
from(
select
user_id,
to_date(login_time) as login_date,
rank()over(partition by user_id order by to_date(login_time)) as rk
from tmp.log_test
group by user_id,to_date(login_time)
order by user_id,login_date)a
)a
group by user_id,
netdate
having count(1)>=3
得到结果
ps:上面的sql有点冗长,可以自己优化一下,或者搞个临时表啥的