播面

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

1. 题目背景与要求

在智慧物流场景中，快递柜的运营需要分析用户在不同时间段的取件习惯。现需要统计在 22:00 至次日 06:00 之间完成取件的用户数，占该柜体总取件用户数的比例，并按城市维度分组。

注意：

• 夜间时间判定：跨天时间段（22:00:00 至 次日 05:59:59）。
• 用户去重：同一用户在同一柜体多次取件，计算用户数时需去重。
• 计算维度：最终结果按城市维度分组展示。

2. 基础数据准备

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

样例数据 (t_cabinet_info)：

表 2：取件记录表 (t_pickup_record)

样例数据 (t_pickup_record)：

3. 期望输出结果

(注：北京总去重用户有 A, B, C, D 共4人。其中在夜间取过件的有 A, B, D 共3人，比例为 3/4 = 0.75。上海总去重用户有 E, F, G 共3人，夜间取过件的仅有 G 共1人，比例为 1/3 = 0.3333。)

4. SparkSQL 核心解题答案

WITH pv_base AS (
    -- 1. 关联两表，并打上是否属于夜间取件的标签
    SELECT 
        c.city,
        r.user_id,
        CASE 
            WHEN HOUR(r.pickup_time) >= 22 OR HOUR(r.pickup_time) < 6 THEN 1 
            ELSE 0 
        END AS is_night_pickup
    FROM t_pickup_record r
    JOIN t_cabinet_info c ON r.cabinet_id = c.cabinet_id
),
user_night_flag AS (
    -- 2. 按城市和用户聚合，确定该用户在此城市是否属于“夜间取件用户”
    SELECT 
        city,
        user_id,
        MAX(is_night_pickup) AS has_night_pickup -- 只要有一次在夜间取件，即算作夜间取件用户
    FROM pv_base
    GROUP BY city, user_id
)
-- 3. 按城市维度计算比例
SELECT 
    city,
    ROUND(
        COUNT(CASE WHEN has_night_pickup = 1 THEN 1 END) / COUNT(user_id), 
        4
    ) AS night_pickup_user_ratio
FROM user_night_flag
GROUP BY city;

5. SparkSQL 深度分析与面试应对指南

在面试中，仅仅写出 SQL 只能达到及格线。面试官通常会针对这段 SQL 进行追问，以下是针对本题的深度技术分析，帮助你应对大厂面试：

5.1 核心考点解析

1. 跨天时间段判定 (Temporal Handling)：
   • 22:00 到次日 06:00 是典型的跨天区间。在 SQL 中不能使用 HOUR(t) BETWEEN 22 AND 6（这是空集）。
   • 解决方案：使用逻辑或 HOUR(t) >= 22 OR HOUR(t) < 6，或者使用 HOUR(t) NOT BETWEEN 6 AND 21。
2. 指标口径的去重控制 (Distinct Grain)：
   • 题目要求的是用户数占比，而不是取件次数占比。
   • 一个用户可能既在白天取件，也在夜间取件。只要他有一次在夜间取件，在分子计算时就必须打上“夜间用户”的标签。
   • 解决方案：利用 MAX(is_night_pickup) 进行上卷聚合。若某用户有多次记录，只要其中一次 is_night_pickup 为 1，则 MAX 结果即为 1。

5.2 Spark 优化与执行计划分析 (面试加分项)

在面对海量数据时，上述 SQL 在 Spark 引擎中运行时需要注意以下几点：

1. 数据倾斜 (Data Skew)：
   • 痛点：city 维度（如北京、上海）可能存在极大的数据倾斜，导致在 GROUP BY city, user_id 阶段，某些 Partition 数据量过大，产生 OOM。
   • 应对方案：
     • 如果是大表 join 小表（如 t_cabinet_info 只有几万行），必须确保触发 Broadcast Hash Join (BHJ)，避免 Shuffle。在 Spark 中可以通过 /*+ BROADCAST(c) */ 提示词强制广播小表。
2. 避免 COUNT(DISTINCT) 的 Shuffle 压力：
   • 有些候选人会写成：COUNT(DISTINCT CASE WHEN ... THEN user_id END) / COUNT(DISTINCT user_id)。
   • 弊端：在一条 SQL 中使用多个不同条件的 COUNT(DISTINCT) 会导致 Spark 在物理执行计划中生成多次临时聚合或引发复杂的 Expand 算子，造成极其严重的 Shuffle 读写。
   • 本设解法的优势：先在 user_night_flag 子查询中通过 GROUP BY city, user_id 完成了去重，外层直接使用普通的 COUNT 和 CASE WHEN，将 COUNT(DISTINCT) 转化为普通的 COUNT，这在 SparkSQL 性能调优中是非常经典的“两阶段聚合”策略。