基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

将一天 24 小时划分为四个时段：深夜（0-6）、上午（6-12）、下午（12-18）、晚上（18-24）。统计各时段的下单用户数、下单订单数以及 GMV 的占比

面试真题：电商各时段用户下单及 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

order_id user_id order_time gmv
1001 U01 2023-10-25 02:15:00 150.00
1002 U02 2023-10-25 08:30:00 200.00
1003 U01 2023-10-25 10:45:00 50.00
1004 U03 2023-10-25 14:20:00 300.00
1005 U04 2023-10-25 19:10:00 100.00
1006 U02 2023-10-25 21:05:00 400.00
1007 U05 2023-10-25 23:45:00 50.00
1008 U03 2023-10-25 15:30:00 150.00
1009 U01 2023-10-25 05:00:00 100.00

3. 期望输出

time_period user_count order_count period_gmv gmv_ratio
深夜 1 2 250.00 16.67%
上午 2 2 250.00 16.67%
下午 1 2 450.00 30.00%
晚上 3 3 550.00 36.67%

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+)

sql 
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 编写的加分细节。
00:00
00:00