SparkSQL 面试题：网点用户取件等待时间分位数统计

1. 题目背景与要求

在物流与快递行业中，用户在网点的取件等待时间是衡量服务质量（SLA）的核心指标。为了评估整体服务水平并识别异常长等待的极端情况，需要按网点（Station）统计用户取件等待时间（小时数）的 50% 分位数（中位数）、90% 分位数和 95% 分位数。

2. 示例数据

表名：user_pickup_records（用户取件记录表）

面试题标准答案 (SparkSQL)

WITH duration_calc AS (
    SELECT 
        station,
        -- 计算等待时间（小时数），保留4位小数
        ROUND(
            (CAST(UNIX_TIMESTAMP(pickup_time) AS DOUBLE) - CAST(UNIX_TIMESTAMP(arrival_time) AS DOUBLE)) / 3600.0, 
            4
        ) AS wait_hours
    FROM 
        user_pickup_records
)
SELECT 
    station,
    -- percentile_approx 允许传入数组一次性计算多个分位数
    percentile_approx(wait_hours, 0.5) AS pct_50,
    percentile_approx(wait_hours, 0.9) AS pct_90,
    percentile_approx(wait_hours, 0.95) AS pct_95
FROM 
    duration_calc
GROUP BY 
    station;

预期输出结果

SparkSQL 深度分析与面试应对策略

在面试中解答此题，不仅要写出 SQL，还要能够向面试官阐述底层原理、性能瓶颈及优化方案。以下是针对该题目的深度剖析：

一、 核心函数选择：percentile vs percentile_approx

1. percentile 函数：

   • 原理：精确计算分位数。它需要将组内的所有数据收集到内存中并进行全局排序。
   • 缺点：如果某个网点（如大型转运中心）的数据量极大，全局排序会导致 OOM (Out Of Memory)，且无法在分布式集群中高效并行处理。
   • 限制：在 SparkSQL 中，percentile 的输入参数必须是整数/非浮点型。由于本题计算的是小时数（带小数），直接使用 percentile 会报错。

2. percentile_approx 函数 (推荐)：

   • 原理：近似计算分位数。它基于 T-Digest 算法，通过构建缓冲区对数据流进行聚类和压缩，只占用极小的内存（$O(\log N)$ 级别）即可在超大规模数据集上估算出极度精确的分位数。
   • 支持类型：支持 Double 等数值类型。
   • 第三参数 (精度控制)：语法为 percentile_approx(col, p, [B])。B 控制参数精度（默认值是 10,000）。B 越大，精度越高，但消耗内存稍多。面试中提到这个参数是加分项。

二、 计算逻辑剖析

1. 时间差转换：
   • SparkSQL 中不能直接对 Timestamp 进行减法，标准做法是使用 UNIX_TIMESTAMP(col) 将其转换为秒级时间戳（Long），相减后再除以 3600.0 转换为小时（Double）。
2. 多值聚合优化：
   • 避免写三次 percentile_approx(wait_hours, 0.5)、percentile_approx(wait_hours, 0.9) 导致的三次独立计算。SparkSQL 的 percentile_approx 支持传入数组参数：percentile_approx(wait_hours, array(0.5, 0.9, 0.95))，这在底层只会对数据做一次扫描（One-pass Scan），能显著提升性能。

三、 面试官可能追问的优化与边界问题

• 追问 1：如果数据存在倾斜（某个大网点数据量占 90%），如何优化？

  • 解答：分位数计算天然对数据倾斜敏感，因为必须按 station 进行 GROUP BY（引发 Shuffle）。
  • 优化方案：
    1. 两阶段聚合（针对近似值）：先对网点加随机前缀进行局部聚合，再去掉前缀进行全局聚合（此方案对分位数精度有损，需谨慎）。
    2. 过滤异常值：在 WHERE 条件中提前过滤掉 arrival_time 或 pickup_time 为空、或等待时间小于 0 的异常脏数据，减少参与 Shuffle 的数据量。

• 追问 2：如果必须要精准分位数，且不能 OOM，怎么做？

  • 解答：
    • 如果数据量可以接受，使用窗口函数 row_number() over (partition by station order by wait_hours) 标记序号 $RN$，结合 count(1) over (partition by station) 计算总数 $N$。
    • 通过过滤条件 $RN = \lceil N \times p \rceil$（或相邻两数取平均）来手动计算精准分位数。这种方式会将排序压力分散到各个 Executor 的磁盘和内存中，比直接 percentile 鲁棒性更强。