sql 分位数_RFM模型指标构建——SQL

本篇文章主要是实现用SQL语句构建RFM模型指标，为文章‘基于RFM模型的用户价值分析——Python&Tableau’第六部分的SQL版本。

文章链接：

miss：基于RFM模型的用户价值分析——Python&Tableau​zhuanlan.zhihu.com

将文章‘基于RFM模型的用户价值分析——Python&Tableau’第五部分清洗后的数据导出：

将数据导以varchar格式导入Navicat：

查看字段数据类型：

#查看数据字段类型
DESC sheet1;

将f1、消费金额字段类型修改为整型，将交易日期字段类型修改为日期型；

#修改表中字段类型
ALTER TABLE sheet1 MODIFY COLUMN f1 INT;
ALTER TABLE sheet1 MODIFY COLUMN 交易日期 date;
ALTER TABLE sheet1 MODIFY COLUMN 消费金额 INT;

查看修改结果：

#查看修改后数据字段类型
DESC sheet1;

构建R指标：

#构建R指标
#查询数据集中最大日期
select max(交易日期)
FROM sheet1;

#创建视图r，将2015-03-17设置为‘今天’，按顾客id分组，找出每个顾客的最近一次购买日期，计算‘今天’与最
#近购买日期之间的时间差
CREATE OR REPLACE VIEW r
AS
SELECT 顾客id,DATEDIFF('2015-03-17',MAX(交易日期)) R
FROM sheet1
GROUP BY 顾客id;
#查看视图r
SELECT * FROM r;

构建F指标：

#创建视图f，把一个用户在一天内的多次消费行为，从整体上看作一次，按顾客id分组统计交易次数。
CREATE OR REPLACE VIEW f
as
SELECT 顾客id,count(DISTINCT 交易日期) F
FROM sheet1
GROUP BY 顾客id;
#查看视图f
SELECT * FROM f;

构建M指标：

#创建视图m，以顾客id分组，对消费金额求和
CREATE OR REPLACE VIEW m
AS
SELECT 顾客id,SUM(消费金额) M
FROM sheet1
GROUP BY 顾客id;
#查看视图m
SELECT * FROM m;

将RFM指标联合：

#创建视图RFM，联合查询视图r、f、m
CREATE OR REPLACE VIEW RFM
AS
SELECT r.`顾客id`,R,F,M
FROM r
LEFT JOIN f
ON r.`顾客id`=f.`顾客id`
LEFT JOIN m
ON f.`顾客id`=m.`顾客id`;
#查看视图RFM
SELECT * FROM RFM;

确认打分R、F、M各指标打分依据并进行打分：

#创建视图df，确认打分依据并进行打分。最近一次交易时间间隔R，设定30天为一个跨度进行打分；F和M按数据
#集RFMX的分位数来打分。
CREATE OR REPLACE VIEW df
as
SELECT *,
(
CASE 
WHEN R<30 THEN 5
WHEN R<60 THEN 4
WHEN R<90 THEN 3
WHEN R<120 THEN 2
ELSE 1
END) R_s,
(
CASE 
WHEN F<13 THEN 1
WHEN F<16 THEN 2
WHEN F<17 THEN 3
WHEN F<21 THEN 4
ELSE 5
END) F_s,
(CASE 
WHEN M<813 THEN 1
WHEN M<1204 THEN 2
WHEN M<1257 THEN 3
WHEN M<1545 THEN 4
ELSE 5
END) M_s
FROM RFM;
#查看视图df
SELECT * FROM df;

判断每个顾客的RFM与R、F、M分值并与指标平均值进行比较，大于平均值记为1，小于平均值记为0：

#创建视图fl，判断每个顾客的RFM与R、F、M分值并与平均值进行比较，大于平均值记为1，小于平均值记为0。
CREATE OR REPLACE VIEW fl
AS
SELECT *,IF(R_S>(SELECT avg(R_S) FROM df),1,0) R是否大于均值,IF(F_S>(SELECT avg(F_S) FROM df
),1,0) F是否大于均值,IF(M_S>(SELECT avg(M_S) FROM df),1,0) M是否大于均值
FROM df;
#查看视图fl
SELECT * FROM fl;

添加顾客数值辅助列：

#创建视图gksz，添加顾客数值辅助列
CREATE OR REPLACE VIEW gksz
AS
SELECT *,CONCAT(R是否大于均值,F是否大于均值,M是否大于均值) 顾客数值
FROM fl;
#查看视图gksz
SELECT * FROM gksz;

定义人群标签并应用：

CREATE OR REPLACE VIEW mx
AS
SELECT *,
(CASE 顾客数值
	WHEN '111' THEN '重要价值客户'
	WHEN '110' THEN '消费潜力客户'
	WHEN '101' THEN '频次深耕客户'
	WHEN '100' THEN '新客户'
	WHEN '011' THEN '重要价值流失预警客户'
	WHEN '010' THEN '一般客户'
	WHEN '001' THEN '高消费唤回客户'
	ELSE '流失客户'
END) 顾客类型
FROM gksz;
#查看视图mx
SELECT * FROM mx;

建模完成。