根据时间对用户进行分组, 然后计算比例就行可能还有一些时间的细节问题, 比如7天内, 那么是两天相减, 需要包含被减的那天, 比如一个用户的浏览时间正好跨了一天
select
case when datediff(t1.l_out_time, t1.f_login_time) <= 6 then '新晋用户'
when datediff(t1.l_out_time, t2.s_time) <= 6 and
datediff(t1.l_out_time, t1.f_login_time) >= 7 then '忠实用户'
# 如果用户只登录过一次, 需要添加额外判定条件
when (datediff(t1.l_out_time, t2.s_time) >= 30) or
(t2.s_time is null and datediff(t1.l_out_time, t1.f_login_time) >= 30) then '流失用户'
else '沉睡用户' end as user_grade,
round(count(distinct t1.uid) / sum(count(distinct t1.uid)) over(), 2) as ratio
from (
select ul.uid,
min(date(ul.in_time)) as f_login_time,
max(max(date(ul.out_time))) over() as l_out_time
from tb_user_log ul
group by ul.uid
) t1
left join (
select date(ul.in_time) as s_time,
ul.uid
from tb_user_log ul
group by date(ul.in_time), ul.uid
) t2 on t1.uid=t2.uid and t1.f_login_time < t2.s_time
group by user_grade
order by ratio desc