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

1. 题目背景

在智能快递柜业务中，为了提高快递柜的周转率，系统通常会设置超期收费规则。本题要求根据快递柜的取件流水数据，按照特定的收费规则，计算每个柜体（cabinet_id）所产生的超期费总额。

2. 收费规则

• 双重免费/收费区间：
  • 存放时间 $\le 12$ 小时：免费（0元）
  • $12$ 小时 $<$ 存放时间 $\le 24$ 小时：收费 1 元
  • 存放时间 $> 24$ 小时：在基础收费 1 元的基础上，每超过 12 小时加收 1 元（不足 12 小时按 12 小时计，即向上取整）。
  • 封顶上限： 每个包裹的超期费最高不超过 5 元。

3. 示例数据

表名：t_pickup_flow（取件流水表）

4. 期望输出结果

5. SparkSQL 参考答案

WITH temp_duration AS (
    SELECT 
        cabinet_id,
        package_id,
        -- 计算存放时间（单位：小时），转换为精确的浮点数
        (unix_timestamp(get_time) - unix_timestamp(put_time)) / 3600.0 AS duration_hours
    FROM t_pickup_flow
),
temp_fee 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 fee
    FROM temp_duration
)
SELECT 
    cabinet_id,
    SUM(fee) AS total_fee
FROM temp_fee
GROUP BY cabinet_id;

6. 核心考点与 SparkSQL 深度解析

本题是电商、物流行业数仓建设中非常典型的“阶梯式计费”场景，面试官主要考察以下几个 SparkSQL 的核心能力：

① 时间差值的精确计算 (unix_timestamp)

在 SparkSQL 中，直接使用 datediff 只能计算天数差。为了计算精确的小时数，必须将时间转换为时间戳（秒），相减后再除以 3600.0。

• 注意点： 必须除以 3600.0（浮点数）而不是 3600（整数），否则在 Spark 中会发生隐式整除，导致丢失分钟和秒的精度。

② 阶梯收费与向上取整算法 (CEIL)

对于超过 24 小时的部分，“每 12 小时加收 1 元，不足 12 小时按 12 小时计”，这是典型的向上取整场景。

• 数学公式： $\text{加收费用} = \lceil \frac{\text{总时间} - 24}{12} \rceil$
• 在 SQL 中使用 CEIL((duration_hours - 24) / 12.0) 完美契合该业务逻辑。例如：存放 24.1 小时，超期 0.1 小时，CEIL(0.1/12) 结果为 1，加收 1 元，总费用 2 元。

③ 极值限制函数的应用 (LEAST)

规则中存在“上限 5 元”的限制。

• 传统写法：使用复杂的 CASE WHEN (1 + CEIL(...)) > 5 THEN 5 ELSE ...。
• 高级/优雅写法： 使用 LEAST(val1, val2) 函数。它会返回参数列表中的最小值。LEAST(计算费用, 5.0) 能够极其简洁地实现“封顶 5 元”的逻辑，展现出候选人熟练的内置函数运用能力。

④ 数据流转解析（以 CAB002 为例）

• PKG9004： 存放 50 小时。
  • 前 24 小时收费 1 元。
  • 超出 $50 - 24 = 26$ 小时。
  • 超期费：$1 + \text{CEIL}(26 / 12) = 1 + 3 = 4$ 元。未超上限，计 4 元。
• PKG9005： 存放 96 小时。
  • 超期费：$1 + \text{CEIL}((96-24)/12) = 1 + 6 = 7$ 元。
  • 触发上限：LEAST(7.0, 5.0)，计 5 元。
• CAB002 总计： $4 + 5 = 8$ 元。