-- 4.4 RFM重要程度

select

*,

if(R评分>3.5984,'高','低') as R程度,

if(F评分>2.1039,'高','低') as F程度,

if(M评分>2.2051,'高','低') as M程度

from

(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) as t;