每个用户r消费时间间隔、f消费频次、m消费金额

# 每个用户r消费时间间隔、f消费频次、m消费金额
select  user_id,
        datediff(date('2014-12-19'),max(日期)) as r,
        count(*) as f,
        sum(amount) as m
from userbehavior_new
where behavior_type = 'buy'
group by user_id;

# RFM评分
select  user_id,
        if(r>=25,1,if(r>=19,2,if(r>=13,3,if(r>=7,4,5)))) as R评分,
	if(f>=5,5,if(f=4,4,if(f=3,3,if(f=2,2,1)))) as F评分,
	if(m>=400,5,if(m>=300,4,if(m>=200,3,if(m>=100,2,1)))) as M评分
from(   select  user_id,
                datediff(date('2014-12-19'),max(日期)) as r,
                count(*) as f,
                sum(amount) as m
        from userbehavior_new
        where behavior_type = 'buy'
        group by user_id) as T; 
# RFM均值
select avg(R评分),avg(F评分),avg(M评分)
from(	select  user_id,
	        if(r>=25,1,if(r>=19,2,if(r>=13,3,if(r>=7,4,5)))) as R评分,
	        if(f>=5,5,if(f=4,4,if(f=3,3,if(f=2,2,1)))) as F评分,
	        if(m>=400,5,if(m>=300,4,if(m>=200,3,if(m>=100,2,1)))) as M评分
		from  (select user_id,
		              datediff(date('2014-12-19'),
		              max(日期)) as r,
		              count(*) as f,
		              sum(amount) as m
			from userbehavior_new
	                where behavior_type = 'buy'
		        group by user_id) as T1
      ) as T2;
      
# RFM重要程度

select *,
       if(R评分>3.5984,'H','L') as R程度,
       if(F评分>2.1039,'H','L') as F程度,
       if(M评分>2.2051,'H','L') as M程度
from(	select user_id,
			   if(r>=25,1,if(r>=19,2,if(r>=13,3,if(r>=7,4,5)))) as R评分,
			   if(f>=5,5,if(f=4,4,if(f=3,3,if(f=2,2,1)))) as F评分,
			   if(m>=400,5,if(m>=300,4,if(m>=200,3,if(m>=100,2,1)))) as M评分
		from  (select user_id,datediff(date('2014-12-19'),max(日期)) as r,count(*) as f,sum(amount) as m
			   from userbehavior_new
			   where behavior_type = 'buy'
			   group by user_id) as T1
	) as T2;
# RFM用户价值

select user_id,
	   concat(R程度,F程度,M程度) as 用户价值
from(	select *,
			   if(R评分>3.5984,'H','L') as R程度,
			   if(F评分>2.1039,'H','L') as F程度,
			   if(M评分>2.2051,'H','L') as M程度
		from(	select user_id,
					   if(r>=25,1,if(r>=19,2,if(r>=13,3,if(r>=7,4,5)))) as R评分,
					   if(f>=5,5,if(f=4,4,if(f=3,3,if(f=2,2,1)))) as F评分,
					   if(m>=400,5,if(m>=300,4,if(m>=200,3,if(m>=100,2,1)))) as M评分
				from  (select user_id,datediff(date('2014-12-19'),max(日期)) as r,count(*) as f,sum(amount) as m
					   from userbehavior_new
					   where behavior_type = 'buy'
					   group by user_id) as T1
			) as T2
) as T3; 
# 查询不同用户价值用户数
select	用户价值,count(*) as 不同价值的用户数	
from(	select user_id,
			   concat(R程度,F程度,M程度) as 用户价值
		from(	select *,
					   if(R评分>3.5984,'H','L') as R程度,
					   if(F评分>2.1039,'H','L') as F程度,
					   if(M评分>2.2051,'H','L') as M程度
				from(	select user_id,
							   if(r>=25,1,if(r>=19,2,if(r>=13,3,if(r>=7,4,5)))) as R评分,
							   if(f>=5,5,if(f=4,4,if(f=3,3,if(f=2,2,1)))) as F评分,
							   if(m>=400,5,if(m>=300,4,if(m>=200,3,if(m>=100,2,1)))) as M评分
						from  (select user_id,datediff(date('2014-12-19'),max(日期)) as r,count(*) as f,sum(amount) as m
							   from userbehavior_new
							   where behavior_type = 'buy'
							   group by user_id) as T1
					) as T2
		) as T3
	) as T4
group by 用户价值
order by 不同价值的用户数 desc;