SQL 用户分层分析：用 RFM 模型识别高价值客户

同样是“购买过的用户”，运营动作不应该完全一样。

有人最近一周刚买过，而且已经买了很多次；有人一年消费金额很高，但最近半年没有下单；还有人只在大促当天买过一次，之后再也没有回来。

如果只看累计消费金额，这三类用户很容易被混在一起。

RFM 模型解决的就是这个问题：

• • R（Recency）最近一次消费时间：用户多久没有购买了
• • F（Frequency）消费频次：用户在统计周期内买了多少次
• • M（Monetary）消费金额：用户在统计周期内贡献了多少钱

今天不讲复杂的机器学习模型，只用 MySQL 8.0 完成一套可以复制到实际项目里的用户分层 SQL：

1. 1. 先明确订单口径和统计周期
2. 2. 聚合出每个用户的 R、F、M
3. 3. 用 NTILE() 把指标转换成 1~5 分
4. 4. 组合 RFM 分数，识别高价值、潜力和流失用户
5. 5. 输出运营可以直接使用的分层结果

一、先把分析口径说清楚

假设有一张订单表 orders，字段如下：

示例数据可能是这样：

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_time DATETIME NOT NULL,
    pay_amount DECIMAL(18, 2) NOT NULL,
    order_status VARCHAR(20) NOT NULL
);

正式计算 RFM 之前，要先确定四个口径。

1. 哪些订单算有效订单？

通常只统计已经支付成功，且没有全额退款的订单。例如：

WHERE order_status IN ('PAID', 'COMPLETED')

如果退款订单仍然保留在订单表里，就不能只看订单状态，还要结合退款金额、退款状态或售后表处理。

2. 统计周期是什么？

RFM 不是永久累计标签，通常会指定一个观察窗口，例如最近 180 天：

order_time >= '2026-02-18 00:00:00'
AND order_time <  '2026-08-16 00:00:00'

这里使用“左闭右开”的时间范围，避免把当天边界重复计算。

3. R 的参考日期是什么？

R 不是直接存储的字段，它需要一个参考日期：

R = 参考日期 - 用户最近一次有效消费日期

如果统计任务每天凌晨运行，参考日期可以使用当天零点；如果是回溯历史数据，则应该固定成分析截止日。

4. F 统计订单数还是购买天数？

最常见的 F 是有效订单数：

COUNT(DISTINCT order_id)

如果一个用户一天可能拆成多个订单，这个指标衡量的是订单频次；如果更关心购买习惯，也可以改成：

COUNT(DISTINCT DATE(order_time))

两者含义不同，不能混用后再拿去比较。

二、先计算用户级 RFM 指标

假设本次分析的截止时间是 2026-08-16 00:00:00，观察最近 180 天的有效订单：

WITH valid_orders AS (
    SELECT
        order_id,
        user_id,
        order_time,
        pay_amount
    FROM orders
    WHERE order_status IN ('PAID', 'COMPLETED')
      AND order_time >= '2026-02-17 00:00:00'
      AND order_time <  '2026-08-16 00:00:00'
),
user_rfm AS (
    SELECT
        user_id,
        DATEDIFF(
            '2026-08-16',
            DATE(MAX(order_time))
        ) AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(pay_amount) AS monetary,
        MAX(order_time) AS last_order_time
    FROM valid_orders
    GROUP BY user_id
)
SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    last_order_time
FROM user_rfm;

这段 SQL 先过滤有效订单，再按用户聚合：

• • recency_days 越小，说明最近购买越近
• • frequency 越大，说明购买越频繁
• • monetary 越大，说明累计贡献越高

需要注意，R 的方向和 F、M 相反：

R 越小越好
F 越大越好
M 越大越好

这也是 RFM 打分最容易写反的地方。

三、用 NTILE 做五档评分

很多文章会直接使用固定阈值，例如消费金额大于 1000 分为 5 分。但固定阈值有两个问题：

1. 1. 不同业务线的金额规模差异很大
2. 2. 业务增长后，原来的阈值会逐渐失效

更通用的方式是使用分位数，把当前用户群切成五档。

1. 给 R 打分

R 越小越好，所以需要先按 recency_days ASC 排序：

NTILE(5) OVER (
    ORDER BY recency_days ASC
) AS r_score

最近的一批用户会得到 1 分，最久没有购买的用户会得到 5 分。

但很多业务习惯把“分数越高越好”，因此可以将 R 的分数反转：

6 - NTILE(5) OVER (
    ORDER BY recency_days ASC
) AS r_score

这样得到的结果就是：

• • 最近购买的用户：5 分
• • 最久没有购买的用户：1 分

2. 给 F 和 M 打分

F、M 都是越大越好，直接按降序排名：

NTILE(5) OVER (
    ORDER BY frequency DESC
) AS f_score,
NTILE(5) OVER (
    ORDER BY monetary DESC
) AS m_score

3. 合并成完整评分

WITH user_rfm AS (
    SELECT
        user_id,
        DATEDIFF(
            '2026-08-16',
            DATE(MAX(order_time))
        ) AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(pay_amount) AS monetary
    FROM orders
    WHERE order_status IN ('PAID', 'COMPLETED')
      AND order_time >= '2026-02-17 00:00:00'
      AND order_time <  '2026-08-16 00:00:00'
    GROUP BY user_id
),
scored_rfm AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        6 - NTILE(5) OVER (
            ORDER BY recency_days 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 user_rfm
)
SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_code,
    r_score + f_score + m_score AS rfm_total_score
FROM scored_rfm;

例如：

555 通常代表当前最值得维护的核心客户；255 则可能是高消费但近期沉默的客户。

四、把分数转换成用户分层

只输出 555、543 这样的编码还不够，业务通常需要更容易理解的标签。

可以先用总分做一版简单分层：

WITH user_rfm AS (
    SELECT
        user_id,
        DATEDIFF('2026-08-16', DATE(MAX(order_time))) AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(pay_amount) AS monetary
    FROM orders
    WHERE order_status IN ('PAID', 'COMPLETED')
      AND order_time >= '2026-02-17 00:00:00'
      AND order_time <  '2026-08-16 00:00:00'
    GROUP BY user_id
),
scored_rfm AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        6 - NTILE(5) OVER (ORDER BY recency_days 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 user_rfm
),
tagged_users AS (
    SELECT
        *,
        r_score + f_score + m_score AS rfm_total_score
    FROM scored_rfm
)
SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    rfm_total_score,
    CASE
        WHEN r_score >= 4
         AND f_score >= 4
         AND m_score >= 4
            THEN '核心客户'
        WHEN r_score >= 4
         AND f_score >= 3
            THEN '高潜客户'
        WHEN r_score <= 2
         AND f_score >= 4
         AND m_score >= 4
            THEN '沉睡高价值客户'
        WHEN r_score >= 4
         AND f_score <= 2
         AND m_score <= 2
            THEN '新客待培育'
        WHEN r_score <= 2
         AND f_score <= 2
            THEN '流失风险客户'
        WHEN rfm_total_score >= 10
            THEN '一般价值客户'
        ELSE '低价值客户'
    END AS customer_segment
FROM tagged_users;

这套规则比单纯按总分分层更有解释性，因为它保留了用户行为结构。

例如：

• • 555：最近买、买得多、花得多，适合会员权益和专属服务
• • 255：过去贡献高，但最近不活跃，适合召回而不是继续泛发优惠
• • 512：最近刚买，但频次和金额还不高，适合首购后的二次转化
• • 112：长期不活跃、购买少，应该控制触达成本

五、处理并列值和排序稳定性

NTILE(5) 会尽量把用户平均分成五组，但它不保证相同指标的用户一定落在同一组。

例如两个用户的消费金额完全相同，却可能因为刚好处在分组边界，被分到不同分数。

如果希望排序稳定，可以增加唯一键作为辅助排序：

NTILE(5) OVER (
    ORDER BY monetary DESC, user_id
) AS m_score

这里的 user_id 不是业务排序依据，只是为了让结果在多次执行时保持稳定。

如果业务更关心“相同金额必须同分”，可以考虑使用 PERCENT_RANK()：

PERCENT_RANK() OVER (
    ORDER BY monetary
) AS monetary_percentile

再通过 CASE 把百分位转换成分数：

CASE
    WHEN monetary_percentile >= 0.8 THEN 5
    WHEN monetary_percentile >= 0.6 THEN 4
    WHEN monetary_percentile >= 0.4 THEN 3
    WHEN monetary_percentile >= 0.2 THEN 2
    ELSE 1
END AS m_score

两种方式的区别是：

• • NTILE() 更接近“平均分组”
• • PERCENT_RANK() 更接近“按相对位置打分”

六、不要忽略退款和异常订单

RFM 的结果高度依赖金额口径。如果退款订单没有被扣除，M 分数会被明显高估。

一个更完整的订单口径可以先计算净支付金额：

WITH order_amount AS (
    SELECT
        o.order_id,
        o.user_id,
        o.order_time,
        o.pay_amount - COALESCE(r.refund_amount, 0) AS net_amount
    FROM orders o
    LEFT JOIN refunds r
        ON o.order_id = r.order_id
    WHERE o.order_status IN ('PAID', 'COMPLETED')
)
SELECT
    user_id,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(net_amount) AS monetary
FROM order_amount
WHERE net_amount > 0
GROUP BY user_id;

如果退款表一笔订单可能有多条退款记录，不能直接和订单表连接，否则会把订单金额重复展开。应该先按订单汇总退款：

WITH refund_by_order AS (
    SELECT
        order_id,
        SUM(refund_amount) AS refund_amount
    FROM refunds
    GROUP BY order_id
),
order_amount AS (
    SELECT
        o.order_id,
        o.user_id,
        o.order_time,
        o.pay_amount - COALESCE(r.refund_amount, 0) AS net_amount
    FROM orders o
    LEFT JOIN refund_by_order r
        ON o.order_id = r.order_id
    WHERE o.order_status IN ('PAID', 'COMPLETED')
)
SELECT
    user_id,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(net_amount) AS monetary
FROM order_amount
WHERE net_amount > 0
GROUP BY user_id;

RFM 不是“把三个字段套进公式”这么简单。订单重复、退款重复和状态口径错误，都会直接改变用户分层结果。

七、把分层结果沉淀成标签表

如果每次看报表都重新计算，运营和分析使用时容易出现口径不一致。可以把结果写入一张每日快照表：

CREATE TABLE user_rfm_snapshot (
    snapshot_date DATE NOT NULL,
    user_id BIGINT NOT NULL,
    recency_days INT NOT NULL,
    frequency INT NOT NULL,
    monetary DECIMAL(18, 2) NOT NULL,
    r_score TINYINT NOT NULL,
    f_score TINYINT NOT NULL,
    m_score TINYINT NOT NULL,
    rfm_total_score TINYINT NOT NULL,
    customer_segment VARCHAR(32) NOT NULL,
    PRIMARY KEY (snapshot_date, user_id)
);

写入快照时，建议保留 snapshot_date，不要只保存当前标签。这样才能回答：

• • 核心客户数量最近一个月是增加还是减少？
• • 哪些用户从高潜客户变成了核心客户？
• • 哪些高价值客户连续三周进入沉睡状态？
• • 一次营销活动之后，各分层的规模如何变化？

每日快照也方便做分层迁移分析：

SELECT
    a.customer_segment AS old_segment,
    b.customer_segment AS new_segment,
    COUNT(*) AS user_count
FROM user_rfm_snapshot a
JOIN user_rfm_snapshot b
  ON a.user_id = b.user_id
 AND a.snapshot_date = '2026-08-15'
 AND b.snapshot_date = '2026-08-16'
GROUP BY
    a.customer_segment,
    b.customer_segment
ORDER BY user_count DESC;

八、按用户分层输出运营动作

最后不要停在“分了几类用户”。分层的价值在于让不同用户得到不同动作。

如果要把动作也输出到 SQL，可以继续加一层映射：

CASE customer_segment
    WHEN '核心客户' THEN '会员权益'
    WHEN '高潜客户' THEN '组合购推荐'
    WHEN '沉睡高价值客户' THEN '个性化召回'
    WHEN '新客待培育' THEN '二次购买引导'
    WHEN '流失风险客户' THEN '低成本唤醒'
    ELSE '常规触达'
END AS recommended_action

九、几个容易踩坑的地方

1. 把退款订单当成正常消费

退款金额不处理，M 会虚高；全额退款订单是否计入 F，也要提前定义。

2. 用当前日期回算历史结果

如果每天直接用 CURRENT_DATE 回算，历史分数会随着时间变化，无法复盘当时的分层结果。生产任务应该固定分析截止日期，并保存每日快照。

3. 只用总分，不看 RFM 结构

255 和 525 可能总分一样，但前者是沉睡高价值客户，后者是近期活跃但消费还不高，运营动作完全不同。

4. 忽略新用户样本不足

刚注册几天的用户天然没有足够时间累积频次和金额。可以单独增加“新注册用户”标签，避免把他们直接判为低价值。

5. 观察周期过长或过短

周期太短，F 和 M 波动大；周期太长，R 对近期变化不敏感。可以根据复购周期选择 90 天、180 天或 365 天，并通过历史数据验证。

十、完整模板

下面是一版可以作为日报或标签任务起点的完整 SQL：

WITH valid_orders AS (
    SELECT
        order_id,
        user_id,
        order_time,
        pay_amount
    FROM orders
    WHERE order_status IN ('PAID', 'COMPLETED')
      AND order_time >= '2026-02-17 00:00:00'
      AND order_time <  '2026-08-16 00:00:00'
),
user_rfm AS (
    SELECT
        user_id,
        DATEDIFF(
            '2026-08-16',
            DATE(MAX(order_time))
        ) AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(pay_amount) AS monetary
    FROM valid_orders
    GROUP BY user_id
),
scored_rfm AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        6 - NTILE(5) OVER (
            ORDER BY recency_days ASC, user_id
        ) AS r_score,
        NTILE(5) OVER (
            ORDER BY frequency DESC, user_id
        ) AS f_score,
        NTILE(5) OVER (
            ORDER BY monetary DESC, user_id
        ) AS m_score
    FROM user_rfm
),
tagged_users AS (
    SELECT
        *,
        r_score + f_score + m_score AS rfm_total_score
    FROM scored_rfm
)
SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_code,
    rfm_total_score,
    CASE
        WHEN r_score >= 4
         AND f_score >= 4
         AND m_score >= 4
            THEN '核心客户'
        WHEN r_score >= 4
         AND f_score >= 3
            THEN '高潜客户'
        WHEN r_score <= 2
         AND f_score >= 4
         AND m_score >= 4
            THEN '沉睡高价值客户'
        WHEN r_score >= 4
         AND f_score <= 2
         AND m_score <= 2
            THEN '新客待培育'
        WHEN r_score <= 2
         AND f_score <= 2
            THEN '流失风险客户'
        WHEN rfm_total_score >= 10
            THEN '一般价值客户'
        ELSE '低价值客户'
    END AS customer_segment
FROM tagged_users
ORDER BY rfm_total_score DESC, monetary DESC;

结语

RFM 的核心不是给用户贴一个“高价值”标签，而是把用户的消费状态拆开看：

• • 最近有没有回来
• • 买得够不够频繁
• • 贡献金额够不够高

SQL 可以很快完成指标计算，但分层规则仍然需要结合业务验证。建议先保存原始 R、F、M 指标和每日快照，再观察不同分层的复购率、客单价和营销响应，持续调整分数边界。

当一张用户标签表能够直接回答“应该重点维护谁、召回谁、减少触达谁”，RFM 才真正从分析模型变成了业务工具。