播面

面试真题：电商各时段用户下单及 GMV 占比统计

1. 题目背景

在电商数据分析中，了解用户在一天中不同时间段的活跃度和消费习惯对于运营决策（如精准营销、推送时间选择）至关重要。

请根据给定的订单明细表，将一天 24 小时划分为四个时段：

• 深夜（0:00:00 - 5:59:59，即 [0, 6) 小时）
• 上午（6:00:00 - 11:59:59，即 [6, 12) 小时）
• 下午（12:00:00 - 17:59:59，即 [12, 18) 小时）
• 晚上（18:00:00 - 23:59:59，即 [18, 24) 小时）

统计各时段的下单用户数（需去重）、下单订单数以及 GMV 占全天总 GMV 的比例（结果保留两位小数，带 % 符号或保留小数均可，本题以百分数呈现）。

2. 示例数据

订单明细表：t_order_detail

3. 期望输出

4. 解题思路

本题是典型的“分箱（Binning）+ 分组聚合 + 窗口函数”综合应用题，解决该问题可以分为以下四个步骤：

1. 时段切分 (Case When)：
   使用 HOUR(order_time) 函数提取订单时间的小时数。利用 CASE WHEN 语句将 0-23 的小时数映射到对应的四个时段（深夜、上午、下午、晚上）。
2. 基础指标聚合：
   按照划分好的时段进行 GROUP BY。
   • 下单用户数：使用 COUNT(DISTINCT user_id) 进行去重统计。
   • 下单订单数：使用 COUNT(order_id) 统计。
   • 当前时段 GMV：使用 SUM(gmv)。
3. 占比计算 (窗口函数)：
   计算 GMV 占比的核心在于获取“全天总 GMV”。
   无需再次 JOIN 原表，直接使用窗口函数 SUM(SUM(gmv)) OVER () 即可获取全局总 GMV，再将当前时段的 GMV 除以全局 GMV。
4. 格式化与排序：
   • 使用 CONCAT 和 ROUND 将占比转换为百分数格式。
   • 通过 ORDER BY CASE 保证输出时段按照“深夜 -> 上午 -> 下午 -> 晚上”的自然时间顺序排列。

5. SQL 代码实现 (标准 SQL / MySQL 8.0+)

WITH period_mapping AS (
    -- 1. 提取小时并划分时间段
    SELECT 
        user_id,
        order_id,
        gmv,
        CASE 
            WHEN HOUR(order_time) >= 0 AND HOUR(order_time) < 6 THEN '深夜'
            WHEN HOUR(order_time) >= 6 AND HOUR(order_time) < 12 THEN '上午'
            WHEN HOUR(order_time) >= 12 AND HOUR(order_time) < 18 THEN '下午'
            WHEN HOUR(order_time) >= 18 AND HOUR(order_time) < 24 THEN '晚上'
        END AS time_period
    FROM t_order_detail
)
-- 2. 聚合统计各指标及占比
SELECT 
    time_period,
    COUNT(DISTINCT user_id) AS user_count,
    COUNT(order_id) AS order_count,
    SUM(gmv) AS period_gmv,
    -- 使用窗口函数计算总GMV并求占比，最后格式化为百分比
    CONCAT(ROUND(SUM(gmv) / SUM(SUM(gmv)) OVER () * 100, 2), '%') AS gmv_ratio
FROM period_mapping
GROUP BY time_period
-- 3. 自定义排序，确保输出符合时间先后顺序
ORDER BY 
    CASE time_period
        WHEN '深夜' THEN 1
        WHEN '上午' THEN 2
        WHEN '下午' THEN 3
        WHEN '晚上' THEN 4
    END;

6. 核心考点解析（面试官加分项）

• HOUR() 函数的运用：考查候选人对日期时间函数的熟悉程度。在 Hive SQL 中，通常使用 hour(order_time)，而在 PostgreSQL 中则可能使用 EXTRACT(HOUR FROM order_time)。
• 窗口函数嵌套聚合：SUM(SUM(gmv)) OVER () 是此题的核心难点。
  • 内层的 SUM(gmv) 是当前分组（即某个时段）的 GMV 和。
  • 外层的 SUM(...) OVER () 是将所有分组后的时段 GMV 再次求和，得到全天总 GMV。
  • 这种写法避免了写子查询去单独算总和再 CROSS JOIN 回来的繁琐，代码极其优雅且性能更好。
• COUNT(DISTINCT) 意识：同一用户可能在同一时段内多次下单（例如示例数据中 U01 在上午/深夜多次下单）。统计用户数时必须使用 DISTINCT 去重，这是评估数据分析师业务严谨性的重要指标。
• 定制化排序 (Custom Sort)：默认的 ORDER BY time_period 会按照拼音或拼音首字母排序（"下午" -> "深夜" -> "上午" -> "晚上"），这不符合业务直觉。使用 ORDER BY CASE... 进行自定义排序是高级 SQL 编写的加分细节。