面试真题

题目描述：
在电商平台中，用户的流失和挽回是核心运营指标之一。请编写 SparkSQL 语句，统计发生过退款/退货行为的用户中，在退款完成后的 15 天内（含第 15 天）再次在平台下单且该笔新订单未发生退款的用户比例（结果保留 4 位小数）。

示例数据

1. 订单表：orders

记录用户在平台的所有下单信息。

2. 退款表：refunds

记录订单的退款/退货完成信息。

期望输出

样例说明：

• 总退款人数（分母）：user_A, user_B, user_C, user_D 共 4 人（user_E 无退款历史，排除）。
• 符合条件人数（分子）：
  • user_A：10-01 退款，10-10 再次下单（间隔 9 天 $\le$ 15 天），且该订单 o002 未退款。（满足）
  • user_B：10-01 退款，10-20 再次下单（间隔 19 天 $>$ 15 天）。（不满足）
  • user_C：10-01 退款，10-05 再次下单（间隔 4 天 $\le$ 15 天），但该订单 o006 发生了退款。（不满足）
  • user_D：10-01 退款后无后续下单。（不满足）
• 最终比例：$1 / 4 = 0.2500$。

SparkSQL 实现

WITH refund_users AS (
    -- 1. 找出所有成功退款的用户及其退款完成时间（作为分母基准）
    SELECT DISTINCT 
        user_id, 
        refund_date
    FROM refunds
    WHERE status = 'Success'
),

non_refunded_orders AS (
    -- 2. 筛选出平台所有未发生退款的订单
    SELECT 
        o.user_id, 
        o.order_id, 
        o.order_date
    FROM orders o
    LEFT JOIN refunds r ON o.order_id = r.order_id
    WHERE r.order_id IS NULL
),

repurchased_candidates AS (
    -- 3. 关联退款用户与未退款订单，筛选出在退款后15天内再次下单且未退款的用户
    SELECT DISTINCT 
        ru.user_id
    FROM refund_users ru
    INNER JOIN non_refunded_orders nro 
        ON ru.user_id = nro.user_id
    WHERE nro.order_date > ru.refund_date 
      AND nro.order_date <= DATE_ADD(ru.refund_date, 15)
)

-- 4. 计算最终比例
SELECT 
    COUNT(DISTINCT ru.user_id) AS refund_user_count,
    COUNT(DISTINCT rc.user_id) AS repurchase_no_refund_count,
    ROUND(
        COUNT(DISTINCT rc.user_id) / COUNT(DISTINCT ru.user_id), 
        4
    ) AS ratio
FROM refund_users ru
LEFT JOIN repurchased_candidates rc 
    ON ru.user_id = rc.user_id;

SparkSQL 深度分析与面试应对指南

在实际的大厂面试中，这道题表面上考察的是基础的 JOIN 和 LEFT JOIN，但实际上蕴含了多个分布式计算和数仓建模的性能优化与边界场景考点。以下是针对该题目的深度剖析：

1. 业务逻辑陷阱：如何正确定义“未退款的订单”

• 误区：很多同学会直接用 FROM orders WHERE order_id NOT IN (SELECT order_id FROM refunds)。
• 面试官视角：在 SparkSQL 中，如果 NOT IN 子查询返回的结果中包含 NULL 值，整个查询会返回空结果。此外，NOT IN 通常不会被 Spark 优化器（Catalyst）很好地转换为高效的 Join 算子。
• 正确做法：使用 LEFT JOIN ... WHERE r.order_id IS NULL（如上面代码第2步所示）。Spark 会将其优化为 Left Anti Join，这是处理“排除型”逻辑性能最优的算子。

2. 时间跨度计算与 Spark 内置函数

• 对于时间跨度的计算，Spark 提供了 DATE_ADD(start_date, num_days) 和 DATEDIFF(end_date, start_date) 函数。
• 在写 SQL 时，注意边界条件。题目要求“15天内再次下单”，对应的区间是 (refund_date, refund_date + 15]。使用 > 而非 >=，因为再次下单必然发生在退款完成的动作之后。

3. 分布式性能优化（Spark 核心考点）

如果在面试中被追问：“如果这两张表是海量数据，你的 SQL 该如何优化？”，可以从以下三个维度作答：

• 广播连接 (Broadcast Hash Join)：
  如果 refunds 表（退款表）相较于 orders 表（订单表）要小得多（通常电商中退款率在 10%~20% 左右），可以在关联时强制使用广播：

  sql

  -- 在 Spark 中可以使用 hint 提示
  SELECT /*+ BROADCAST(ru) */ ...

  这样可以避免大表 orders 的 Shuffle，大幅提升执行效率。

• 避免多阶段的 COUNT(DISTINCT)：
  在最终的聚合计算中，使用了 COUNT(DISTINCT ru.user_id)。在超大数据量下，COUNT(DISTINCT) 会导致数据倾斜和单点瓶颈。

  • 优化方案：在 CTE 临时表（如 refund_users 和 repurchased_candidates）中，已经提前做过了 DISTINCT user_id，因此最后的聚合计算可以安全地降级为简单的 COUNT(user_id)，从而避免生成高代价的全局去重执行计划。

• 数据倾斜应对 (Data Skew)：
  如果某些大促期间或某些特定商家有大量的退款/下单记录，容易导致 JOIN 时某些 Task 内存溢出（OOM）。

  • 解决方案：
    1. 过滤不必要的异常数据（如 user_id IS NOT NULL）。
    2. 开启 Spark 3.x 的 AQE (Adaptive Query Execution) 自动处理倾斜连接：spark.sql.adaptive.skewJoin.enabled = true。