滴滴大数据面试SQL-高峰期供需缺口分析

一、题目

已知有两张表：

• t1_order_record：订单表，记录每条订单的创建时间、所在城市
• t1_driver_online：司机在线表，记录每位司机当天在线的时间段（start_time, end_time）和所在城市

请分析早高峰（07:00-09:00）和晚高峰（17:00-19:00）时段内，每个城市的订单需求量（订单数）与可用司机数（在该时段在线的司机数），并计算供需缺口 = 订单数 - 可用司机数，按缺口从大到小排列。

样例数据

t1_order_record：

+-----------+----------+----------------------+
| order_id  |  city    |     order_time       |
+-----------+----------+----------------------+
| 1         | 北京     | 2024-06-01 07:15:00  |
| 2         | 北京     | 2024-06-01 07:45:00  |
| 3         | 北京     | 2024-06-01 17:30:00  |
| 4         | 上海     | 2024-06-01 07:20:00  |
| 5         | 上海     | 2024-06-01 07:50:00  |
| 6         | 上海     | 2024-06-01 18:00:00  |
| 7         | 上海     | 2024-06-01 18:15:00  |
| 8         | 北京     | 2024-06-01 08:30:00  |
+-----------+----------+----------------------+

t1_driver_online：

+-----------+----------+----------------------+----------------------+
| driver_id |  city    |     start_time       |      end_time        |
+-----------+----------+----------------------+----------------------+
| d001      | 北京     | 2024-06-01 06:00:00  | 2024-06-01 12:00:00  |
| d002      | 北京     | 2024-06-01 08:00:00  | 2024-06-01 20:00:00  |
| d003      | 北京     | 2024-06-01 16:00:00  | 2024-06-01 22:00:00  |
| d004      | 上海     | 2024-06-01 07:00:00  | 2024-06-01 15:00:00  |
| d005      | 上海     | 2024-06-01 16:00:00  | 2024-06-01 20:00:00  |
| d006      | 上海     | 2024-06-01 17:00:00  | 2024-06-01 23:00:00  |
+-----------+----------+----------------------+----------------------+

三、思路分析

本题是滴滴业务中非常典型的供需分析场景。核心难点在于：

• 订单侧：需要从order_time提取小时信息，判断该订单属于哪个高峰时段（早高峰/晚高峰），然后按城市+时段分组计订单数。

• 司机侧：需要判断司机的在线时间段是否与高峰时段有重叠。判断逻辑为：司机在线时段 [start_time, end_time] 与 [peak_start, peak_end] 有交集，即 start_time < peak_end AND end_time > peak_start。一个司机如果在线时段覆盖了整个7:00-9:00，则同时计入早高峰。

• 最终结果：分别统计订单数和司机数，然后JOIN计算缺口。需要注意如果一个城市在某时段没有订单或没有司机，应妥善处理NULL值。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

1.统计各城市各高峰时段的订单数

先将订单按城市分组，判断订单时间属于哪个高峰时段（7-9点为早高峰，17-19点为晚高峰），使用CASE WHEN进行时段划分。

执行SQL

select
    city,
    case
        when hour(order_time) between 7 and 8 then '早高峰(07:00-09:00)'
        when hour(order_time) between 17 and 18 then '晚高峰(17:00-19:00)'
        else '非高峰期'
    end as peak_period,
    count(*) as order_cnt
from t1_order_record
where hour(order_time) between 7 and 8
   or hour(order_time) between 17 and 18
group by city, peak_period

执行结果

+---------+------------------------+------------+
|  city   |      peak_period       | order_cnt  |
+---------+------------------------+------------+
| 北京    | 早高峰(07:00-09:00)    | 2          |
| 北京    | 晚高峰(17:00-19:00)    | 1          |
| 上海    | 早高峰(07:00-09:00)    | 2          |
| 上海    | 晚高峰(17:00-19:00)    | 2          |
+---------+------------------------+------------+

2.统计各城市各高峰时段的可用司机数

通过判断司机的在线时间区间与高峰时段是否重叠来统计可用司机数。一个司机的在线时段覆盖了某高峰时段即计入。注意高峰期的时间边界：7:00-9:00 和 17:00-19:00。

执行SQL

select
    city,
    sum(case
        when start_time < '2024-06-01 09:00:00' and end_time > '2024-06-01 07:00:00'
        then 1 else 0
    end) as morning_drivers,
    sum(case
        when start_time < '2024-06-01 19:00:00' and end_time > '2024-06-01 17:00:00'
        then 1 else 0
    end) as evening_drivers
from t1_driver_online
group by city

执行结果

+---------+------------------+------------------+
|  city   | morning_drivers  | evening_drivers  |
+---------+------------------+------------------+
| 北京    | 2                | 2                |
| 上海    | 1                | 2                |
+---------+------------------+------------------+

3.关联订单数与司机数计算供需缺口

将两个结果通过city和时段进行关联，计算供需缺口 = 订单数 - 可用司机数。使用CTE结构将前面两步整合。

执行SQL

with order_stats as (
    select
        city,
        case
            when hour(order_time) between 7 and 8 then '早高峰'
            when hour(order_time) between 17 and 18 then '晚高峰'
        end as peak_period,
        count(*) as order_cnt
    from t1_order_record
    where hour(order_time) between 7 and 8
       or hour(order_time) between 17 and 18
    group by city, peak_period
),
driver_stats as (
    select
        city,
        '早高峰' as peak_period,
        sum(case
            when start_time < '2024-06-01 09:00:00' and end_time > '2024-06-01 07:00:00'
            then 1 else 0
        end) as driver_cnt
    from t1_driver_online
    group by city
    union all
    select
        city,
        '晚高峰' as peak_period,
        sum(case
            when start_time < '2024-06-01 19:00:00' and end_time > '2024-06-01 17:00:00'
            then 1 else 0
        end) as driver_cnt
    from t1_driver_online
    group by city
)
select
    o.city,
    o.peak_period,
    o.order_cnt,
    coalesce(d.driver_cnt, 0) as driver_cnt,
    o.order_cnt - coalesce(d.driver_cnt, 0) as gap
from order_stats o
left join driver_stats d
    on o.city = d.city and o.peak_period = d.peak_period
order by gap desc

执行结果

+---------+------------+------------+-------------+------+
|  city   | peak_period| order_cnt  | driver_cnt  | gap  |
+---------+------------+------------+-------------+------+
| 上海    | 早高峰     | 2          | 1           | 1    |
| 北京    | 早高峰     | 2          | 2           | 0    |
| 上海    | 晚高峰     | 2          | 2           | 0    |
| 北京    | 晚高峰     | 1          | 2           | -1   |
+---------+------------+------------+-------------+------+

结果表明上海早高峰供需缺口最大（缺口=1，司机不足）；北京晚高峰则为负缺口（司机过剩）。

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
NULL值处理	NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE IF NOT EXISTS t1_order_record (
    order_id BIGINT COMMENT '订单ID',
    city STRING COMMENT '城市',
    order_time STRING COMMENT '订单创建时间'
);

CREATE TABLE IF NOT EXISTS t1_driver_online (
    driver_id STRING COMMENT '司机ID',
    city STRING COMMENT '城市',
    start_time STRING COMMENT '在线开始时间',
    end_time STRING COMMENT '在线结束时间'
);

--数据插入
INSERT INTO t1_order_record(order_id, city, order_time) VALUES
(1, '北京', '2024-06-01 07:15:00'),
(2, '北京', '2024-06-01 07:45:00'),
(3, '北京', '2024-06-01 17:30:00'),
(4, '上海', '2024-06-01 07:20:00'),
(5, '上海', '2024-06-01 07:50:00'),
(6, '上海', '2024-06-01 18:00:00'),
(7, '上海', '2024-06-01 18:15:00'),
(8, '北京', '2024-06-01 08:30:00');

INSERT INTO t1_driver_online(driver_id, city, start_time, end_time) VALUES
('d001', '北京', '2024-06-01 06:00:00', '2024-06-01 12:00:00'),
('d002', '北京', '2024-06-01 08:00:00', '2024-06-01 20:00:00'),
('d003', '北京', '2024-06-01 16:00:00', '2024-06-01 22:00:00'),
('d004', '上海', '2024-06-01 07:00:00', '2024-06-01 15:00:00'),
('d005', '上海', '2024-06-01 16:00:00', '2024-06-01 20:00:00'),
('d006', '上海', '2024-06-01 17:00:00', '2024-06-01 23:00:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题