扫码加入数据分析学习群

-- 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;