面试题：快递柜 2025 年累计离线时长统计

1. 题目背景

在物联网和智能硬件运营中，分析设备的可用率（SLA）是一项非常核心的任务。快递柜通过定期上报心跳包来同步其当前状态。若快递柜因网络故障或断电等原因离线，系统会记录其状态变化。

请编写 SparkSQL 语句，根据给定的快递柜状态变更历史表，计算每个快递柜在 2025 年期间（2025-01-01 00:00:00 至 2025-12-31 23:59:59）累计处于 “offline”（离线/断网） 状态的累计小时数（结果保留 2 位小数）。

2. 基础数据准备

输入表：cabinet_status_log（快递柜状态变更日志表）

边界条件说明：

• CAB002 在 2025-12-31 22:00:00 变更为 offline 后，在 2025 年内未再上线。其离线状态持续到了 2025 年结束（即 2026-01-01 00:00:00），在计算 2025 年累计离线时长时，该时间段应截断至 2025 年底。

期望输出结果

分析：

• CAB001 离线时段：
  • 12:00:00 到 14:30:00 = 2.5 小时
  • 08:00:00 到 11:15:00 = 3.25 小时
  • 累计 = 5.75 小时
• CAB002 离线时段：
  • 10:00:00 到 16:00:00 = 6.0 小时
  • 2025-12-31 22:00:00 到 2026-01-01 00:00:00（年终截断） = 2.0 小时
  • 累计 = 8.0 小时

3. SparkSQL 解决方案

WITH lead_events AS (
    SELECT 
        cabinet_id,
        status,
        CAST(event_time AS TIMESTAMP) AS start_time,
        -- 使用 LEAD 函数获取下一次状态变更的时间，若无下一次变更，说明一直持续到 2025 年底
        COALESCE(
            LEAD(CAST(event_time AS TIMESTAMP)) OVER (PARTITION BY cabinet_id ORDER BY event_time),
            CAST('2026-01-01 00:00:00' AS TIMESTAMP)
        ) AS end_time
    FROM cabinet_status_log
    WHERE event_time >= '2025-01-01 00:00:00' 
      AND event_time < '2026-01-01 00:00:00'
),
offline_durations AS (
    SELECT 
        cabinet_id,
        -- 计算单次离线时长（秒），再转换为小时
        (unix_timestamp(end_time) - unix_timestamp(start_time)) / 3600.0 AS duration_hours
    FROM lead_events
    WHERE status = 'offline'
)
SELECT 
    cabinet_id,
    ROUND(SUM(duration_hours), 2) AS total_offline_hours
FROM offline_durations
GROUP BY cabinet_id
ORDER BY total_offline_hours DESC;

4. 面试题核心知识点与深度剖析

此题是大数据计算（如监控、SLA、轨迹分析）中非常经典的“状态持续时间计算”问题。面试官主要考察以下几个维度：

1) 窗口函数 LEAD / LAG 的应用

• 核心点：状态的持续时间是由“当前状态的开始时间”到“下一个状态的开始时间”决定的。
• 解析：通过 LEAD(event_time) OVER (PARTITION BY cabinet_id ORDER BY event_time) 可以无缝获取当前状态的结束时间。

2) 边界条件处理（极易丢分点）

在真实生产环境中，设备状态可能在跨年时保持不变。本题针对 2025 年进行统计，必须处理 “跨年截断” 问题。

• 年终截断：若某个柜子在 2025 年最后一次上报是 offline，则 LEAD 结果为 NULL。此时需使用 COALESCE(..., '2026-01-01 00:00:00') 将终点强制截断至 2025 年最后一秒的下一秒，确保不漏算年终的离线时长。
• 进阶思考（可向面试官主动提及）：若该柜子在 2025 年初之前就是 offline 状态（例如 2024-12-31 延续过来的离线），在实际业务中还需要向前追溯 2024 年底的最后一条状态。本题简化了该场景，仅计算起止均在 2025 年内的变动。

3) 时间差值的计算

• SparkSQL 中计算两个 Timestamp 差值的常见高效做法是转换为 Unix 时间戳：(unix_timestamp(end_time) - unix_timestamp(start_time)) 得到秒数差，再除以 3600.0 转换为小时。
• 注意不要使用 datediff，因为 datediff 只返回天数差，无法精确到时分秒。

4) Spark 性能优化考虑（高分加分项）

如果在面试中能主动提及以下优化点，会极大地增加通过概率：

• 数据倾斜（Data Skew）：如果某些“明星快递柜”频繁上报状态，会导致 PARTITION BY cabinet_id 时单个 Executor 负载过大。
  • 解决办法：可以对 cabinet_id 进行加盐（Salt）局部聚合，或者增加并行度 spark.sql.shuffle.partitions。
• 过滤下推（Predicate Pushdown）：在窗口计算前，先通过 WHERE event_time >= '2025-01-01 00:00:00' 过滤非 2025 年的数据，减少 Shuffle 的数据量。
• 避免使用全局排序：窗口函数中只使用了 PARTITION BY + ORDER BY，Spark 会在 Shuffle 后在每个分区内进行局部排序，这是高效的。避免在窗口外进行全局 ORDER BY 导致性能瓶颈。