本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
SQL 实战编程题（GitHub 高频题） · 时间序列与增长分析

RFM 用户分群的 SQL 实现？(Recency / Frequency / Monetary 三个维度的分位数)

SQL implementation of RFM user segmentation? (based on quantiles of the Rec

答案语言

考察要点

这道题面试官想看你是否懂：

  1. 业务理解与 SQL 转化能力：能否将 RFM 这种业务模型准确地翻译成 SQL 查询逻辑，正确计算 R（最近一次消费时间）、F（消费频率）、M（消费金额）三个指标。
  2. 高级 SQL 语法：是否熟练掌握窗口函数（特别是 NTILE）和公用表表达式（CTE），这是实现分群最高效、最优雅的方式。
  3. SQL 性能与优化意识：是否考虑到大数据量下的查询性能，并能提出基于索引、预计算等工程化的解决方案。

核心概念

RFM 是一种经典的用户价值分析模型，通过三个核心指标对用户进行分群，以实现精细化运营。

  1. Recency (R) - 最近一次消费时间: 用户距离最后一次消费有多久。R 值越小，代表用户最近消费过，价值越高。

    • 计算: DATEDIFF(分析日期, MAX(用户下单日期))。通常分析日期为 CURDATE() 或数据快照的最新日期。

  2. Frequency (F) - 消费频率: 用户在特定时间段内的消费次数。F 值越大，代表用户是忠实的回头客，价值越高。

    • 计算: COUNT(DISTINCT 订单ID)。注意要去重，避免一个订单多行商品被重复计算。

  3. Monetary (M) - 消费金额: 用户在特定时间段内的总消费金额。M 值越大，代表用户贡献的收入越多，价值越高。

    • 计算: SUM(订单金额)。

  4. 分位数 (Quantile): 将一个有序数据集切分成 n 个等份的分割点。在 RFM 中，我们通常使用 NTILE(n) 窗口函数将用户按 R、F、M 各自的得分分成 n 个等级。例如，NTILE(5) 会将用户分成 5 个等级（1-5分）。

    • R 的评分: Recency 值越小越好，所以需要按 R 升序排列。NTILE(5) OVER (ORDER BY recency ASC)。
    • F 和 M 的评分: Frequency 和 Monetary 值越大越好，所以需要按 F 和 M 降序排列。NTILE(5) OVER (ORDER BY frequency DESC)。

数据示例 假设我们有一个 orders 表：

order_iduser_idorder_dateamount
11012023-10-01100
21022023-10-1050
31012023-11-15200
41032023-09-0580
51022023-11-20150

假设分析日期为 2023-12-01，计算出的 RFM 原始值：

user_idRecency (天)FrequencyMonetary
101162300
102112200
10387180

接下来使用 NTILE(3) 对其进行评分：

  • R 分: 102 (11天) -> 1分, 101 (16天) -> 2分, 103 (87天) -> 3分 (升序)
  • F 分: 101/102 (2次) -> 1分, 103 (1次) -> 2分 (降序)
  • M 分: 101 (300) -> 1分, 102 (200) -> 2分, 103 (80) -> 3分 (降序)

多种解法

解法 1：CTE + 窗口函数 NTILE（推荐）

这是最现代、可读性最好、性能也最优的解法。通过 CTE 逐步构建 RFM 值，最后用窗口函数进行评分。

  • 版本: ✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle
  • SQL:
sql
 1-- 假设当前分析日期为 '2023-12-31'
 2WITH
 3  -- Step 1: 计算每个用户的原始 R, F, M 值
 4  rfm_raw AS (
 5    SELECT
 6      user_id,
 7      DATEDIFF('2023-12-31', MAX(order_date)) AS recency,
 8      COUNT(DISTINCT order_id) AS frequency,
 9      SUM(amount) AS monetary
10    FROM orders
11    GROUP BY
12      user_id
13  ),
14  -- Step 2: 使用 NTILE 窗口函数为 R, F, M 打分
15  rfm_scores AS (
16    SELECT
17      user_id,
18      recency,
19      frequency,
20      monetary,
21      -- Recency: 值越小越好，所以用 ASC
22      NTILE(5) OVER (ORDER BY recency ASC) AS r_score,
23      -- Frequency: 值越大越好，所以用 DESC
24      NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
25      -- Monetary: 值越大越好，所以用 DESC
26      NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
27    FROM rfm_raw
28  )
29-- Step 3: 输出最终结果，可以拼接 RFM 分数或进行分类
30SELECT
31  user_id,
32  recency,
33  frequency,
34  monetary,
35  r_score,
36  f_score,
37  m_score,
38  CONCAT(r_score, f_score, m_score) AS rfm_segment
39FROM rfm_scores
40ORDER BY
41  user_id;
  • 时间/空间复杂度:
    • Step 1 (rfm_raw): O(Nlog⁡N)O(N \log N) 或 O(N)O(N)，取决于 GROUP BY 的实现（排序或哈希）。
    • Step 2 (rfm_scores): NTILE 需要排序，复杂度为 O(Ulog⁡U)O(U \log U)，其中 U 是用户数。
    • 总体复杂度由数据量最大的步骤决定，通常是 Step 1。
  • 索引依赖: 为了加速 Step 1 的 GROUP BY，在 orders 表上建立 (user_id, order_date) 的复合索引会非常有效。amount 列通常不需要索引。

解法 2：子查询 + 用户变量（兼容老版本）

在 MySQL 8.0 之前，没有窗口函数，可以借助用户变量来模拟排序和行号分配，从而计算分位数。这种方法可读性差，且在 MySQL 8.0+ 中已被官方不推荐使用。

  • 版本: ✅ MySQL 5.7；❌ MySQL 8.0+ (不保证执行顺序)
  • SQL:
sql
 1-- 这种方法非常繁琐，需要对每个指标分别排序和计算分位数，这里仅演示 F 值的计算过程
 2-- 假设已通过子查询或临时表得到 rfm_raw
 3-- SET @total_users = (SELECT COUNT(*) FROM rfm_raw);
 4

 5-- 计算 F 分数
 6SELECT
 7    user_id,
 8    frequency,
 9    -- 通过排名和总用户数计算分位数
10    CEIL(f_rank / (@total_users / 5)) AS f_score
11FROM (
12    SELECT
13        user_id,
14        frequency,
15        @rank := @rank + 1 AS f_rank
16    FROM
17        rfm_raw,
18        (SELECT @rank := 0) AS r
19    ORDER BY
20        frequency DESC
21) AS ranked_frequency;
22

23-- 要完成整个 RFM，需要对 R, F, M 分别执行类似上面的操作，然后将结果 JOIN 起来，非常复杂。
24-- 更好的兼容方案是使用多次自连接的子查询，但性能极差，接近 O(N^2)，不推荐。
  • 复杂度: 每次排序都需要 O(Ulog⁡U)O(U \log U)，并且需要多次扫描和连接，整体性能远不如窗口函数。
  • 何时使用: 仅当必须在不支持窗口函数的旧版 MySQL 上实现时才考虑。在面试中，可以提及此方案以展示知识广度，但要明确指出其缺点和窗口函数的优越性。

常见低分回答

  1. COUNT(*) vs COUNT(DISTINCT order_id): 在计算频率时，如果 orders 表是订单商品明细表（一个订单有多行），使用 COUNT(*) 会错误地将商品行数计为订单数，导致 F 值虚高。
  2. Recency 计算错误: DATEDIFF(MAX(order_date), '2023-12-31') 这样把日期参数搞反，导致 Recency 为负数。或者忘记 Recency 是值越小越好，在 NTILE 中错误地使用了 DESC 排序。
  3. 滥用 RANK() 或 DENSE_RANK(): RANK() 会产生不连续的排名（1, 2, 2, 4），DENSE_RANK() 会产生连续排名（1, 2, 2, 3）。而 NTILE(n) 的目标是均匀分桶，语义完全不同。如果面试官要求的是分位数分群，用 RANK 是答非所问。
  4. 不使用 CTE，写成庞大的嵌套子查询: SELECT ... FROM (SELECT ... FROM (SELECT ... GROUP BY ...)) ... 这样的代码可读性和可维护性极差，是减分项。CTE 能让逻辑分层，清晰易懂。
  5. 忽略 NULL 值处理: 如果 amount 列可能为 NULL，SUM(amount) 会忽略 NULL 值，但如果一个用户所有订单的 amount 都是 NULL，SUM 结果是 NULL。这可能导致在后续评分中出现问题。应考虑使用 COALESCE(SUM(amount), 0)。

工程实践与优化

  • 物化视图/汇总表: RFM 分析通常是 T+1 的离线计算。在生产环境中，我们会将这个 SQL 任务配置成一个每日执行的批处理作业（如用 Airflow 调度），将结果写入一个用户标签表或物化视图 user_rfm_scores。线上业务直接查询这个轻量的结果表，实现毫秒级响应，而不是在 OLTP 库上实时运行这个重量级查询。
  • 执行计划分析:
    • 对于解法 1，EXPLAIN 的输出应该显示：
      1. rfm_raw CTE: type: index 或 range，利用 (user_id, order_date) 索引执行 GROUP BY。Extra 字段可能显示 Using index。
      2. rfm_scores CTE: Extra 字段会显示 Window aggregate，表示正在执行窗口函数。
    • 关键是确保第一步聚合的效率。如果 type 是 ALL（全表扫描），说明索引未生效，需要检查索引设计。
  • 索引选择: orders 表的核心索引是 (user_id, order_date)。user_id 在前，可以快速定位到用户的所有订单；order_date 在后，方便 GROUP BY 找到 MAX(order_date) 并进行排序。
  • 避免大事务: 如果计算结果需要 INSERT 或 UPDATE 到汇总表，应分批处理，避免产生锁住几百万行的单个大事务，影响线上数据库稳定性。

常见追问与延伸题目

  1. 追问：如果 orders 表有 100 亿行，你的 MySQL 方案还适用吗？该如何优化？

    • 回答要点:
      • 不适用: 在百亿级别的表上直接对 OLTP 数据库（如 MySQL）执行如此复杂的聚合和窗口函数查询，会严重冲击主库性能，甚至导致数据库崩溃。
      • 解决方案: 必须将计算任务迁移到专门的分析平台（OLAP）或数据仓库。
      • 架构一（大数据平台）: 将 orders 数据同步到数据湖（如 S3/HDFS），使用 Spark SQL 或 Hive 来执行完全相同的 SQL 逻辑。分布式计算引擎能横向扩展，轻松处理 PB 级数据。计算结果存回 Hive 表或同步到 HBase/Redis 等 KV 存储供线上查询。
      • 架构二（OLAP 数据库）: 将数据导入到 ClickHouse, StarRocks, 或 Snowflake 等列式存储的 OLAP 数据库。这些数据库为聚合查询和窗口函数做了深度优化，查询速度比 MySQL 快几个数量级。SQL 语法基本兼容。

  2. 追问：业务方希望动态调整分群数量，比如从 5 分位改成 4 分位或 10 分位，如何让你的 SQL 更灵活？

    • 回答要点:
      • 参数化: NTILE(5) 中的 5 是硬编码。可以将 SQL 封装在存储过程或应用层代码中，将分位数 n 作为参数传入。
      • 存储过程示例:
        sql
         1CREATE PROCEDURE sp_calculate_rfm(IN n_quantiles INT)
         2BEGIN
         3  -- SQL 主体部分...
         4  -- NTILE(5) 替换为 NTILE(n_quantiles)
         5  -- ...
         6END;
      • 应用层处理: 在 Python 或 Java 代码中，通过字符串格式化动态生成 SQL，例如 sql = f"SELECT ..., NTILE({n}) OVER (...) FROM ..."。这种方式更灵活，也更容易集成到自动化任务中。

  3. 追问：除了 RFM，你还知道哪些用户分层模型？如果要你用 SQL 实现“用户生命周期”分层（新用户、活跃用户、流失用户），你会怎么做？

    • 回答要点:
      • 其他模型: 除了 RFM，还有 CLV (Customer Lifetime Value, 用户生命周期价值)、AARRR (海盗指标)、行为聚类等。这展示了你对数据分析领域的广度认知。
      • 用户生命周期 SQL 实现思路:
        1. 定义规则: 首先和业务方明确“新用户”、“活跃用户”、“流失用户”的定义。例如：
          • 新用户: 首次消费在最近 30 天内。
          • 活跃用户: 最近 30 天内有消费，且非新用户。
          • 流失用户: 最后一次消费在 90 天以前。
          • 沉睡用户: 最后一次消费在 30-90 天之间。
        2. SQL 实现:
          sql
           1-- 假设当前分析日期为 '2023-12-31'
           2WITH user_activity AS (
           3  SELECT
           4    user_id,
           5    MIN(order_date) AS first_order_date,
           6    MAX(order_date) AS last_order_date
           7  FROM orders
           8  GROUP BY user_id
           9)
          10SELECT
          11  user_id,
          12  CASE
          13    WHEN DATEDIFF('2023-12-31', first_order_date) <= 30 THEN '新用户'
          14    WHEN DATEDIFF('2023-12-31', last_order_date) <= 30 THEN '活跃用户'
          15    WHEN DATEDIFF('2023-12-31', last_order_date) > 90 THEN '流失用户'
          16    ELSE '沉睡用户'
          17  END AS lifecycle_segment
          18FROM user_activity;
        • 这个回答展示了你不仅能解决眼前问题，还能举一反三，处理其他类似的业务分析需求。
← Previous
DAU、WAU、MAU 的查询和它们的关系？粘性 = DAU/MAU 的 SQL？
Next →
漏斗转化分析的 SQL？(funnel 步骤间的留存)