播面

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

题目描述：
在双十一当天（2023-11-11），请你编写一个 SQL 统计每个时间段（以每 2 小时为一个时间段，如 0-2, 2-4 ... 22-24）的 GMV（GMV 指支付金额）占当天全天 GMV 的百分比。

示例数据

订单表：user_orders

期望输出结果

(注：全天总 GMV 为 2500.00。未产生成交的时间段不显示，或通过左连接补 0，本题默认只输出有交易的时间段。)

SQL 标准答案

WITH daily_orders AS (
    -- 1. 过滤双十一当天数据，并计算每笔订单属于哪个2小时区间
    SELECT
        payment_amount,
        FLOOR(HOUR(pay_time) / 2) * 2 AS interval_start
    FROM user_orders
    WHERE DATE(pay_time) = '2023-11-11'
),
interval_gmv AS (
    -- 2. 按区间分组，统计各区间的 GMV 总额
    SELECT
        CONCAT(interval_start, '-', interval_start + 2) AS time_interval,
        SUM(payment_amount) AS gmv,
        interval_start
    FROM daily_orders
    GROUP BY interval_start
)
-- 3. 使用窗口函数计算全天总 GMV 并求得占比
SELECT
    time_interval,
    gmv,
    CONCAT(ROUND(gmv / SUM(gmv) OVER () * 100, 2), '%') AS gmv_percentage
FROM interval_gmv
ORDER BY interval_start;

核心考点与解题思路剖析（面试官视角的加分项）

这道题是电商数据分析（SQL 笔试）中非常经典的一道分时段趋势分析题，主要考察候选人的以下几个核心能力：

1. 时间维度的“分桶”处理（Bucket Grouping）

• 难点：如何把连续的 24小时 划分为 12个 2小时的区间（如 0-2, 2-4）。
• 解法：
  • 利用 HOUR(pay_time) 提取出小时（0-23）。
  • 通过向下取整的数学技巧：FLOOR(HOUR(pay_time) / 2) * 2。
    • 若小时为 0 或 1：FLOOR(0/2)*2 和 FLOOR(1/2)*2 结果都是 0。
    • 若小时为 2 或 3：结果都是 2。
  • 通过 CONCAT(interval_start, '-', interval_start + 2) 拼接出漂亮的时间段标签 0-2。
• 面试加分点：不建议使用冗长的 CASE WHEN HOUR(pay_time) BETWEEN 0 AND 1 THEN '0-2' ...，这种数学分桶方式更体现代码的优雅与高级感。

2. 分母的动态计算（窗口函数的妙用）

• 难点：在对时间段进行 GROUP BY 的同时，如何拿到全天不分组的总 GMV。
• 常规解法：写一个子查询 SELECT SUM(payment_amount) FROM user_orders ...，然后进行笛卡尔积关联，这样会多扫描一次表，性能较差。
• 最优解法：使用窗口函数 SUM(gmv) OVER ()。在已经 GROUP BY 之后的结果集上，不加 PARTITION BY 地使用 SUM() 窗口函数，能直接计算出所有行的 gmv 总和，作为占比的分母。

3. 细节处理与格式化

• 百分比转换：使用 ROUND(ratio * 100, 2) 保留两位小数，再用 CONCAT(..., '%') 转换为易读的百分比格式。
• 排序问题：时间段如果是字符串（如 "10-12" 比 "2-4" 的字符顺序更靠前），直接用 ORDER BY time_interval 会导致排序混乱。本答案在 CTE 中保留了数值型的 interval_start，并在最后用它进行 ORDER BY，确保了输出结果按时间流严格正序。