基本流程:
- 先查询出所有日期中的最大值
- 条件判断: 忠实用户--大于等于最大值减6并且不等于该用户的第一次登陆时间 新晋用户--大于等于最大值减6并且等于该用户的第一次登陆时间 沉睡用户--小于等于最大值减6且大于最大值减30 流失客户--小于等于最大值减30
- 根据新生成字段分组求出各等级用户占总用户的比例
这里注意先要根据每个用户分组聚合得到每个用户最近登陆时间表
select t2.user_grade
,round(count(distinct t2.uid)/(select count(distinct uid) from tb_user_log),2) ratio
from (select uid
,case when
date_add(t1.dt,interval 6 day)>=(select max(date(in_time))
from tb_user_log)
and (t1.uid,t1.dt) not in (select uid,min(date(in_time))
from tb_user_log
group by uid)
then '忠实用户'
when date_add(t1.dt,interval 6 day)>=(select max(date(in_time))
from tb_user_log)
and (t1.uid,t1.dt) in (select uid,min(date(in_time))
from tb_user_log
group by uid)
then '新晋用户'
when date_add(t1.dt,interval 6 day)<=(select max(date(in_time))
from tb_user_log)
and date_add(t1.dt,interval 30 day)>(select max(date(in_time))
from tb_user_log)
then '沉睡用户'
else '流失用户'
end user_grade
from (select uid,max(date(in_time)) dt
from tb_user_log
group by uid) as t1
) as t2
group by t2.user_grade
order by ratio desc