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