通过代码
select
user_grade,
round(count(uid) / tot,2) ratio
from
(select
uid,
tot,
(case
when datediff(now,intime) > 6 and datediff(now,newtime) <= 6 then '忠实用户'
when datediff(now,intime) <= 6 then '新晋用户'
when datediff(now,intime) > 29 and datediff(now,newtime) > 29 then '流失用户'
else '沉睡用户'
end
)user_grade
from
(select
uid,
(select count(distinct uid) from tb_user_log) tot,
(select max(date(out_time)) from tb_user_log) now,
min(date(out_time)) intime,
max(date(out_time)) newtime
FROM
tb_user_log
GROUP BY
uid)t)a
GROUP BY
user_grade
order BY
ratio DESC
思路
统计活跃间隔对用户分级后,各活跃等级用户占比,结果保留两位小数,且按占比降序排序。
注:
用户等级标准简化为:
忠实用户(近7天活跃过且非新晋用户)、
新晋用户(近7天新增)、
沉睡用户(近7天未活跃但更早前活跃过)、
流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。近7天表示包含当天T的近7天,即闭区间[T-6, T]。
我们先拆出来**“今天 now”、“最早出现在这个表里的时间 intime”、“最后的活跃时间 newtime”、“用户总数量tot”**
select
uid,
(select count(distinct uid) from tb_user_log) tot,
(select max(date(out_time)) from tb_user_log) now,
min(date(out_time)) intime,
max(date(out_time)) newtime
FROM
tb_user_log
GROUP BY
uid
然后以此为基础划分各级用户
忠实用户(近7天活跃过且非新晋用户)、 datediff(now,intime) > 6 (注册时间) and datediff(now,newtime) <= 6 (最近活跃)
(题上说近7天包括今天所以这里是6 )
新晋用户(近7天新增)、datediff(now,intime) <= 6(表里有过就活跃过)
沉睡用户(近7天未活跃但更早前活跃过)、注册时间、最近活跃 between 7 and 29,太长了所以放在else里面 else!!!不是default!!
流失用户(近30天未活跃但更早前活跃过)。datediff(now,intime) > 29 and datediff(now,newtime) > 29 then '流失用户'
select
uid,
tot,
(case
when datediff(now,intime) > 6 and datediff(now,newtime) <= 6 then '忠实用户'
when datediff(now,intime) <= 6 then '新晋用户'
when datediff(now,intime) > 29 and datediff(now,newtime) > 29 then '流失用户'
else '沉睡用户'
end
)user_grade
from
(上边)t
ok