播面

SparkSQL 面试题：特定异常包裹提取行为及支付历史分析

1. 题目背景与业务逻辑

在物流和快递行业中，包裹滞留（从入柜/到达驿站到用户取走的时间差）会严重占用仓储资源。通常，超期提取需要缴纳“超期费”。
本题旨在通过用户的包裹提取历史和费用支付记录，筛选出符合以下特征的高风险/白嫖型用户：

1. 频繁滞留：在过去 90 天内，至少有 3 次包裹滞留超过 5 天才取走。
2. 拒绝付费：该用户在历史上从来没有在线支付过超期费（可能通过线下投诉、强行取件或系统漏洞规避）。

2. 示例数据

表 1：包裹提取记录表 (t_package_pickup)

记录包裹的入库时间、取件时间等信息。

表 2：超期费支付记录表 (t_overdue_payment)

记录用户在线支付超期费的历史。

3. 考察预期输出结果

根据上述示例数据，只有 13800138000 符合条件：

• 过去90天内（假设当前判定基准线为 2023-12-15）：
  • 13800138000 滞留超5天的次数为 3 次（P001, P002, P003 均滞留 7 天），且在支付表无记录。
  • 13911112222 虽然也有 3 次滞留超 5 天（P004滞留6天, P005滞留8天, P006滞留9天），但其有支付记录（PAY001），故排除。
  • 13799999999 滞留超5天次数为 3 次，且无支付记录，符合条件。

4. SparkSQL 标准答案

WITH temp_stay_users AS (
    -- 步骤 1：筛选出过去 90 天内，包裹滞留超过 5 天且累计次数 >= 3 的用户
    SELECT 
        user_phone,
        COUNT(package_id) AS overdue_count
    FROM 
        t_package_pickup
    WHERE 
        -- 限制在过去 90 天内（以 current_date() 为基准，测试时可替换为固定日期）
        arrive_time >= DATE_SUB(CURRENT_DATE(), 90)
        -- 筛选滞留超过 5 天的包裹 (1天 = 24小时，也可以用 datediff)
        -- 此处使用 CAST 将时间差转为天数，或直接用 datediff
        AND DATEDIFF(pickup_time, arrive_time) > 5
    GROUP BY 
        user_phone
    HAVING 
        COUNT(package_id) >= 3
)
-- 步骤 2：排除有在线支付记录的用户
SELECT 
    t1.user_phone
FROM 
    temp_stay_users t1
LEFT JOIN 
    (SELECT DISTINCT user_phone FROM t_overdue_payment) t2
ON 
    t1.user_phone = t2.user_phone
WHERE 
    t2.user_phone IS NULL;

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

在面试中，仅仅写出上述 SQL 是不够的。面试官往往会针对你的 SQL 方案进行追问，以考察你的底座原理理解和性能调优能力。以下是针对该题目的深度技术剖析：

5.1 时间函数的选择与注意事项

• DATEDIFF 还是精确到秒的减法？
  • DATEDIFF(pickup_time, arrive_time) > 5 比较的是“天数差异”（忽略具体时分秒）。如果业务要求精确到120小时（5*24小时），则应该使用：
    unix_timestamp(pickup_time) - unix_timestamp(arrive_time) > 5 * 24 * 3600。
  • 面试加分点：主动向面试官确认“超过5天”是指自然日跨度（DATEDIFF），还是严格的 120 小时。

5.2 排除数据集的实现方案对比 (Anti-Join 选型)

排除“有支付记录的用户”，有三种常见写法。在 SparkSQL 中，它们的执行计划和性能表现有很大差异：

1. LEFT JOIN ... WHERE t2.user_phone IS NULL (本案采用)
   • 原理：Spark 会将其优化为 LeftOuterJoin，如果右表较小，会触发 BroadcastHashJoin。
2. NOT EXISTS
   sql

   WHERE NOT EXISTS (SELECT 1 FROM t_overdue_payment t2 WHERE t1.user_phone = t2.user_phone)

   • 原理：Spark 优化器（Catalyst）通常会将其转化为 LeftAnti Join。在 Spark 3.x 中，这是性能最好、最推荐的写法，因为它不需要在 Join 后做 Filter 过滤，直接在 Join 阶段就过滤掉匹配的数据。
3. NOT IN
   • 警告：尽量避免使用 NOT IN。如果 t_overdue_payment 中的 user_phone 包含 NULL 值，整个 NOT IN 查询将返回空结果（三值逻辑陷阱）。Spark 在处理 NOT IN 时，往往无法使用高效的 Hash Join，而退化为 BroadcastNestedLoopJoin，导致性能极差。

5.3 数据倾斜 (Data Skew) 处理

在实际业务中，t_package_pickup（包裹表）和 t_overdue_payment（支付表）都是海量数据表。

• 倾斜场景：某些“刷单手机号”或者“平台公共账号（如客服代收）”会有极其庞大的包裹量和支付量。在进行 GROUP BY user_phone 或 JOIN 时，这些 Key 会被分发到同一个 Executor，导致 OOM 或 长尾 Task。
• 解决方案：
  1. 两阶段聚合（针对 GROUP BY 倾斜）：先给 user_phone 加随机前缀（如 0~9_13800000000）进行局部聚合，再去掉前缀进行全局聚合。
  2. 广播连接 (Broadcast Join)：如果支付表 t_overdue_payment 过滤后数据量较小（如小于 10MB，由 spark.sql.autoBroadcastJoinThreshold 控制），Spark 会自动广播该表，避免 Shuffle。我们可以显式加上 Hint：/*+ BROADCAST(t2) */。