使用开窗函数来实现
select
round(count(t3.user_id) /(select count(distinct user_id) from login),3) cnt1
from
(
select
t1.user_id,
t1.new_dt,
count(t1.new_dt) cnt
from
(
select
t.user_id,
t.date,
t.rn,
t.date + t.rn new_dt
from
(
select
user_id,
date,
row_number() over (partition by user_id order by date desc) rn
from
login
)t
)t1
group by t1.user_id,t1.new_dt
having cnt>=2
)t3