-- 4.3 RFM均值
select
avg(R评分) as R均值,
avg(F评分) as F均值,
avg(M评分) as M均值
from
(select
user_id,
case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5
when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4
when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3
when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2
else 1
end as R评分,
if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F评分,
if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M评分
from userbehavior_new
where behavior_type='buy'
group by user_id) as t;
#3.59842.10392.2051
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行