SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R（Recency）

SQL 使用ADD_MONTHS实现RFM参数—R（Recency）

前言：

博主目前从事的是BA数据分析师、在实际工作中需要问其他部门的同事提供SQL需求，在最近的工作中大量用到SQL搭建RFM模型的案例，其中分别涉及到参数R，F和M的参数实现。因此，以我实际工作遇到的问题进行总结。

参数R的概念：

R值：最近一次消费（Recency）
消费者最近（最后）一次消费时间距离现在或指定时间的时间间隔。理论上，R值越小，所对应的客户价值就越高，即对店铺的回购几次最有可能产生回应。在CRM系统RFM模型中首先需要关注的就是R值。

使用SQL实现R值

案例如下：
2022年5月1日，某电商平台进行了一次促销活动，需要查看不同历史时间段购买的人数情况，即不同Recency的人数分布。R值如下：
1、R3
2、R4-R6
3、R7-R12
4、R13-R24
5、R25+

1、创建表格（以临时表格为例）

创建顾客及其下单日期

2、查看原始表格

顾客每次下单都会生成对应的下单时间。

3、查看每个顾客最近（最后）一次购买日期

注：最近下单日期，也为顾客最后的下单日期。因此取日期的最大值即可。

DROP TABLE IF EXISTS 临时表;
CREATE TEMPORARY TABLE 临时表 AS ... ...

上面创建临时表是为了下一步查询语句的方便，实际工作中会经常遇到。并推荐大家经常使用。

4、基于每个顾客的last_date添加Recency字段

注：在mysql中是不存在ADD_MONTHS函数的，因此在mysql的对应的编辑器中可使用ADDDATE进行代替。以下两种方法实现我们的目标R：
1、ADDDATE

ADDDATE('2022-05-01', INTERVAL -3 MONTH)  
 -- 指定日期月份减3，结果为2022-02-01

2、ADD_MONTHS

ADD_MONTHS('2022-05-01', -3)
 -- 指定日期月份减3，结果为2022-02-01

两个函数的具体使用方法，大家可以参考这位博主的文章
https://blog.csdn.net/weixin_35740875/article/details/115846912

5、查看每个R对应的人数

完整代码如下：

SELECT * FROM r;

DROP TABLE IF EXISTS temp_LD;
CREATE TEMPORARY TABLE temp_LD AS
SELECT cus,max(date) AS last_date from r
GROUP BY cus;

SELECT * FROM temp_LD;

-- RECENCY
DROP TABLE IF EXISTS temp_RF;
CREATE TEMPORARY TABLE temp_RF AS
SELECT *,CASE 
WHEN last_date >= ADDDATE('2022-05-01', INTERVAL -3 MONTH)  AND 
last_date < '2022-05-01' THEN 'R03'
WHEN last_date >= ADDDATE('2022-05-01', INTERVAL -6 MONTH)  AND 
last_date < ADDDATE('2022-05-01', INTERVAL -3 MONTH)  THEN 'R04-06'
WHEN last_date >= ADDDATE('2022-05-01', INTERVAL -12 MONTH) AND 
last_date < ADDDATE('2022-05-01', INTERVAL -6 MONTH)  THEN 'R07-12'
WHEN last_date >= ADDDATE('2022-05-01', INTERVAL -24 MONTH) AND 
last_date < ADDDATE('2022-05-01', INTERVAL -12 MONTH) THEN 'R13-24'
WHEN last_date < ADDDATE('2022-05-01', INTERVAL -24 MONTH) THEN 'R25+'
END AS Recency
FROM temp_LD;

SELECT * from temp_RF;

SELECT Recency,COUNT(DISTINCT cus) AS cus_num 
FROM temp_RF
GROUP BY Recency;