播面

面试真题：用户首次与第二次下单金额差值及平台平均差值统计

1. 模拟数据集

订单基础表：user_orders

2. 核心考点与解题思路

1. 确定订单顺序（行变列/序列定位）：需要识别出每个用户的“首次下单”和“第二次下单”。这需要使用窗口函数 ROW_NUMBER() 按照用户分组，时间升序排列。
2. 提取特定顺位的数据：通过条件聚合（CASE WHEN + MAX）或者行转列的方法，将每个用户的第一笔和第二笔订单金额提取到同一行。
3. 过滤不合规数据：仅有1次下单记录的用户（如 u03）无法计算差值，需要在计算差值时予以过滤（即第二笔订单金额为 NULL 的用户不参与后续计算）。
4. 计算全局指标：要求“统计全平台用户的平均差值”，在输出明细的同时计算全局平均值，最优雅的方式是使用 无 PARTITION BY 的窗口函数 AVG(diff) OVER()，避免再次写子查询进行 JOIN。

3. SparkSQL 最佳实践代码

WITH ranked_orders AS (
    -- Step 1: 为每个用户的订单按时间进行排序，并只保留前2次下单记录以减少数据Shuffle量
    SELECT
        user_id,
        pay_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time ASC) as rn
    FROM user_orders
),
user_pivoted AS (
    -- Step 2: 使用行转列（条件聚合），获取每个用户首次和第二次下单的金额
    SELECT
        user_id,
        MAX(CASE WHEN rn = 1 THEN pay_amount END) as first_pay_amount,
        MAX(CASE WHEN rn = 2 THEN pay_amount END) as second_pay_amount
    FROM ranked_orders
    WHERE rn <= 2
    GROUP BY user_id
),
user_diff_calc AS (
    -- Step 3: 计算差值，并过滤掉没有第二次下单的用户
    SELECT
        user_id,
        first_pay_amount,
        second_pay_amount,
        (first_pay_amount - second_pay_amount) as diff
    FROM user_pivoted
    WHERE second_pay_amount IS NOT NULL
)
-- Step 4: 输出每个用户的差值明细，并通过窗口函数计算全平台的平均差值
SELECT
    user_id,
    first_pay_amount,
    second_pay_amount,
    diff,
    ROUND(AVG(diff) OVER (), 2) as platform_avg_diff
FROM user_diff_calc;

4. 预期输出结果

(注：u03 因只有1笔订单被过滤；平台平均差值计算公式为：(20 + (-50) + 0) / 3 = -10.00)

5. SparkSQL 面试加分项与深度分析

在面试中，仅仅写出 SQL 是不够的。如果能主动向面试官解释以下优化点和底层原理，通过率会极大提升：

① 避免使用 Self-Join（自连接）

初学者常使用 JOIN 把首次下单表和第二次下单表关联起来：

-- 不推荐做法
SELECT ... FROM (SELECT ... WHERE rn=1) a JOIN (SELECT ... WHERE rn=2) b ON a.user_id = b.user_id

• 弊端：在 Spark 中，JOIN 会引入昂贵的 Shuffle (Shuffle Hash Join / Sort Merge Join)，导致网络传输大增，且容易因为数据倾斜（某些大用户有极多订单）导致 OOM。
• 优化方案：本解法使用 MAX(CASE WHEN rn = 1 ...) 条件聚合。这种方式只需要一次 GROUP BY 聚合，Spark 底层仅发生一次基于 user_id 的 Hash 聚合 Shuffle，性能远优于 JOIN。

② 提前过滤数据（Filter Pushdown 思想）

在第一层 CTE (ranked_orders) 往第二层 (user_pivoted) 转化时，增加了 WHERE rn <= 2 条件。

• 原理：如果一个用户有 1000 笔订单，我们其实只关心前 2 笔。提前过滤掉 rn > 2 的数据，可以极大地减少聚合阶段内存和磁盘 I/O 的压力。

③ 巧妙使用开窗聚合 AVG() OVER ()

在最终输出时，题目要求同时展示“每个用户”和“全平台平均”。

• 普通做法：写一个子查询 SELECT AVG(diff) FROM ...，然后再 CROSS JOIN 回原表。
• Spark 优化做法：使用 AVG(diff) OVER ()。在没有 PARTITION BY 的情况下，Spark 会将所有数据汇总到一个 Window 算子中计算出平均值，并广播/追加到每一行，代码极其优雅且执行效率高。

④ 数据倾斜预防（Data Skew）

如果面试官追问：“如果某些大 V 用户的订单量极大，导致 ROW_NUMBER() 阶段发生数据倾斜怎么处理？”

• 回答：
  1. 在 ROW_NUMBER 之前，如果业务允许，先通过 WHERE 过滤掉不必要的历史订单。
  2. 如果仅仅是为了计算前两次，可以在 Map 端先做一次局部 TopN（配合物理执行计划中的 Limit 压减），但由于 Spark SQL 窗口函数不支持直接在 Map 端局部聚合，最有效的方式是提高 spark.sql.shuffle.partitions 的并行度，或对倾斜的 user_id 加盐（Salting）打散后再聚合。