面试真题：双十一分时段 GMV 占比统计

1. 题目背景

在电商年度大促（如双十一）期间，运营团队需要实时或离线监控不同时段的销售爆发情况。本题要求统计双十一当天，每 2 小时为一个时间段（例如 00:00:00-01:59:59 计为 0-2，以此类推），每个时间段的 GMV（成交总额）占当天全天总 GMV 的比例。

2. 样例数据

输入表：user_orders（用户订单表）

期望输出结果

SparkSQL 解决方案

WITH t_base AS (
    SELECT 
        order_amount,
        -- 计算每笔订单属于哪个2小时区间
        -- hour() 获取小时(0-23)，floor(hour/2)*2 得到区间起点，再拼接终点
        CONCAT(FLOOR(HOUR(pay_time) / 2) * 2, '-', FLOOR(HOUR(pay_time) / 2) * 2 + 2) AS time_interval
    FROM user_orders
    -- 严格限制在双十一当天
    WHERE pay_time >= '2023-11-11 00:00:00' AND pay_time < '2023-11-12 00:00:00'
),
t_interval_gmv AS (
    SELECT 
        time_interval,
        SUM(order_amount) AS interval_gmv
    FROM t_base
    GROUP BY time_interval
)
SELECT 
    time_interval,
    ROUND(interval_gmv, 2) AS interval_gmv,
    ROUND(SUM(interval_gmv) OVER(), 2) AS total_gmv,
    CONCAT(ROUND((interval_gmv / SUM(interval_gmv) OVER()) * 100, 2), '%') AS gmv_percentage
FROM t_interval_gmv
ORDER BY CAST(SPLIT(time_interval, '-')[0] AS INT);

SparkSQL 深度分析与面试加分项

在面试中，仅仅写出上述 SQL 是不够的。面试官往往会针对你的 SQL 方案进行深挖。以下是针对本题的深度解析和调优技巧，帮助你在面试中脱颖而出：

1. 时间分段技巧（Mathematical Bucketing）

• 常见误区：初学者往往会写一堆 CASE WHEN HOUR(pay_time) BETWEEN 0 AND 1 THEN '0-2' ...。这种写法极其冗长且不易维护。
• 优雅解法：利用数学公式 FLOOR(HOUR(pay_time) / N) * N 进行分箱。
  • 若按 2 小时分段：FLOOR(HOUR(pay_time) / 2) * 2。
  • 若按 4 小时分段：FLOOR(HOUR(pay_time) / 4) * 4。
  • 该技巧展示了你具备良好的逻辑抽象能力。

2. 窗口函数避免 Self-Join 性能瓶颈

• 传统做法：先 GROUP BY 计算每个时段的 GMV，再用一条 SELECT SUM(order_amount) FROM table 的子查询进行 JOIN 来计算占比。
• 优化做法（如上所示）：使用窗口函数 SUM(interval_gmv) OVER()。
  • 原理：OVER() 括号留空表示将整个结果集作为一个窗口（无分组无排序），Spark 会在内存中直接对聚合后的轻量级结果集求和。
  • 性能优势：避免了将大表进行两次扫描和不必要的关联（Join）操作，极大减少了 Shuffle，提升了 Spark 执行计划的效率。

3. 执行计划与 Shuffle 调优（高级加分项）

当被问及“如果数据量极大（百亿级），这个任务在 Spark 中如何调优”时，可以从以下几个维度回答：

• 过滤下推（Predicate Pushdown）：
  在 t_base 中，我们使用了 pay_time >= '2023-11-11 00:00:00'。如果 user_orders 表是按天分区的（例如分区字段为 dt），应优先使用 dt = '2023-11-11' 过滤。这样 Spark 可以直接进行分区裁剪，避免全表扫描。
• 数据倾斜（Data Skew）处理：
  • 问题：双十一当天，0点到2点（0-2）往往是下单高峰期，数据量远超其他时段，这会导致 GROUP BY time_interval 时发生严重的数据倾斜（某个 Reducer 处理的数据量极大，导致 Stage 拖尾）。
  • 解决方案（两阶段聚合）：
    如果出现倾斜，可以引入随机双重聚合。先给 key 加随机前缀进行局部聚合，再去掉前缀进行全局聚合：
    sql

    -- 1. 局部聚合
    SELECT time_interval, SUM(order_amount) 
    FROM (SELECT time_interval, order_amount, CONCAT(time_interval, '_', FLOOR(RAND() * 10)) as stage1_key FROM t_base)
    GROUP BY stage1_key, time_interval

• 窗口函数的执行位置：
  在最后的 SELECT 中，SUM(interval_gmv) OVER() 是在 t_interval_gmv（仅有 12 行数据，因为 24 小时分为 12 个段）的基础上运行的。由于数据集极小，此时的窗口操作完全在单节点的单个 Partition 内瞬间完成，没有任何性能压力。