一道综合题收官：用SQL实现RFM用户分层

SQL手册第 10 题约 10 分钟读完

本文目录

什么是RFM模型？
面试原题
完整实现：四步走
完整SQL（合并版）
加一步：看各分层的用户分布
实战经验：我在出海业务中怎么用的
面试追问

最后一篇，拿一道综合题收官。RFM模型是用户运营中最经典的分层方法，一条SQL就能实现，但它会用到前面9天几乎所有的知识点：聚合、窗口函数、CASE WHEN、子查询。能完整写出来，你的SQL面试基本就稳了。

什么是RFM模型？

RFM是三个维度的缩写：

R（Recency）： 最近一次消费距今多少天——越近越好

F（Frequency）： 一段时间内消费了多少次——越多越好

M（Monetary）： 一段时间内消费了多少钱——越多越好

根据这三个维度的高低组合，可以把用户分成不同的群体，然后针对性地做运营策略。

用户类型	R	F	M	运营策略
---------	---	---	---	---------
重要价值用户	高	高	高	VIP服务，维持满意度
重要发展用户	高	低	高	提升复购频次
重要保持用户	低	高	高	流失预警，召回
重要挽留用户	低	低	高	大额流失，重点挽回
一般价值用户	高	高	低	引导升单
一般发展用户	高	低	低	新客培养
一般保持用户	低	高	低	低价值高频，观察
流失用户	低	低	低	低成本触达或放弃

面试原题

给定用户订单表，实现RFM用户分层。

-- 订单表
user_order(
  user_id       STRING,
  order_id      STRING,
  order_amount  DECIMAL(10,2),
  order_time    TIMESTAMP,
  dt            STRING
)

完整实现：四步走

第一步：计算RFM原始值

WITH rfm_raw AS (
  SELECT
    user_id,
    -- R：最近一次消费距今天数
    DATEDIFF('2025-02-11', MAX(DATE(order_time))) AS recency,
    -- F：最近90天的订单次数
    COUNT(DISTINCT order_id) AS frequency,
    -- M：最近90天的消费总额
    SUM(order_amount) AS monetary
  FROM user_order
  WHERE dt >= DATE_SUB('2025-02-11', 90)  -- 最近90天
    AND order_amount > 0                    -- 排除退款
  GROUP BY user_id
)

这里有几个细节：

R用DATEDIFF算天数，数值越小说明越近，是"好"的

F用COUNT(DISTINCT order_id)而不是COUNT(*)，避免一个订单多条记录被重复计算

M排除了order_amount <= 0的记录（退款/取消）

时间窗口90天，实际业务中根据行业调整（高频消费品可能用30天，低频用180天）

第二步：RFM打分

把连续值转换成离散分数。最常见的方法是用分位数，把每个维度分成5档：

, rfm_scored AS (
  SELECT
    user_id,
    recency,
    frequency,
    monetary,
    -- R打分：天数越小分越高（5分最好）
    NTILE(5) OVER (ORDER BY recency ASC) AS r_score,
    -- F打分：次数越多分越高
    NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
    -- M打分：金额越高分越高
    NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
  FROM rfm_raw
)

为什么用NTILE而不是手动划分区间？

NTILE是窗口函数，把数据等分成N组。好处是不需要预先知道数据的分布，它会自动根据排名均匀分组。手动划分（比如0-30天给5分，31-60天给4分）需要你了解数据分布，而且不同业务的合理区间完全不同。

注意： R的排序是ASC（天数越小越好，排在前面得高分），F和M的排序是DESC（越大越好）。这个方向很容易搞反。

第三步：简化为高/低二分类

8种用户类型太多了，面试中通常简化为高低两档。常见做法：大于均值（或大于等于3分）为"高"，否则为"低"。

, rfm_labeled AS (
  SELECT
    user_id,
    recency, frequency, monetary,
    r_score, f_score, m_score,
    CASE WHEN r_score >= 3 THEN '高' ELSE '低' END AS r_level,
    CASE WHEN f_score >= 3 THEN '高' ELSE '低' END AS f_level,
    CASE WHEN m_score >= 3 THEN '高' ELSE '低' END AS m_level
  FROM rfm_scored
)

第四步：用户分层命名

SELECT
  user_id,
  recency, frequency, monetary,
  r_score, f_score, m_score,
  r_level, f_level, m_level,
  CASE
    WHEN r_level = '高' AND f_level = '高' AND m_level = '高' THEN '重要价值用户'
    WHEN r_level = '高' AND f_level = '低' AND m_level = '高' THEN '重要发展用户'
    WHEN r_level = '低' AND f_level = '高' AND m_level = '高' THEN '重要保持用户'
    WHEN r_level = '低' AND f_level = '低' AND m_level = '高' THEN '重要挽留用户'
    WHEN r_level = '高' AND f_level = '高' AND m_level = '低' THEN '一般价值用户'
    WHEN r_level = '高' AND f_level = '低' AND m_level = '低' THEN '一般发展用户'
    WHEN r_level = '低' AND f_level = '高' AND m_level = '低' THEN '一般保持用户'
    WHEN r_level = '低' AND f_level = '低' AND m_level = '低' THEN '流失用户'
  END AS user_segment
FROM rfm_labeled

完整SQL（合并版）

面试的时候可以直接写这个合并版：

WITH rfm_raw AS (
  SELECT
    user_id,
    DATEDIFF('2025-02-11', MAX(DATE(order_time))) AS recency,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_amount) AS monetary
  FROM user_order
  WHERE dt >= DATE_SUB('2025-02-11', 90)
    AND order_amount > 0
  GROUP BY user_id
),
rfm_scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY recency ASC)   AS r_score,
    NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary DESC)  AS m_score
  FROM rfm_raw
)
SELECT
  user_id,
  recency, frequency, monetary,
  r_score, f_score, m_score,
  CASE
    WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN '重要价值用户'
    WHEN r_score >= 3 AND f_score <  3 AND m_score >= 3 THEN '重要发展用户'
    WHEN r_score <  3 AND f_score >= 3 AND m_score >= 3 THEN '重要保持用户'
    WHEN r_score <  3 AND f_score <  3 AND m_score >= 3 THEN '重要挽留用户'
    WHEN r_score >= 3 AND f_score >= 3 AND m_score <  3 THEN '一般价值用户'
    WHEN r_score >= 3 AND f_score <  3 AND m_score <  3 THEN '一般发展用户'
    WHEN r_score <  3 AND f_score >= 3 AND m_score <  3 THEN '一般保持用户'
    ELSE '流失用户'
  END AS user_segment
FROM rfm_scored

加一步：看各分层的用户分布

面试官可能追问"跑出来之后你怎么看结果"：

WITH rfm_result AS (
  -- 上面的完整SQL
)
SELECT
  user_segment,
  COUNT(1) AS user_cnt,
  ROUND(COUNT(1) * 100.0 / SUM(COUNT(1)) OVER (), 2) AS pct,
  ROUND(AVG(recency), 1) AS avg_recency,
  ROUND(AVG(frequency), 1) AS avg_frequency,
  ROUND(AVG(monetary), 2) AS avg_monetary
FROM rfm_result
GROUP BY user_segment
ORDER BY user_cnt DESC

这条SQL输出每个分层的用户数量、占比、以及各维度的均值。运营拿到这个结果就能制定针对性策略了。

实战经验：我在出海业务中怎么用的

短剧出海的付费场景和传统电商不太一样——用户充值的是虚拟货币（coin），用来解锁剧集。RFM模型要做一些调整：

R：最近一次充值距今天数

F：最近30天充值次数（短剧消费频率比电商高）

M：累计充值金额（不只是最近90天，因为大R用户可能集中充一次用很久）

另外一个调整是分层的阈值。我们发现NTILE均匀分组的效果不好——因为大部分用户只充过一次几块钱，头部大R用户充值额能到几千美金。均匀分组会导致前4档全是小额用户，第5档全是大额用户，区分度不够。

后来改用了业务经验分档：

CASE
  WHEN monetary >= 100 THEN 5  -- 大R用户
  WHEN monetary >= 30  THEN 4
  WHEN monetary >= 10  THEN 3
  WHEN monetary >= 3   THEN 2
  ELSE 1
END AS m_score

阈值是根据充值金额的分布和业务经验定的。面试的时候如果你能讲出"NTILE不一定好用，有时候需要根据业务调整分档逻辑"，说明你真的做过这个事情。

面试追问

Q：RFM的缺点是什么？

主要有三个：一是三个维度的权重相同（但实际上M可能比F更重要），二是只看历史行为没有预测能力，三是只用了消费数据没考虑行为数据（浏览、收藏等）。更高级的做法是用机器学习做用户分群（K-Means聚类），但RFM的优点是简单、可解释、好落地。

Q：这个结果怎么落地？

通常RFM的结果会写到用户标签表里，被推送系统、运营后台、广告投放系统调用。比如"重要保持用户"触发自动召回短信，"重要发展用户"推荐高客单价商品。

刷题进度保存在当前浏览器