之前写了一个零基础的sql教学攻略，我的观点一直就是如果我们只需要像使用excel一样使用数据库来辅助工作的，只需要最简单的查询语法就可以了。
两小时入门SQL，像使用Excel一样使用sql，这是一篇给新人看的内容 - 简书
今天就用上面的教程里讲的单表查询操作，用最简单的语法，来完成一个工作中最常用的电商RFM客户模型分析。
首先关于什么是RFM就不介绍了，其他文章有详细的介绍，我们就直接上流程。
数据我放在了一个免费的服务器里，大家可以按照图的内容登录访问到，里面也有其他的案例数据。不会用也可以看我上面写的教程，里面有如何访问服务器的设置，以及软件包。
首先要说本次介绍的方法不是最佳的方法，有很多更加简洁的写法，更方便的流程的，但是UP写这个系列的目的就是想说我们就是要用最简单的语法，通过搭配excel来使用，方便又简单的实现想要的功能。
现在你有了最原始的一份excel数据，第一步就是把表格导入到数据库里，在数据库的表上点击右键，选择导入数据
选择好导入文件的格式，后面的内容都用默认点击下一步，直到这个界面
在这里为数据库中的表命名，推荐使用英文，下一步。
这里需要选择数据的形式，默认为字符串类型，初学者这里可以选择默认不去修改，随着使用熟练了再去学习数据格式的问题，后面我也会专门出一期攻略介绍。现在数据就导入成功了。
这是一份最原始的销售数据了，此处表命名为rfm。里面有客户编号，交易时间，和交易金额三个内容，数据的整理的工作可以在excel中进行，这个大家都会做，只要处理好格式和数据，直接导入数据库就可以了。在RFM分析中计分法要比均值法更复杂一些，这里我们就用计分法来演示。
第一步：生成RFM数据值
现在我们想要对数据进行整理，生成五列数据，假设我们是以2020-07-01作为分析日期，需要的五列数据分别是客户号、最后交易时间、R值、F值、M值。如果是在excel里进行操作需要用到很复杂的公式来求出数据，但是在数据库里，我们只需要定义好变量。
UserID：去重后对用户进行展示，直接使用UserID
LastDate：最后交易日期，直接使用MAX（date）函数对日期进行最大值比较
R_value：用2020-07-01减掉最后交易日求数天数，用函数datediff('2020-07-01',max(date)),这个函数在教程里没有提到，教程里只提了五个最常用的，这个函数就是表示求两个日期之间的天数，用法也很简单DATEDIFF（），括号里填入前后两个日期。
F_value：最用户数重复的次数进行计数，使用公式COUNT(UserID)
M_value：对用户交易额求和，使用公式SUM(`amount(10K RMB)`)
好了，现在按照教程所写，可以生成结果了，只需三行。
SELECT UserID , max(date) LastDate,datediff('2020-07-01',max(date)) R_value ,count( UserID) F_value,sum(amount) M_value
from rfm
group by UserID;
此时RFM原始值数据已经查询出来了，我们需要把这个数据保存为新表格，后面的操作再新表格上进行了，这里有两个方法，最原始的就是在结果上点击右键直接把内容输出，然后把输出结果重新导入进数据库生成一张新表格rfm1，这样不需要任何的语法就可以生成一张新的表。
如何你觉得这样操作麻烦一些，也可以直接使用SQL的关键词，此处为表格增删改相关的关键词CREATE TABLE，关键词的用法这里就不介绍了，感兴趣的可以自己查看一下或者等我们的攻略。直接在上面的查询语句前加入生成表格的关键词即可生成新表格了。
CREATE TABLE rfm1 ASSELECT UserID , max(date) LastDate,datediff('2020-07-01',max(date)) R_value ,count( UserID) F_value,sum(amount) M_value
from rfm
group by UserID;
第二步：生成RFM计数值
根据上面的计分规则，把RFM的原始值转化为计分值。
此处使用CASE WHEN...THAN...关键词把每种情况表示为一个判断语句，根据判断结果生成相应的值。
CASE WHENE 在上面的攻略里没有提到，要在后续有详细解释，这里如果不能马上看明白可以稍微一等
select UserID,R_value,F_value,M_value,
(case
when R_value<=30 then 5
when R_value<=60 then 4
when R_value<=90 then 3
when R_value<=180 then 2else 1 end) R_score,
(case
when F_value<=1 then 1
when F_value<=3 then 2
when F_value<=5 then 3
when F_value<=7 then 4else 5 end) F_score,
(case
when M_value<=30 then 1
when M_value<=50 then 2
when M_value<=100 then 3
when M_value<=500 then 4
else 5 end ) _score
from rfm1
查询结果如下
同样按照第一步的操作，可以先导出再导入为新表格，也可以同样用关键词生成一个新表格frm2，再新表格中进行操作。
第三步：生成RFM档位值
制定挡位规则，在挡位列中≥均值的为一档并用数字1表示，<均值的为一档并用数字0表示，根据此规则生成RFM档位值。首先使用AVG函数求每个参数的均值。
select avg(R_score) R_avg,avg(F_score) F_avg,avg(M_score) M_avg
FROM rfm2
然后使用CASE WHENE根据均值数字进行比较生成档位值
select UserID,
(case when R_score >= 2.94 then 1 else 0 END) R_grade,
(case when F_score >= 1.16 then 1 else 0 END) F_grade,
(case when M_score >= 2.385 then 1 else 0 END) M_grade
FROM rfm2
当然，此处有更简便的方法就是使用子查询，把求均值作为 一个子查询直接放到查询中，同样的，此处放出代码，感兴趣的同学可以去学习子查询。
select UserID,
(case when R_score>(select avg(R_score)FROM rfm2) then 1 else 0 END) R_grade,
(case when F_score>(select avg(F_score)FROM rfm2) then 1 else 0 END) F_grade,
(case when M_score>(select avg(M_score)FROM rfm2) then 1 else 0 END) M_grade
FROM rfm2
同上我们生成了第三个表格rfm3。
第四步：生成RFM用户分层
根据如下规则对用户进行分类
同样使用CASE WHENE进行比较生成结果，这里行数比较多，注意不要输入错误。
select UserID,R_grade,F_grade,M_grade,
(case
when R_grade =1 and F_grade =1 and M_grade =1 then '活跃VIP'
when R_grade =1 and F_grade =1 and M_grade =0 then '普通熟客'
when R_grade =1 and F_grade =0 and M_grade =1 then '潜力VIP'
when R_grade =1 and F_grade =0 and M_grade =0 then '普通新客'
when R_grade =0 and F_grade =1 and M_grade =1 then '沉睡VIP'
when R_grade =0 and F_grade =1 and M_grade =0 then '流失熟客'
when R_grade =0 and F_grade =0 and M_grade =1 then '流失土豪'
else '完全流失' END ) user_level
from rfm3
同样生成表格，这是我们需要生成的最后一个表格rfm4了。
同样此处可以使用多表查询的方式让查询更加的简单，放出代码，感兴趣的自己学习。
首先，添加一个用户分类的数据表UserType
然后把两个表格关联进行多表查询。
select r.userid,r.R_grade,r.F_grade,r.M_grade, u.UserType
from rfm3 r
inner join UserType u on r.r_grade=u.r and r.f_grade=u.f and r.m_grade=u.m
同样得出结论。
第五步：统计用户分层数据
这是最简单的一步，直接对用户类型进行计数即可。
SELECT user_level,COUNT(user_level)
FROM rfm4
GROUP BY user_level
大功告成，我们用最简单的语法完成了RFM模型的分析。
思考题：在rfm3的表格中，如果使用多表查询的方式与类型表进行比较直接查询到用户类型的话，可以把多表查询作为一个子查询，这样，可以不用创建rfm4实现统计结果，感兴趣的可以尝试下。
网友评论