面试真题：计算用户 RFM 指标

题目背景：
在电商数据分析中，RFM 模型是衡量客户价值和客户创利能力的重要工具。

• R (Recency) 最近一次消费距今的天数：行业常识是该值越小，用户越活跃。
• F (Frequency) 消费频次：在指定期间内用户的消费次数。
• M (Monetary) 消费总金额：在指定期间内用户的消费总额。

题目要求：
请编写 SQL 语句，根据给定的用户消费记录表 user_orders，计算过去 90 天内（假设当前统计日期为 2023-12-31，即统计区间为 2023-10-03 至 2023-12-31）每个用户的 R、F、M 原始指标值。

输入数据：user_orders（用户消费记录表）

(注：用户 104 的消费时间在 90 天之前，应在计算中被过滤)

期望输出结果

参考答案 (SQL)

以下编写基于标准 SQL (兼容 MySQL 8.0+, Hive SQL, Presto 等主流引擎)：

SELECT
    user_id,
    -- 计算 R：统计日期 '2023-12-31' 减去最大（最近一次）订单日期的天数
    DATEDIFF('2023-12-31', MAX(order_date)) AS recency,
    -- 计算 F：订单总数
    COUNT(order_id) AS frequency,
    -- 计算 M：订单总金额
    SUM(order_amount) AS monetary
FROM
    user_orders
WHERE
    -- 限制时间窗口在过去 90 天内（含 2023-12-31 当天）
    order_date >= DATE_SUB('2023-12-31', INTERVAL 90 DAY)
    AND order_date <= '2023-12-31'
GROUP BY
    user_id;

SQL 解析与面试避坑指南

为了在面试中脱颖而出，仅写出上述 SQL 是不够的。以下是针对此题的深度剖析和面试官可能追问的延伸点：

1. 核心函数与逻辑拆解

• 时间窗口过滤 (WHERE)：
  • 必须先通过 WHERE 子句过滤出 90 天内的数据，再进行 GROUP BY。
  • 使用 DATE_SUB('2023-12-31', INTERVAL 90 DAY) 可以动态计算出开始日期（即 2023-10-02 之后）。在面试中，若想展示严谨性，可以向面试官确认：“90天”是否包含当天，边界值如何处理。
• 最近一次消费天数 (R 的计算)：
  • 利用 MAX(order_date) 找到用户最近一次消费的日期。
  • 使用 DATEDIFF(end_date, start_date) 计算天数差。注意：不同数据库的 DATEDIFF 参数顺序不同（例如 MySQL 中是 DATEDIFF(expr1, expr2) 即 expr1 - expr2；而 SQL Server 或 Hive 的某些版本有差异），面试时可以主动说明这一点，展现多引擎经验。

2. 面试高频追问/避坑点

Q1：如果某个用户在 90 天内没有任何消费，他应该出现在结果里吗？

• 分析：根据 WHERE 过滤，没有消费的用户会被直接排除。
• 回答套路：主动向面试官确认业务定义：“如果用户在90天内无消费，是直接排除（如上文 SQL 所示），还是需要保留该用户，但将 R、F、M 标记为 NULL 或 0？”。如果要保留，则需要从 user_info 用户主表出发，使用 LEFT JOIN 关联订单表。

Q2：如何对计算出来的 R、F、M 进行打分（分箱/得分化）？

• 分析：这是 RFM 模型的下一步。通常会使用 NTILE(5) 函数或者 CASE WHEN 将原始值映射到 1~5 分。
• 加分回答：
  sql

  -- 延伸：利用 NTILE 将 F 值和 M 值分为 5 个档次（5分最高，1分最低）
  -- R 值因为是越小越好，所以需要反向打分（或者在 NTILE 后用 6 - NTILE）
  SELECT 
      user_id,
      NTILE(5) OVER (ORDER BY recency DESC) AS r_score, -- recency 越大，得分越低
      NTILE(5) OVER (ORDER BY frequency ASC) AS f_score, -- frequency 越大，得分越高
      NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM (...)

Q3：如果同一天内一个用户下了多笔订单，F（频次）怎么算？

• 分析：有些业务场景下，“频次”定义为“消费天数”，有些定义为“订单总数”。
• 回答套路：
  • 如果是订单总数，直接使用 COUNT(order_id)。
  • 如果是消费天数，需要使用 COUNT(DISTINCT order_date)。主动提及这一点会显得你具有极强的业务严谨性。