SparkSQL 面试题：快件 24 小时滞留率统计

1. 题目背景与业务定义

在物流末端配送（如丰巢、菜鸟驿站）业务中，滞留件（投递超过 24 小时未取或一直未取）直接影响快递柜/驿站的周转效率。

• 投递时间（drop_off_time）：快递员将快件放入柜中的时间。
• 取件时间（pickup_time）：用户取走快件的时间（若为 NULL，表示目前仍滞留在柜中）。
• 滞留件定义：
  1. 已取件，但取件时间与投递时间间隔 > 24 小时。
  2. 至今未取件（即取件时间为 NULL），且当前时间（或业务统计截止时间）与投递时间间隔 > 24 小时。
• 统计目标：统计每日投递的快件中，滞留件占当天总投递量的比例（滞留率 = 当天投递且滞留的件数 / 当天总投递量）。

2. 示例数据

表1：快件投递取件表 (express_delivery)

假定当前系统统计截止时间（即运行报表的时间）为 2023-11-03 12:00:00。

3. SparkSQL 核心代码实现

WITH base_data AS (
    SELECT 
        express_id,
        -- 转换为日期作为分组维度
        DATE(drop_off_time) AS drop_off_date,
        drop_off_time,
        pickup_time,
        -- 使用 UNIX 时间戳计算秒数差，处理未取件情况（使用当前时间/业务指定截止时间 '2023-11-03 12:00:00' 替代 CURRENT_TIMESTAMP()）
        CAST(
            (UNIX_TIMESTAMP(COALESCE(pickup_time, CAST('2023-11-03 12:00:00' AS TIMESTAMP))) 
             - UNIX_TIMESTAMP(drop_off_time)) / 3600 
            AS DOUBLE
        ) AS duration_hours
    FROM express_delivery
)
SELECT 
    drop_off_date,
    COUNT(express_id) AS total_dropped,
    SUM(CASE WHEN duration_hours > 24 THEN 1 ELSE 0 END) AS total_delayed,
    -- 转换为百分比并保留两位小数
    ROUND(
        SUM(CASE WHEN duration_hours > 24 THEN 1 ELSE 0 END) * 100.0 / COUNT(express_id), 
        2
    ) AS delay_rate_percent
FROM base_data
GROUP BY drop_off_date
ORDER BY drop_off_date;

预期输出结果：

4. SparkSQL 面试考点深度剖析

在面试中，这道题目看似简单，但面试官往往通过此题考察候选人在实际生产环境中处理边界问题、时间计算和性能优化的能力。以下是需要掌握的硬核考点：

考点一：未取件（NULL 值）的边界处理

• 易错点：直接使用 pickup_time - drop_off_time。若快件未被取走，pickup_time 为 NULL，直接相减会导致结果为 NULL，从而漏掉“目前仍滞留在柜中”的快件。
• 解决方案：使用 COALESCE(pickup_time, CURRENT_TIMESTAMP())。对于未取走的件，采用“当前系统时间”（或业务基准时间）作为虚拟取件时间参与计算。

考点二：精确的时间差计算

• Hive/SparkSQL 差异：DATEDIFF(end, start) 只能计算天数差（按天截断），无法精确到小时。
• 解决方案：
  • 方法 A（标准高效）：将时间转换为 UNIX_TIMESTAMP（秒数），相减后除以 3600 得到小时数。
  • 方法 B（Spark 3.x 特性）：使用时间间隔类型 (CAST(pickup_time AS LONG) - CAST(drop_off_time AS LONG)) / 3600。

考点三：指标聚合与精度控制

• 条件计数：使用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 或 COUNT(IF(...)) 来统计符合特定条件的行数。
• 避免整数除法截断：在计算比例时，必须乘以 100.0 转换为 Double 类型，否则在某些 SQL 引擎中 Int / Int 会直接截断为 0。

考点四：Spark 性能调优扩展（高阶回答加分项）

若在面试中被追问：“如果这张表每天有数亿条数据，该如何优化这个 Spark 作业？”可以从以下几个维度作答：

1. 数据倾斜与预分区：
   • 投递时间 drop_off_date 作为分区字段（Partition Column）。在读取数据时，利用分区剪裁（Partition Pruning）只读取特定日期范围内的数据，避免全表扫描。
2. 避免双重 Scan：
   • 在使用 WITH 语法（CTE）时，如果该临时表被多次引用，Spark 默认会重复计算。可以使用 .cache() 或在 SQL 中显式进行 CACHE TABLE 提升吞吐。
3. 数据倾斜预防：
   • 如果某些极少数的大型驿站/快递柜（由 station_id 标识）件量极大导致数据倾斜，在 Group By 日期前，可以加入随机双阶段聚合（加盐），或者调整 spark.sql.shuffle.partitions 参数。