播面

面试题：用户群组留存率分析 (Cohort Retention)

1. 题目描述

在电商和APP运营中，群组留存率（Cohort Retention）是衡量用户粘性和生命周期价值（LTV）的核心指标。
请编写一段 SparkSQL 脚本，以用户首次购买月份作为群组分类（如 "2025-01" 群组），计算每个群组在首购后 第 1 个月、第 2 个月、第 3 个月 的购买留存率（只要该月有购买行为即视为留存，结果保留两位小数，并以百分比展示，如 33.33%）。

2. 样例数据

输入表：user_orders (用户订单表)

3. 期望输出

样例数据解析：

• 2025-01群组：包含 User_A, User_B, User_D 共 3 人。
  • 第 1 个月（2025-02）：只有 User_A 购买，留存率 1/3 = 33.33%
  • 第 2 个月（2025-03）：只有 User_B 购买，留存率 1/3 = 33.33%
  • 第 3 个月（2025-04）：只有 User_A 购买，留存率 1/3 = 33.33%
• 2025-02群组：包含 User_C 共 1 人。
  • 第 1 个月（2025-03）：User_C 购买，留存率 1/1 = 100.00%

4. SparkSQL 解决方案

WITH user_first_purchase AS (
    -- Step 1: 计算每个用户的首次购买日期
    SELECT 
        user_id,
        MIN(CAST(order_date AS DATE)) AS first_purchase_date
    FROM user_orders
    GROUP BY user_id
),

user_cohort_diff AS (
    -- Step 2: 关联原表，计算每次购买与首次购买的月份差值值
    SELECT DISTINCT
        o.user_id,
        f.first_purchase_date,
        DATE_FORMAT(f.first_purchase_date, 'yyyy-MM') AS cohort_month,
        -- 使用 MONTHS_BETWEEN 计算自然月差值，ROUND 消除因日期天数不同产生的浮点误差
        CAST(ROUND(MONTHS_BETWEEN(TRUNC(CAST(o.order_date AS DATE), 'MM'), TRUNC(f.first_purchase_date, 'MM'))) AS INT) AS month_diff
    FROM user_orders o
    JOIN user_first_purchase f ON o.user_id = f.user_id
)

-- Step 3: 分组聚合，利用条件计数（Conditional Aggregation）计算留存率
SELECT 
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    
    -- 第 1 个月留存率
    CONCAT(
        ROUND(COUNT(DISTINCT CASE WHEN month_diff = 1 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2), 
        '%'
    ) AS m1_retention_rate,
    
    -- 第 2 个月留存率
    CONCAT(
        ROUND(COUNT(DISTINCT CASE WHEN month_diff = 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2), 
        '%'
    ) AS m2_retention_rate,
    
    -- 第 3 个月留存率
    CONCAT(
        ROUND(COUNT(DISTINCT CASE WHEN month_diff = 3 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2), 
        '%'
    ) AS m3_retention_rate
FROM user_cohort_diff
GROUP BY cohort_month
ORDER BY cohort_month;

5. 面试考点与深度解析

在 SparkSQL 面试中，该题属于中高难度的常规业务分析题。面试官主要考察以下几个技术维度：

① 日期处理函数的熟练度（Date Functions）

• 陷阱点：直接用天数除以 30（如 DATEDIFF / 30）计算月份差是不准确的，因为不同月份天数不同（如2月、大月、小月）。
• 破局点：
  • 使用 TRUNC(date, 'MM') 将日期截断到当月第一天。
  • 配合 MONTHS_BETWEEN(date1, date2) 计算精准的月份差。由于计算结果可能是浮点数，需要用 ROUND 进行四舍五入并转换为整型。

② 核心业务逻辑的表达：条件聚合（Conditional Aggregation）

• 留存率计算的精髓在于分母保持一致（群组初始人数），分子动态变化（特定月份留存人数）。
• 很多初学者会尝试用多次 JOIN 或 WINDOW 函数去硬拼，导致代码臃肿、性能低下。
• 最佳实践是使用 COUNT(DISTINCT CASE WHEN ... THEN ... END)。这种在 COUNT 内部进行条件判定（CASE WHEN）的写法，可以在一次 GROUP BY 中计算出所有目标月份的留存人数，极大地减少了 Shuffle 阶段的数据传输。

③ 性能优化（Spark Performance Tuning）

当面试官追问：“如果 user_orders 是个万亿级的大表，这个 SQL 应该如何优化？”你可以从以下几个维度作答：

1. 避免全局唯一键去重导致的倾斜（Distinct Optimization）：
   • COUNT(DISTINCT user_id) 会触发全局的 Shuffle。如果存在热点用户（如爬虫、公共账号），会导致严重的数据倾斜（Data Skew）。
   • 优化方案：在 Step 2 中，已经通过 SELECT DISTINCT o.user_id, ... 提前在 Map 端进行了局部去重，降低了最后一步 COUNT(DISTINCT) 的数据量。
2. 广播连接（Broadcast Join）：
   • user_first_purchase（每个用户的首次购买日期表）的大小取决于独立用户数（通常远小于订单总数）。
   • 如果该表小于 spark.sql.autoBroadcastJoinThreshold（默认10MB），Spark 会自动进行 Broadcast Hash Join。在代码中，也可以显式加上 Hint：/*+ BROADCAST(f) */，将大表与小表关联转化为本地 Map 侧 Join，避免大表之间的全局 Shuffle。
3. 数据预分区与桶（Bucket）：
   • 如果 user_orders 已经是按照 user_id 进行分桶（Bucketing）的表，Join 过程将实现 SortMergeJoin (SMJ) 免 Shuffle，能成倍提升运行效率。