select 用户分级, round(count(uid)/(select count(distinct uid) from tb_user_log),2) as 比例 from ( select uid, case when early_dt >= date_sub(today,interval 6 day) then '新晋用户' when latest_dt >= date_sub(today,interval 6 day) then '忠实用户' when latest_dt >= date_sub(today,interval 29 day) and latest_dt < date_sub(today,interval 6 day) then '沉睡用户' when latest_dt < date_sub(today,interval 29 day) then '流失用户' end) '用户分级' from ( select uid,max(date(out_time)) latest_dt,min(date(in_time)) early_dt from tb_user_log group by uid ) t1 ) t2 group by 用户分级 order by 比例 desc