代码又臭又长，用了很多次子查询，感觉很不聪明的样子，基本思路是如下 1：判断是不是找出最后一次登录时间差 2：是不是新顾客 3：标签分组 4：统计


select grade ,round(
    count(*)/(select count(distinct uid) from tb_user_log),2) as ration
from 
(
    select case when gap<7 and times!=1 then "忠实用户"
            when gap<7 and times=1 then "新晋用户"
            when gap>=7 and gap<30 then "沉睡用户"
            when gap>=30 then "流失用户" end as grade,
            uid
from(
select uid,min(gap) as gap,max(times) as times
from(
select uid,
     timestampdiff(day,in_time,(select max(in_time) from tb_user_log))
       as gap,
   count(*) over(partition by uid order by in_time) as times
from tb_user_log) tmp
GROUP by uid)  base
) info
GROUP by grade
order by ration desc,grade