-- 4.2 RFM评分
select
user_id,
timestampdiff(day,max(日期),'2014-12-19') as R,
count(*) as F,
sum(amount) as M,
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;
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行