如何利用SQL创建RFM用户分析模型

2024-01-15 15:44

本文主要是介绍如何利用SQL创建RFM用户分析模型,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

   RFM用户分析模型究竟是什么?在之前的博客已经介绍了它的意义以及如何在现有的数据的情况下,利用Pandas创建分析模型,这里不再重复,详细可以参考我博客https://blog.csdn.net/weixin_48591974/article/details/116192534



   这一期将详细介绍如何利用SQL直接获取所需的数据去创建RFM用户分析模型。SQL ---- Structured Query Language(结构化查询语言 ),它是一种专门用来与数据库沟通的语言,提供了很多从数据库中高效地读写数据、查询数据的方法。常用的DBMS(数据库管理软件) 有 MySQL、Access、SQLite、SQL SERVER、Oracle 等等。



Recency:最近一次消费,即上一次交易距今多少天,反应了客户是否流失;


Frequency:消费频率,一段时间内客户的消费频率,反应了客户的消费活跃度;


Monetary:消费金额,一段时间内客户消费总金额,反应了客户价值


根据 RFM 这 3个 Dimension,可以分为8种客户类型,如下图:



在这里插入图片描述



   以下将简单介绍3个指标值的计算方法:



   F 值的计算:例如要计算 子表 orders 里 user_id 为 8002011的值。



在这里插入图片描述



   统计数据行数,我们可以使用 count() 函数完成,最后得出 F值为 3



SELECT  count(*)    AS F
FROM    orders
WHERE   user_id = 8002011;


   M 值的计算: 使用 sum() 函数将计算字段 price * amount 所有行加起来就得出 user_id 8002011 总消费金额为 13000



SELECT  sum(price * amount) AS M
FROM    orders
WHERE   user_id = 8002011;


   R 值的计算:在orders表中,可见时间是以 Integer 类型存在,也就是以格兰威治秒数(Unix时间戳)类型存在,指的是从1970年1月1日开始计算的秒数。关于时间戳和日期转换方法,可以运用 python的 datetime module 去实现转换。例如今天(2024-01-12)所对应的时间戳经过转换为 1704988800(秒),因此可以用这数值减去最近一次消费的日期,最后转换成天数,就知道 user_id 8002011最近一次消费距离今天为多少天。



SELECT (1704988800 - max(pay_time)) / (24 * 60 * 60) AS R,
FROM    orders
WHERE   user_id = 8002011;


   有了以上对3个指标的计算方法,以下就继续介绍各种方法进行分析。假设有如下总的数据表(更多数据行省略…)



在这里插入图片描述



   首先通过GROUP BY 子句,将原数据进行 分组,再通过聚合函数把字段组合相同的行划分为同一组。具体语法如下:



SELECT user_id  AS 用户ID,user_name AS 用户昵称,(1704988800 - max(pay_time)) / (24 * 60 * 60) AS R,count(user_id) AS F,  sum(price * amount) AS M
FROM   orders
GROUP BY user_id;


   这样就可以把每位用户的 RFM 值都计算出来了。



在这里插入图片描述



   刚才根据 R、F、M 维度表现的高与低,把用户分划分为 8 类,我们可以选择中位数作为区分高、低的依据。



   数据表 orders 共包含 21名用户数据。所以如果我们想获取中位数,查询语句需要写成 LIMIT 10, 1 由于21的中位数为 11, 因此 LIMIT 语句的第1个参数为 10,第2个参数为返回1行的结果,那就是中位数的值。



   获取 R 的中位数: 结果为32



SELECT (1704988800 - max(pay_time)) / (24 * 60 * 60) AS R
FROM   orders
GROUP BY user_id
ORDER BY R
LIMIT 10,1;


   获取 F 的中位数: 结果为3



SELECT count(user_id) AS F
FROM   orders
GROUP BY user_id
ORDER BY F
LIMIT 10,1;


   获取 M 的中位数: 结果为 36500



SELECT sum(price * amount) AS M
FROM   orders
GROUP BY user_id
ORDER BY M
LIMIT 10,1;


   接着,以如何获得 “新用户” 和 "流失用户"为例去继续编写语句分析。以上已经得出 R、F、M 的中位数。由于R(最近一次消费)的值越小,说明用户越活跃,因此低于 32 的被划分为 R 水平 高,反之为 R 水平 低。同理,我们可以得出 F 和 M 的划分方法, F:低于 3 为水平 低,反之为 高;M:低于 36500 为水平 低,反之为 高。



   HAVING 子句在用法上和 WHERE 子句非常相似,它们都能够根据指定条件筛选数据,也可以使用我们在 SELECT 子句中定义的字段别名。不同之处在于,WHERE 子句作用于原数据表,用来筛选 行;而 HAVING 子句作用于分组结果,用来筛选 分组。所以在编写语句时,必须区分好。



SELECT user_id  AS 用户ID,user_name AS 用户昵称,(1704988800 - max(pay_time)) / (24 * 60 * 60) AS R,count(user_id) AS F,  sum(price * amount) AS M
FROM   orders
GROUP BY user_id
HAVING R <=32AND F < 3AND M < 36500;


   得出"新用户" 数据表:



在这里插入图片描述



SELECT user_id  AS 用户ID,user_name AS 用户昵称,(1704988800 - max(pay_time)) / (24 * 60 * 60) AS R,count(user_id) AS F,  sum(price * amount) AS M
FROM   orders
GROUP BY user_id
HAVING R > 32AND F < 3AND M < 36500;


   得出"流失用户" 数据表:



在这里插入图片描述



   同理,其它6个维度的客户分析也是同样编写相对应的条件语句进行筛选。对于在众多的用户中如何可以快速、科学地分类,尤其前5个维度,对于每个商业行业有着重要的作用,也是掌握企业命运的风向标。

这篇关于如何利用SQL创建RFM用户分析模型的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/609404

相关文章

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

idea中创建新类时自动添加注释的实现

《idea中创建新类时自动添加注释的实现》在每次使用idea创建一个新类时,过了一段时间发现看不懂这个类是用来干嘛的,为了解决这个问题,我们可以设置在创建一个新类时自动添加注释,帮助我们理解这个类的用... 目录前言:详细操作:步骤一:点击上方的 文件(File),点击&nbmyHIgsp;设置(Setti

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误