SparkSQL 面试题：用户首购 Cohort LTV（生命周期价值）累计 GMV 计算

1. 题目背景

在电商和零售业务中，Cohort Analysis（同期群分析） 是评估用户留存和生命周期价值（LTV, Lifetime Value）的核心方法。通过追踪同一批次（如同一月份首次下单）的用户在后续不同时间跨度内的累计消费金额（GMV），运营团队可以评估获客质量以及用户的长期价值。

2. 题目要求

给定一张用户历史订单表 user_orders，请编写 SparkSQL 查询：

1. 确定每个用户的首购月份（First Purchase Month）。
2. 计算每个首购月份的用户群体，在首购当月（记为第 0 个月）以及后续 12 个月内（第 1 到第 12 个月），累计贡献的 GMV。
3. 最终输出结果包含：首购月份、相对月份（0 至 12）、该相对月份的当月 GMV、以及截止到该相对月份的累计 GMV。

3. 示例数据

输入数据表：user_orders（订单表）

期望输出结果 (部分展示)

4. SparkSQL 解决方案

WITH user_first_purchase AS (
    -- Step 1: 计算每个用户的首购日期及首购月份
    SELECT 
        user_id,
        MIN(order_date) AS first_purchase_date,
        DATE_FORMAT(MIN(order_date), 'yyyy-MM') AS first_purchase_month
    FROM user_orders
    GROUP BY user_id
),

order_with_relative_month AS (
    -- Step 2: 关联原表，计算每笔订单相对于首购月份的月份差（relative_month）
    SELECT 
        o.user_id,
        u.first_purchase_month,
        o.gmv,
        -- 使用 months_between 计算月份差，并转为整数
        CAST(
            (CAST(SPLIT(DATE_FORMAT(o.order_date, 'yyyy-MM'), '-')[0] AS INT) - CAST(SPLIT(u.first_purchase_month, '-')[0] AS INT)) * 12 +
            (CAST(SPLIT(DATE_FORMAT(o.order_date, 'yyyy-MM'), '-')[1] AS INT) - CAST(SPLIT(u.first_purchase_month, '-')[1] AS INT))
            AS INT
        ) AS relative_month
    FROM user_orders o
    JOIN user_first_purchase u ON o.user_id = u.user_id
),

cohort_monthly_gmv AS (
    -- Step 3: 过滤出后续12个月内的订单，并按首购月和相对月聚合当月 GMV
    SELECT 
        first_purchase_month,
        relative_month,
        SUM(gmv) AS monthly_gmv
    FROM order_with_relative_month
    WHERE relative_month BETWEEN 0 AND 12
    GROUP BY first_purchase_month, relative_month
)

-- Step 4: 使用窗口函数计算累计 GMV
SELECT 
    first_purchase_month,
    relative_month,
    monthly_gmv,
    SUM(monthly_gmv) OVER (
        PARTITION BY first_purchase_month 
        ORDER BY relative_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_gmv
FROM cohort_monthly_gmv
ORDER BY first_purchase_month, relative_month;

5. 核心考点与 SparkSQL 深度解析

对于面试官而言，这道题不仅考察了基础的 SQL 编写能力，更能筛选出对窗口函数、数据倾斜、Spark 算子优化有深入理解的高级开发人员。

解析 1：月份差（Relative Month）的精准计算

• 痛点：Spark 的内置函数 months_between 返回的是浮点数（基于天数计算），直接 CAST AS INT 容易因为月中、月末的天数差异导致计算偏大或偏小。
• 解法：在 SQL 中，推荐使用“年差 * 12 + 月差”的方法精确计算自然月跨度。即：
  $$\text{Relative Month} = (\text{Order Year} - \text{Start Year}) \times 12 + (\text{Order Month} - \text{Start Month})$$
  这样可以完全规避由于每月天数不同（28/30/31天）带来的边界精度问题。

解析 2：窗口函数的应用场景与语法

• 在 Step 4 中，计算“累计 GMV”使用了聚合窗口函数：
  SUM(monthly_gmv) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
• 面试加分点：面试官可能会问，ROWS BETWEEN 和 RANGE BETWEEN 的区别。
  • ROWS 是物理行，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从分区第一行累加到当前行。
  • 在 Spark 中，如果不显式指定 ROWS 或 RANGE，默认的 ORDER BY 会采用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。为了执行效率和安全性，推荐显式书写 ROWS 规避计算开销。

解析 3：针对大数据量下的优化方案（高频追问）

如果 user_orders 是一个日均百亿级的超大表，上述 SQL 会触发大 Shuffle，如何优化？

1. 两阶段聚合（Map端预聚合）：
   在 Step 3 中，我们先按 first_purchase_month, relative_month 进行了 GROUP BY。由于 Cohort 的组合极少（12个月 * 13个相对月 = 156 种组合），这会极大地减少流向下一个 Stage（Step 4）的数据量，从而避免了在窗口计算阶段发生 OOM。

2. 避免 Join 带来的数据倾斜：

   • 大宽表设计：在数仓建设中，用户的“首购时间”属于极其稳定的属性，通常会沉淀在 dim_user（用户维度表）中。
   • 广播连接（Broadcast Hash Join）：如果用户表较小，在与订单表 Join 时，可以使用 BROADCAST(user_first_purchase) 强制进行广播 Join，避免将海量的订单表进行 Shuffle 重新分区（Shuffle Hash Join）。

3. 增量计算代替全量计算：
   在实际生产中，不需要每天都重新计算历史所有月份的 LTV。可以用日增量表更新昨天的 LTV 状态，或者仅重刷最近 12 个月的数据，通过分区裁剪（Partition Pruning）限制 order_date 的范围。