SparkSQL 面试题：用户 RFM 模型指标计算

1. 题目背景与要求

在电商和零售数字化运营中，RFM 模型是衡量客户价值的重要工具：

• R (Recency) 最近一次消费天数：用户最近一次消费时间距离参考时间的天数（天数越小，活跃度越高）。
• F (Frequency) 消费频次：用户在指定时间窗口内的消费次数。
• M (Monetary) 消费总金额：用户在指定时间窗口内的消费总金额。

要求：
假设当前统计参考日期为 2023-10-31。请使用 SparkSQL 编写查询，筛选出过去 90 天内（即 2023-08-02 至 2023-10-31，含双端边界）有消费记录的用户，并计算每个用户的 R、F、M 原始值。

2. 示例数据

输入表：user_orders（用户订单明细表）

期望输出表

数据解析说明：

• user_03 的 ord_105 发生在 5 月 20 日，超出了 90 天范围，故不予计算；仅计算 9 月 1 日的订单。
• user_04 的订单在 7 月 1 日，完全超出 90 天范围，因此输出结果中不包含 user_04。

3. SparkSQL 解决方案

-- 设定参考日期为 '2023-10-31'
WITH filtered_orders AS (
    SELECT 
        user_id,
        order_id,
        CAST(order_date AS DATE) AS order_date,
        amount
    FROM 
        user_orders
    WHERE 
        CAST(order_date AS DATE) >= DATE_SUB(CAST('2023-10-31' AS DATE), 90)
        AND CAST(order_date AS DATE) <= CAST('2023-10-31' AS DATE)
)
SELECT 
    user_id,
    -- R: 计算参考日期与最大（最近）订单日期之间的天数差
    DATEDIFF(CAST('2023-10-31' AS DATE), MAX(order_date)) AS R,
    -- F: 统计去重后的订单数（防止单笔订单数据重复上报造成频次虚高）
    COUNT(DISTINCT order_id) AS F,
    -- M: 统计消费总金额
    ROUND(SUM(amount), 2) AS M
FROM 
    filtered_orders
GROUP BY 
    user_id;

4. 面试官视角：深度分析与答题加分项

在面试中，写出上述 SQL 只能拿到基础分。如果能主动向面试官阐述以下技术细节和生产环境优化点，会极大增加通过概率：

① 日期函数安全与性能优化

• 隐式类型转换风险：在 Spark 3.x 中，直接对 String 类型的日期进行 DATEDIFF 或 DATE_SUB 容易产生时区或格式解析错误。在 SQL 中显式使用 CAST(order_date AS DATE) 可以增强代码的鲁棒性。
• 谓词下推（Predicate Pushdown）：在 WHERE 子句中直接过滤时间，使 Spark 能在扫描数据源（如 Parquet / Delta Lake）时直接利用分区裁剪（Partition Pruning），避免加载过去数年的无用数据。

② 警惕 COUNT(DISTINCT) 引起的数据倾斜

• 痛点：在超大规模数据集下，COUNT(DISTINCT order_id) 会引发全局 Shuffle。如果某个“网红”或“渠道”商家（user_id）拥有海量订单，会导致严重的数据倾斜（Data Skew），导致某个 Executor 内存溢出（OOM）。
• 解决方案（分两阶段聚合）：
  如果面试官追问如何优化倾斜，可以给出双重聚合方案：
  sql

  -- 第一阶段：去重
  WITH stage1 AS (
      SELECT user_id, order_id, MAX(order_date) as order_date, SUM(amount) as amount
      FROM user_orders
      WHERE order_date BETWEEN '2023-08-02' AND '2023-10-31'
      GROUP BY user_id, order_id
  )
  -- 第二阶段：计算 RFM
  SELECT 
      user_id,
      DATEDIFF('2023-10-31', MAX(order_date)) AS R,
      COUNT(1) AS F,
      SUM(amount) AS M
  FROM stage1
  GROUP BY user_id;

③ 业务边界思考：未消费用户的处理

• 追问：“如果我们需要输出近 90 天没消费、但是历史上有消费的用户，怎么处理？”
• 答案：应当使用主用户表（user_info）或者全局历史表作为主表，LEFT JOIN 这 90 天的聚合结果。对于 R 值可以赋予一个默认极大值（如 999），F 和 M 赋予 0。这体现了候选人具备将技术方案与实际业务场景闭环结合的能力。