排序解决连续签到问题

需求描述

有一张用户签到的明细表，需要找出连续签到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有点冗长，可以自己优化一下，或者搞个临时表啥的