【SQL案例】RFM模型分析案例

RFM模型分析案例

MySQL 版本 8.0

数据示例

日期	用户uid	名称	订单id	订单金额
20220824	152***1316	刘记	2223609363681413	40.700
20220814	150***2533	旺角	2222676792962412	35.060
20220815	150***2533	旺角	2222796827128230	31.760
20220816	150***2533	旺角	2222861551856190	31.760
20220824	152***1316	刘记	2223647388439413	31.760
20220814	150***2533	旺角	2222647768644208	30.660
…

代码实现

-- 计算 r f m 得分
DROP TABLE if exists t_order_rfm;
CREATE TABLE t_order_rfm
SELECT *
			,( max(r)over() -r ) / (max(r)over()-min(r)over())*100  r_no
			,( f- min(f)over() ) / (max(f)over()-min(f)over())*100  f_no
			,( m- min(r)over() ) / (max(m)over()-min(m)over())*100  m_no
FROM(
-- 分析 r f m
			SELECT *
					 ,DATEDIFF('20220920',max(日期)over(PARTITION by 用户uid))  r
					 ,count(配送单id)over(PARTITION by 用户uid) f
					 ,sum(订单金额)over(PARTITION by 用户uid) m
			FROM(
						SELECT  DATE_FORMAT(ftime,"%Y%m%d") 日期,用户uid,名称,配送单id,订单金额
						FROM `t_order`
						WHERE 订单金额>0
					)t 
		)t

# RFM 模型用户分类
SELECT *,case 
					when r_no>=r_avg and f_no>=f_avg and m_no>=m_avg then '重要价值'
					when r_no< r_avg and f_no>=f_avg and m_no>=m_avg then '重要保持'
					when r_no>=r_avg and f_no< f_avg and m_no>=m_avg then '重要发展'
					when r_no< r_avg and f_no< f_avg and m_no>=m_avg then '重要挽留'
					when r_no>=r_avg and f_no>=f_avg and m_no< m_avg then '一般价值'
					when r_no< r_avg and f_no>=f_avg and m_no< m_avg then '一般保持'
					when r_no>=r_avg and f_no< f_avg and m_no< m_avg then '一般发展'
					when r_no< r_avg and f_no< f_avg and m_no< m_avg then '一般挽留'
				ELSE '请检查' end 用户类型 
FROM(
		SELECT a.*,b.*
		FROM t_order_rfm a,( 
					SELECT avg(r_no)	r_avg 
						,avg(f_no)	f_avg 
						,avg(m_no)	m_avg
					from (SELECT r_no,f_no,m_no  FROM t_order_rfm GROUP BY r_no,f_no,m_no)t
				)b
	)t

可视化处理