播面

SparkSQL 面试题：快递柜超期费计算

1. 题目背景与要求

某快递柜系统规定了如下的超期收费规则：

• 0 ~ 12 小时（含 12 小时）：免费。
• 12 ~ 24 小时（含 24 小时）：收费 1 元。
• 超过 24 小时：每增加 12 小时（不足 12 小时按 12 小时计）加收 1 元。
• 封顶费用：每个包裹的超期费上限为 5 元。

现有快递柜取件流水表（package_pickup_log），请编写 SparkSQL 语句，计算每个柜体（cabinet_id）产生的超期费总额。

2. 示例数据

快递柜取件流水表 (package_pickup_log)

3. 期望输出结果

4. SparkSQL 核心解法

WITH duration_calc AS (
    SELECT 
        cabinet_id,
        package_id,
        -- 计算入柜到取件的时间差（小时数），转为 double 确保精度
        (unix_timestamp(pickup_time) - unix_timestamp(put_time)) / 3600.0 AS duration_hours
    FROM 
        package_pickup_log
),
fee_calc AS (
    SELECT 
        cabinet_id,
        package_id,
        duration_hours,
        CASE 
            -- 1. 12小时以内免费
            WHEN duration_hours <= 12 THEN 0.0
            -- 2. 12到24小时收费1元
            WHEN duration_hours > 12 AND duration_hours <= 24 THEN 1.0
            -- 3. 超过24小时，每12小时加收1元，上限5元
            ELSE LEAST(1.0 + CEIL((duration_hours - 24) / 12.0), 5.0)
        END AS pkg_fee
    FROM 
        duration_calc
)
SELECT 
    cabinet_id,
    SUM(pkg_fee) AS total_fee
FROM 
    fee_calc
GROUP BY 
    cabinet_id;

5. SparkSQL 核心考点与解题分析

在本题中，面试官主要考察候选人处理复杂业务规则转化、时间窗口计算以及边界值处理的能力。以下是具体的深度解析：

5.1 时间差值的精准计算

• 痛点：直接使用 datediff 只能计算天数差，无法满足小时级别的计算。
• 解析：在 SparkSQL 中，最通用的高精度时间计算方式是将 timestamp 通过 unix_timestamp() 转换为秒级时间戳，相减后再除以 3600.0 得到带小数点的小时数（注意：必须除以 3600.0 浮点数，若除以 3600 会被隐式转换为整除，丢失分钟精度）。

5.2 阶梯式计费与向上取整（CEIL）

• 规则分析：
  • 24小时以内：直接通过 CASE WHEN 划分区间。
  • 超过24小时：每增加 12 小时加收 1 元，不足 12 小时按 12 小时计。这属于典型的向上取整场景。
• 数学公式转化：
  • 超期时间 = duration_hours - 24。
  • 超期部分占 12 小时的份数（向上取整）= CEIL((duration_hours - 24) / 12.0)。
  • 总费用 = 基础费用(24小时的1元) + 加收费用 = 1.0 + CEIL((duration_hours - 24) / 12.0)。

5.3 封顶值限制（LEAST 函数的巧妙应用）

• 传统写法：使用复杂的嵌套 CASE WHEN 或者是 IF(fee > 5.0, 5.0, fee)。
• 优雅实现：SparkSQL 提供了 LEAST(value1, value2, ...) 函数，它会返回参数列表中的最小值。通过 LEAST(calculated_fee, 5.0)，可以直接优雅地实现“最高封顶 5 元”的业务限制，提高代码的可读性和执行效率。

5.4 边界测试案例验证

• P001 (7小时)：$\le 12$ 小时，费用 = 0元。
• P002 (18小时)：$> 12$ 且 $\le 24$ 小时，费用 = 1元。
• P003 (26小时)：$> 24$ 小时，超期 2 小时，不足 12 小时算 1 次。费用 = $1 + \lceil(26-24)/12\rceil = 1 + 1 =$ 2元。
  • CAB001 累计 = 0 + 1 + 2 = 3元。
• P004 (50小时)：$> 24$ 小时，费用 = $1 + \lceil(50-24)/12\rceil = 1 + \lceil2.16\rceil = 1 + 3 =$ 4元。
• P005 (96小时)：$> 24$ 小时，计算费用 = $1 + \lceil(96-24)/12\rceil = 1 + 6 = 7$元，触发封顶，最终费用 = 5元。
  • CAB002 累计 = 4 + 5 = 9元（注：若无封顶则为 11元）。