小木的学习日记-SQL中分组与数据统计-优快云博客

RFM模型：

我们先来介绍一个大家耳熟但又不太了解的模型---RFM模型

R(Recency)-最近的一次消费时间间隔！！注意是间隔！比如你在A店铺，6月1日买了一个东西，然后第二次进行交易时6月5日又再该店铺有买了一个，则R时间间隔就是4天（5-1 哈哈哈）

F(Frequency)-消费次数（消费了几次）

M(Monetary)-总消费金额

GROUP BY

1 通过GROUP BY 算每一个客户的M

SELECT account_id,SUM(total_price) AS M  选中orders表中的account_id列以及对total_price求和后给新的列M
FROM orders   数据来自于表orders
GROUP BY account_id;  按照account_id进行分组（account_id对应的客户就是按照客户分组，从而计算出每一个客户的M）

2注意

在MYSQL数据库中，对于一个单元格有多个数据的列不能直接用SELECT取出其中所有的元素。比如

若使用SELECT id 只能取出id列每个单元格中的第一个元素。

2 计算F消费频率（消费的总次数）

总次数---我就想到了对于的总行数，因为1行代表一次消费记录嘛

所以F的数量与行数相同

所以呢剩下的任务就是对F进行筛选以及分组了

SELECT account_id,COUNT(*) AS F   总结所有的行数并且赋值给新的列 F
FROM orders   数据来自于orders表
WHERE total_price is not null 条件-总的价格不是空的
GROUP BY account_id;  以用户id(account_id)进行分组

HAVING（having）

其作用就是分组（GROUP BY）之后的筛选

以下是其与WHERE的区别

HAVING支持所有WHERE操作符。唯一的差别是WHERE过滤行，HAVING过滤组。

HAVING必须在GROUP BY之后使用，表示对分组后的结果进行筛选。

比如：
SELECT account_id,COUNT(*) AS F
FROM orders
WHERE total_price is not null
GROUP BY account_id
HAVING F>5   在分组之后进行筛选 
ORDER BY F DESC,account_id;

FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT/OFFSET
以上是SQL语言的执行顺序

先从FROM表中获取数据集，然后对数据进行筛选----价格不是空值---筛选后进行分组（按照用户ID）--筛选后对每个用户的数据进行二次筛选比如----HAVING F>5 只要F大于5的数据  之后呢先按照F进行降序排序如果F相等时按account_id进行升序排序（因为没有写出DESC则默认是升序ASC）

简洁版：

一、认知破冰：为什么 RFM 模型是数据人的「瑞士军刀」？

（用「痛点场景 + 价值承诺」建立阅读动机，参考《Hooked》用户触发理论）

场景导入：

假设你是电商运营，面对 10 万条订单数据，如何快速区分「高价值客户」和「沉睡客户」？
❌ 传统做法：凭经验翻 Excel，耗时 3 天还漏看关键客户
✅ 高效解法：用 RFM 模型 3 步量化客户价值，10 分钟生成决策报表

核心价值：

3 个维度（R/F/M）拆解客户分层逻辑
5 行 SQL 实现从数据到洞察的跃迁
避开 90% 新手会犯的分组统计陷阱

二、RFM 模型底层逻辑：3 个维度定义客户价值

（采用「概念→公式→案例」递进结构，符合《认知天性》记忆规律）

2.1 R（Recency）- 最近消费间隔：用时间衡量活跃度

核心公式：
R = 当前日期 - 最后一次消费日期（数值越小，客户越活跃）
案例解析：

客户 A：6 月 5 日下单 → R=4 天（假设今日 6 月 9 日）
客户 B：5 月 1 日下单 → R=39 天
运营策略：R>30 天的客户需重点唤醒（如定向优惠券）

2.2 F（Frequency）- 消费频次：用次数衡量忠诚度

核心公式：
F = 某时间段内消费次数（数值越大，忠诚度越高）
反常识点：

单次高消费客户≠优质客户（可能是偶然行为）
高频低消客户更具长期价值（如咖啡月卡用户）

2.3 M（Monetary）- 消费金额：用钱包份额衡量贡献度

核心公式：
M = 累计消费金额（数值越大，商业价值越高）
业务场景：

M>10000 元：VIP 专属服务
M<500 元：引导升级套餐

三、SQL 实战：从 0 到 1 计算 RFM 指标（附执行顺序脑图）

（采用「需求拆解→代码分步→结果验证」流程，参考《代码整洁之道》可读性原则）

3.1 准备工作：理解数据结构

表结构示例（orders 表）：

account_id	order_date	total_price
101	2025-06-01	199
101	2025-06-05	299
102	2025-05-01	599

3.2 计算 M（总消费金额）：分组聚合核心逻辑

需求：按客户分组，计算每位客户的累计消费金额

sql

SELECT  
  account_id,  
  SUM(total_price) AS M  -- 核心聚合函数  
FROM  
  orders  
GROUP BY  
  account_id;  -- 按客户ID分组（关键点：GROUP BY后只能跟聚合列或分组列）

执行顺序解析：
FROM orders → 加载数据 → GROUP BY account_id → 分组 → SUM(total_price) → 计算 M

3.3 计算 F（消费频次）：行数统计与筛选

需求：统计有效订单（排除金额为空的记录）的客户消费次数

sql

SELECT  
  account_id,  
  COUNT(*) AS F  -- COUNT(*)统计非NULL行总数  
FROM  
  orders  
WHERE  
  total_price IS NOT NULL  -- 先过滤无效数据（WHERE在GROUP BY前执行）  
GROUP BY  
  account_id;

避坑提示：
❗️ MYSQL 中，若字段含多值（如 JSON 数组），直接 SELECT 会取首个元素
✅ 正确做法：先用 JSON_EXTRACT 解析后再分组

3.4 组合查询：一次性计算 R/F/M（含时间函数）

需求：计算近 30 天内的客户 R/F/M 值（当前日期：2025-06-09）

sql

SELECT  
  account_id,  
  DATEDIFF('2025-06-09', MAX(order_date)) AS R,  -- 计算最近消费间隔  
  COUNT(*) AS F,  
  SUM(total_price) AS M  
FROM  
  orders  
WHERE  
  order_date >= '2025-05-10'  -- 筛选近30天数据  
GROUP BY  
  account_id  
HAVING  
  F > 5  -- 分组后筛选：仅保留消费超5次的客户  
ORDER BY  
  M DESC, account_id ASC;  -- 按金额降序，ID升序排序

执行顺序脑图：

plaintext

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

（用箭头图可视化，帮助技术人员快速记忆）

四、高阶技巧：HAVING vs WHERE 的本质区别（附决策树）

（解决新手核心困惑，采用「对比表格 + 决策树」直观呈现，参考《视觉锤》理论）

维度	WHERE	HAVING
执行时机	GROUP BY 前过滤行	GROUP BY 后过滤分组
可用条件	原始列、表达式（如 total_price）	聚合结果（如 COUNT (*)、SUM (M)）
性能影响	优先用 WHERE（减少分组数据量）	分组后过滤（数据量较大时慢）