面试真题

题目：
在智能快递柜业务中，格口（Slot）可能会因为物理损坏（如锁舌卡死、门板变形）而导致快递员无法投递、用户无法寄件，且这种情况往往没有系统报错上报。
请编写 SparkSQL 语句，找出在指定的分析日期（假设为 2023-10-27）前连续 7 天内（即 2023-10-21 至 2023-10-27 闭区间），没有任何一次投递（PUT）或寄件（SEND）记录的在用（启用状态）“僵尸格口”。

示例数据

1. 格口基础信息表：t_cabinet_slot

2. 格口操作日志表：t_slot_op_log

核心考点

1. 左外连接（LEFT JOIN）与防瘪（NOT Exists / IS NULL）的运用：如何找出在活跃主表存在，但在业务日志表中不存在的数据。
2. 时间函数的处理：SparkSQL 中日期减法 DATE_SUB、类型转换 CAST 以及区间过滤。
3. 复杂窗口函数（高级考点）：如何拓展求解“历史任意时间段内，是否存在连续 7 天以上无操作的格口”（使用 LEAD / LAG）。

解题方案

在实际面试中，面试官往往会根据你的回答层层递进。这里提供两种场景的解法：

场景一：排查指定/当前日期前 7 天内无记录的格口（最常用、最符合排障场景）

思路：

1. 筛选出格口表 t_cabinet_slot 中状态为启用的格口（status = 1）。
2. 在日志表 t_slot_op_log 中筛选出在指定时间窗口内（2023-10-21 至 2023-10-27）且操作类型为 PUT 或 SEND 的记录。
3. 将两表进行 LEFT JOIN，关联条件为柜体ID和格口ID。
4. 过滤出右表关联字段为 NULL 的记录，即为“僵尸格口”。

SELECT 
    s.cabinet_id,
    s.slot_id
FROM t_cabinet_slot s
LEFT JOIN (
    -- 筛选指定7天内有投递或寄件记录的格口
    SELECT DISTINCT cabinet_id, slot_id
    FROM t_slot_op_log
    WHERE op_type IN ('PUT', 'SEND')
      AND CAST(op_time AS DATE) BETWEEN DATE_SUB('2023-10-27', 6) AND '2023-10-27'
) l 
ON s.cabinet_id = l.cabinet_id AND s.slot_id = l.slot_id
WHERE s.status = 1                  -- 必须是在用格口
  AND l.slot_id IS NULL;            -- 7天内没有任何记录

期望输出结果：

• CAB001-S02：最后一次投递在 10-19，在 10-21 至 10-27 窗口内无记录。
• CAB001-S03：完全没有操作记录。
• CAB002-S02：最后一次投递在 10-20，在窗口内无记录。
• (CAB002-S03 因 status=0 被过滤)

场景二：升级版——排查历史任意时间段内，曾出现过“连续7天以上无记录”的格口

如果面试官问：“如果是要找出历史任意时期，格口放假超过7天未被使用的记录呢？”

思路：

1. 使用窗口函数 LEAD(op_time) OVER (PARTITION BY ... ORDER BY ...) 获取当前格口下一次的操作时间。
2. 计算相邻两次操作之间的时间差 DATEDIFF(next_op_time, op_time)。
3. 过滤出时间差大于等于 7 天的格口。

WITH sorted_logs AS (
    -- 1. 提取每个启用的格口的每次 PUT/SEND 操作时间
    SELECT 
        s.cabinet_id,
        s.slot_id,
        CAST(l.op_time AS DATE) AS op_date
    FROM t_cabinet_slot s
    JOIN t_slot_op_log l 
      ON s.cabinet_id = l.cabinet_id AND s.slot_id = l.slot_id
    WHERE s.status = 1 AND l.op_type IN ('PUT', 'SEND')
),
gap_calc AS (
    -- 2. 使用 LEAD 函数获取下一次操作日期，并计算间隔天数
    SELECT 
        cabinet_id,
        slot_id,
        op_date,
        LEAD(op_date) OVER (PARTITION BY cabinet_id, slot_id ORDER BY op_date) AS next_op_date
    FROM sorted_logs
)
-- 3. 找出历史间隔 >= 7天的格口
SELECT DISTINCT
    cabinet_id,
    slot_id
FROM gap_calc
WHERE DATEDIFF(next_op_date, op_date) >= 7;

SparkSQL 面试官心理分析与避坑指南

1. 为什么不用 NOT IN？

   • 避坑点：在面试中，尽量避免使用 WHERE ID NOT IN (SELECT ID FROM ...)。因为在 Spark 中，如果子查询的结果集包含 NULL 值，整个 NOT IN 子句会直接返回 NULL（导致查不出任何数据）。此外，LEFT JOIN ... WHERE ... IS NULL 会被 Spark 优化器优化为 LeftAnti Join（左半反连接），性能极高。

2. 数据倾斜（Data Skew）防范

   • 高分回答：在实际的快递业务中，某些“热点柜体”（如小区门口、高频写字楼）的数据量极大。如果在进行格口表和日志表 JOIN 时出现数据倾斜，可以采用以下策略：
     • MapJoin / Broadcast Join：格口基本信息表 t_cabinet_slot 通常比较小（几十万到几百万行），可以将其广播到 Executor 内存中（使用 /*+ BROADCAST(s) */），避免 Shuffle 引起的倾斜。

3. 物理故障排查的实际业务闭环

   • 加分表达：向面试官展现你的大数据业务 sense。

     “在产线环境中，找出‘僵尸格口’后，我们不能直接判定其损坏，通常还会结合历史开门成功率、格口温度/震动传感器数据或者召回派单系统。如果判定为疑似损坏，我们会通过 SparkSQL 任务每天 T+1 跑出名单，推送给线下运维人员的工单系统去现场开门核检，从而完成了从‘数据分析’到‘业务落地’的闭环。”