播面

SparkSQL 面试题：快递柜夜间取件用户占比统计

1. 题目背景

在智慧物流场景中，快递柜的运营需要分析用户在不同时间段的取件行为。本题旨在考察候选人对 跨天时间段处理、窗口函数/聚合函数、条件计数 以及 多表关联 的掌握程度，是典型的实际业务分析场景。

2. 示例数据

表1：快递柜信息表 (tb_cabinets)

表2：取件记录表 (tb_pickup_records)

3. 期望输出结果

按城市和柜体维度分组，计算夜间（22:00 - 次日06:00）取件的去重用户数占该柜体总去重用户数的比例。

数据解析说明：

• CAB001：总共有 U101, U102, U103 三个用户。U101在22:15取件，U102在01:30取件，U103在05:45取件，三位用户均在夜间段取过件。夜间去重用户为3，总去重用户为3，占比 100%。
• CAB002：只有 U104 一个用户，其在 23:00（夜间）取过件。占比 100%。
• CAB003：有 U105（18:00 非夜间）和 U106（03:00 夜间）两个用户。夜间去重用户为1，总去重用户为2，占比 50%。

4. SparkSQL 核心解法

WITH prep_data AS (
    SELECT 
        c.city,
        r.cabinet_id,
        r.user_id,
        -- 判断是否在 22:00 至次日 06:00 之间
        CASE 
            WHEN DATE_FORMAT(r.pickup_time, 'HH:mm:ss') >= '22:00:00' 
              OR DATE_FORMAT(r.pickup_time, 'HH:mm:ss') <= '06:00:00' 
            THEN 1 
            ELSE 0 
        END AS is_night
    FROM tb_pickup_records r
    JOIN tb_cabinets c ON r.cabinet_id = c.cabinet_id
),
cabinet_user_metrics AS (
    SELECT 
        city,
        cabinet_id,
        -- 计算夜间取件的去重用户数
        COUNT(DISTINCT CASE WHEN is_night = 1 THEN user_id END) AS night_users,
        -- 计算该柜体的总去重用户数
        COUNT(DISTINCT user_id) AS total_users
    FROM prep_data
    GROUP BY city, cabinet_id
)
SELECT 
    city,
    cabinet_id,
    night_users,
    total_users,
    -- 转化为百分比格式，保留两位小数
    CONCAT(ROUND(COALESCE(night_users * 100.0 / NULLIF(total_users, 0), 0), 2), '%') AS night_ratio
FROM cabinet_user_metrics
ORDER BY city, cabinet_id;

5. SparkSQL 面试考点与深度分析

考点一：跨天时间段的逻辑表达 (Time Range Across Midnight)

• 面试官意图：考察候选人对时间戳处理的敏感度。22:00 到次日 06:00 是跨天的，不能简单地使用 BETWEEN '22:00:00' AND '06:00:00'（这在逻辑上是空集）。
• 解决方案：必须使用 OR 逻辑。即：小时 >= 22 或者 小时 <= 06。在 SparkSQL 中，使用 DATE_FORMAT(pickup_time, 'HH:mm:ss') 提取 24 小时制时间进行字符串或数值比较是最稳妥的做法。

考点二：精确去重与条件聚合 (Conditional Count Distinct)

• 面试官意图：同一用户可能在白天和夜间都取过件（例如 CAB002 的 U104）。如果直接算记录数会造成失真，题目要求的是用户数（去重）。
• 解决方案：结合 COUNT(DISTINCT ...) 与 CASE WHEN。
  • 夜间用户数：COUNT(DISTINCT CASE WHEN is_night = 1 THEN user_id END)。当不满足条件时，CASE WHEN 返回 NULL，而 COUNT(DISTINCT) 会自动忽略 NULL 值，从而精准计算出夜间取过件的去重用户数。

考点三：健壮性设计与防零除 (Zero Division Prevention)

• 面试官意图：考察候选人在生产环境写 SQL 的严谨性。如果某柜体总用户数为 0（虽然在 JOIN 情况下极少发生，但作为数仓研发必须考虑），直接除以 0 会导致 Spark 任务报错或返回 NaN/Null。
• 解决方案：使用 NULLIF(total_users, 0)。当 total_users 为 0 时，将其转换为 NULL，任何数除以 NULL 都会安全地返回 NULL，再配合 COALESCE(..., 0) 进行兜底，保证了代码的极致健壮性。

考点四：Spark 物理执行优化（延伸问答）

如果面试官追问：“如果这两张表数据量极大，Spark 层面该如何优化？”

1. MapJoin (Broadcast Hash Join)：tb_cabinets（柜体表）通常是维度表，数据量较小（一般几万到几十万级别）。可以显式使用 BROADCAST(c) 提示，将小表广播到各个 Executor，避免大表 tb_pickup_records 发生 Shuffle，从而极大提升 Join 性能。
2. 避免全局 Distinct 导致的数据倾斜：COUNT(DISTINCT user_id) 在 Spark 中会生成一个两阶段聚合的执行计划。如果某个热点柜体（如地铁口快递柜）有极多取件记录，会导致 Shuffle Read 倾斜。可以考虑先按 cabinet_id, user_id 进行 GROUP BY 去重，再进行 COUNT 聚合，化整为零。