面试题：计算平台的“30天复购率”

1. 题目背景

在电商和零售业务中，“复购率”是衡量用户粘性和商品吸引力的核心指标。
定义：在某天首次购买商品的用户中，在随后的 30 天内（不含首次购买当天，即区间为 [首次购买日期 + 1天, 首次购买日期 + 30天]）再次发生购买行为的用户比例。

请使用 SparkSQL 编写查询，计算每天新增用户的30天复购率。

2. 示例数据

输入表：订单表 user_orders

期望输出结果

结果解释：

• 2023-01-01 首购用户有 101、102、103（共3人）。
  • 101 在 01-15 再次购买（在30天内），算复购；
  • 102 在 01-01 同天有多次购买不算复购，02-05 再次购买（距离首购 35 天，超出30天），不算复购；
  • 103 无后续购买。
  • 复购率 = 1 / 3 = 33.33%。
• 2023-01-02 首购用户有 104（共1人）。
  • 104 在 01-20 再次购买（在30天内），算复购。
  • 复购率 = 1 / 1 = 100%。

3. SparkSQL 解决方案

WITH user_order_dates AS (
    -- 1. 转换日期格式，并计算每个用户的首次购买日期（首购日）
    SELECT 
        user_id,
        CAST(order_date AS DATE) AS order_date,
        MIN(CAST(order_date AS DATE)) OVER(PARTITION BY user_id) AS first_buy_date
    FROM user_orders
),
user_repurchase_flag AS (
    -- 2. 标记每个首购用户是否在 (首购日, 首购日 + 30天] 区间内有复购行为
    SELECT 
        first_buy_date,
        user_id,
        -- 如果用户存在非首购日，且在30天内的订单，记为1，否则为0
        MAX(
            CASE 
                WHEN order_date > first_buy_date AND order_date <= DATE_ADD(first_buy_date, 30) 
                THEN 1 
                ELSE 0 
            END
        ) AS is_repurchase
    FROM user_order_dates
    GROUP BY first_buy_date, user_id
)
-- 3. 按首购日分组，计算总人数、复购人数及复购率
SELECT 
    first_buy_date,
    COUNT(DISTINCT user_id) AS cohort_size,
    SUM(is_repurchase) AS repurchase_count,
    ROUND(SUM(is_repurchase) / COUNT(DISTINCT user_id), 4) AS repurchase_rate
FROM user_repurchase_flag
GROUP BY first_buy_date
ORDER BY first_buy_date;

4. 面试官视角：SparkSQL 深度解析与调优建议

在实际的 Spark 核心开发或高阶数据仓库面试中，仅写出上述 SQL 只能算及格。面试官通常会针对此题进行追问。以下是帮你“拔高”的分析：

核心考点 1：留存分析（Cohort Analysis）的经典模型

本题是典型的同期群（Cohort）分析。

• 关键点：必须先锁定“初始行为发生的日期”（即首购日 first_buy_date），再用后续行为与其做时间差对比。
• 避坑指南：直接用 Group By 后的 MIN(order_date) 关联原表容易漏掉同一天购买多次的情况。使用窗口函数 MIN(...) OVER(...) 能够保留明细，简化后续的时间差计算。

核心考点 2：时间边界的精确控制

• 定义要求：不含首次购买当天。
• 在条件中必须写成：order_date > first_buy_date（排除了同天多次购买的情况）且 order_date <= DATE_ADD(first_buy_date, 30)。

核心考点 3：Spark 性能调优（高阶加分项）

如果在面试中被问到 “当 user_orders 是一张百亿级的大表，这个 SQL 该怎么优化？”，可以从以下几个维度回答：

1. 避免全局 COUNT(DISTINCT) 带来的数据倾斜：

   • 在最后的聚合中，我们使用了 COUNT(DISTINCT user_id)。在 Spark 中，COUNT(DISTINCT) 会引入两阶段聚合或全局单 Reduce 瓶颈。
   • 优化方案：在 user_repurchase_flag 这一步，我们已经按照 first_buy_date 和 user_id 进行了 GROUP BY，这意味着每个首购日下的 user_id 已经是唯一的了。因此，最后的聚合可以安全地将 COUNT(DISTINCT user_id) 改为 COUNT(user_id)，从而消除了 DISTINCT 的性能隐患。
   sql

   -- 优化后的最终投影层
   SELECT 
       first_buy_date,
       COUNT(user_id) AS cohort_size,  -- 替代 COUNT(DISTINCT user_id)
       SUM(is_repurchase) AS repurchase_count,
       ROUND(SUM(is_repurchase) / COUNT(user_id), 4) AS repurchase_rate
   FROM user_repurchase_flag
   GROUP BY first_buy_date;

2. 窗口函数（Window）与自关联（Self-Join）的选择：

   • 本解法采用窗口函数 MIN() OVER，这会触发一次基于 user_id 的 Shuffle。
   • 如果不用窗口函数，采用先 GROUP BY 求出首购表，再与原表 JOIN 的方式：
     • 若首购表（用户量）较小，Spark 会自动采用 Broadcast Hash Join (BHJ)，避免了大表的 Shuffle，性能会成倍提升。
     • 若用户量极大，Join 会退化为 Sort Merge Join (SMJ)，此时窗口函数由于只对原表进行一次 Shuffle 扫描，表现往往优于大表自关联。

3. 数据倾斜处理：

   • 若某些“爆款”日期（如双十一）导致某天的首购用户极多，在按 first_buy_date 进行第二轮聚合时会出现数据倾斜。
   • 解决方案：可以引入两阶段聚合（加盐局部聚合 + 去盐全局聚合），先将 first_buy_date 加上随机前缀进行聚合，最后再去掉前缀进行二次聚合，打散倾斜 key。