> 文档中心 > SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(Recency)

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、创建表格(以临时表格为例)

创建顾客及其下单日期
SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(Recency)

2、查看原始表格

SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(Recency)
顾客每次下单都会生成对应的下单时间。

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

SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(Recency)
注:最近下单日期,也为顾客最后的下单日期。因此取日期的最大值即可。

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

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

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

SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(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对应的人数

SQL 使用ADD_MONTHS或ADDDATE实现RFM参数—R(Recency)
完整代码如下:

SELECT * FROM r;DROP TABLE IF EXISTS temp_LD;CREATE TEMPORARY TABLE temp_LD ASSELECT cus,max(date) AS last_date from rGROUP BY cus;SELECT * FROM temp_LD;-- RECENCYDROP TABLE IF EXISTS temp_RF;CREATE TEMPORARY TABLE temp_RF ASSELECT *,CASE WHEN last_date >= ADDDATE('2022-05-01', INTERVAL -3 MONTH)  AND last_date = ADDDATE('2022-05-01', INTERVAL -6 MONTH)  AND last_date = ADDDATE('2022-05-01', INTERVAL -12 MONTH) AND 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 RecencyFROM temp_LD;SELECT * from temp_RF;SELECT Recency,COUNT(DISTINCT cus) AS cus_num FROM temp_RFGROUP BY Recency;