SQL 充电桩使用率：充电时长/总可用时长（蔚来面试题）

一、题目

现有一张充电桩使用记录表 t2_charge_log，记录了每个充电桩每次被使用的起止时间。请计算每个充电站在 2024-01-01 这天各时段（每4小时为一个时段：00:00-03:59, 04:00-07:59, ..., 20:00-23:59）的充电桩使用率。

使用率 = 该时段内充电桩总占用时长 / (时段小时数 × 充电桩数量 × 60 分钟)

注意：一次充电可能跨越多个时段（如 02:00 开始充到 05:00 结束，跨越了"00:00-03:59"和"04:00-07:59"两个时段），需要按实际重叠时长拆分到各时段。

充电记录表 t2_charge_log：

+----------+-------------+----------------------+----------------------+
| pile_id  | station_id  |      start_time      |       end_time       |
+----------+-------------+----------------------+----------------------+
| P01      | A           | 2024-01-01 01:00:00  | 2024-01-01 03:30:00  |
| P02      | A           | 2024-01-01 02:00:00  | 2024-01-01 05:00:00  |
| P03      | A           | 2024-01-01 08:00:00  | 2024-01-01 09:30:00  |
| P01      | A           | 2024-01-01 13:00:00  | 2024-01-01 14:00:00  |
| P02      | A           | 2024-01-01 15:00:00  | 2024-01-01 17:30:00  |
| P04      | B           | 2024-01-01 08:00:00  | 2024-01-01 10:00:00  |
| P05      | B           | 2024-01-01 09:00:00  | 2024-01-01 11:00:00  |
| P04      | B           | 2024-01-01 16:00:00  | 2024-01-01 18:00:00  |
+----------+-------------+----------------------+----------------------+

二、思路分析

本题是"时间区间拆分"问题，核心难点在于：一次充电记录可能跨越多个时段，不能简单按 start_time 归属到单个时段。正确做法是：

1. 构建时段维度：用 union all 生成6个4小时时段，每个时段有 slot_start_min / slot_end_min 两个分钟边界
2. 提取充电分钟边界：将充电起止时间转为当天分钟数 start_min / end_min
3. 交叉连接 + 计算重叠：充电记录 cross join 时段维度，用 least/greatest 计算每条记录在每个时段内的重叠分钟数
4. 聚合计算使用率：按站+时段聚合重叠分钟，除以分母（4小时 × 桩数 × 60）

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

三、逐步推导

1. 统计每个充电站的充电桩数量

执行SQL

select station_id,
       count(distinct pile_id) as pile_cnt
from t2_charge_log
group by station_id

执行结果

+-------------+-----------+
| station_id  | pile_cnt  |
+-------------+-----------+
| B           | 2         |
| A           | 3         |
+-------------+-----------+
2 rows selected (0.984 seconds)(https://www.dwsql.com)

2. 将充电记录按实际重叠时长拆分到各时段

先将充电起止时间转为当天分钟数，再 cross join 时段维度，用 least/greatest 计算重叠分钟，只保留重叠 > 0 的记录。

执行SQL

with charge_minutes as (
    -- 充电起止时间转为当天分钟数（0-1439）
    select station_id, pile_id,
           cast(substr(start_time, 12, 2) as int) * 60 + cast(substr(start_time, 15, 2) as int) as start_min,
           cast(substr(end_time, 12, 2) as int)   * 60 + cast(substr(end_time, 15, 2) as int) as end_min
    from t2_charge_log
),
time_slots as (
    -- 6个4小时时段（左闭右开，分钟边界）
    select  0 as slot_start_min,  240 as slot_end_min, '00:00-03:59' as time_slot
    union all select 240,  480, '04:00-07:59'
    union all select 480,  720, '08:00-11:59'
    union all select 720,  960, '12:00-15:59'
    union all select 960, 1200, '16:00-19:59'
    union all select 1200, 1440, '20:00-23:59'
)
select c.station_id, s.time_slot, c.start_min, c.end_min,
       -- 重叠分钟 = min(end, slot_end) - max(start, slot_start)，正值才有效
       least(c.end_min, s.slot_end_min) - greatest(c.start_min, s.slot_start_min) as overlap_min
from charge_minutes c
cross join time_slots s
where least(c.end_min, s.slot_end_min) - greatest(c.start_min, s.slot_start_min) > 0
order by c.station_id, s.time_slot, c.start_min

执行结果

+-------------+--------------+------------+----------+--------------+
| station_id  |  time_slot   | start_min  | end_min  | overlap_min  |
+-------------+--------------+------------+----------+--------------+
| A           | 00:00-03:59  | 60         | 210      | 150          |
| A           | 00:00-03:59  | 120        | 300      | 120          |
| A           | 04:00-07:59  | 120        | 300      | 60           |
| A           | 08:00-11:59  | 480        | 570      | 90           |
| A           | 12:00-15:59  | 780        | 840      | 60           |
| A           | 12:00-15:59  | 900        | 1050     | 60           |
| A           | 16:00-19:59  | 900        | 1050     | 90           |
| B           | 08:00-11:59  | 480        | 600      | 120          |
| B           | 08:00-11:59  | 540        | 660      | 120          |
| B           | 16:00-19:59  | 960        | 1080     | 120          |
+-------------+--------------+------------+----------+--------------+
10 rows selected (0.925 seconds)(https://www.dwsql.com)

关键：P02（120→300，即02:00-05:00）被拆成两行——00:00-03:59 时段重叠 120 分钟、04:00-07:59 时段重叠 60 分钟。P02（900→1050，即15:00-17:30）也拆成 12:00-15:59 的60分钟和 16:00-19:59 的90分钟。这正是跨时段拆分的核心。

3. 按站+时段聚合计算使用率

执行SQL

with charge_minutes as (
    select station_id, pile_id,
           cast(substr(start_time, 12, 2) as int) * 60 + cast(substr(start_time, 15, 2) as int) as start_min,
           cast(substr(end_time, 12, 2) as int)   * 60 + cast(substr(end_time, 15, 2) as int) as end_min
    from t2_charge_log
),
time_slots as (
    select  0 as slot_start_min,  240 as slot_end_min, '00:00-03:59' as time_slot
    union all select 240,  480, '04:00-07:59'
    union all select 480,  720, '08:00-11:59'
    union all select 720,  960, '12:00-15:59'
    union all select 960, 1200, '16:00-19:59'
    union all select 1200, 1440, '20:00-23:59'
),
pile_stats as (
    select station_id, count(distinct pile_id) as pile_cnt
    from t2_charge_log
    group by station_id
)
select o.station_id,
       o.time_slot,
       p.pile_cnt,
       o.total_minutes,
       round(o.total_minutes / (4 * p.pile_cnt * 60), 4) as usage_rate
from (
    select c.station_id, s.time_slot,
           sum(least(c.end_min, s.slot_end_min) - greatest(c.start_min, s.slot_start_min)) as total_minutes
    from charge_minutes c
    cross join time_slots s
    where least(c.end_min, s.slot_end_min) - greatest(c.start_min, s.slot_start_min) > 0
    group by c.station_id, s.time_slot
) o
join pile_stats p on o.station_id = p.station_id
order by o.station_id, o.time_slot

执行结果

+-------------+--------------+-----------+----------------+-------------+
| station_id  |  time_slot   | pile_cnt  | total_minutes  | usage_rate  |
+-------------+--------------+-----------+----------------+-------------+
| A           | 00:00-03:59  | 3         | 270            | 0.375       |
| A           | 04:00-07:59  | 3         | 60             | 0.0833      |
| A           | 08:00-11:59  | 3         | 90             | 0.125       |
| A           | 12:00-15:59  | 3         | 120            | 0.1667      |
| A           | 16:00-19:59  | 3         | 90             | 0.125       |
| B           | 08:00-11:59  | 2         | 240            | 0.5         |
| B           | 16:00-19:59  | 2         | 120            | 0.25        |
+-------------+--------------+-----------+----------------+-------------+
7 rows selected (1.181 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：不能按 start_time 归属到单一时段

一次充电跨越多个时段时（如 P02 的 02:00-05:00），简单按 start_time 的小时数归入单个时段会把整段时长（180分钟）错误地全部算到"00:00-03:59"，导致该时段使用率虚高、下一个时段漏算。必须用交叉连接 + 重叠计算拆分。

坑2：重叠计算的区间公式

重叠分钟 = least(end_min, slot_end_min) - greatest(start_min, slot_start_min)，当结果为负或0时表示无重叠。这是区间重叠的标准公式，比写一堆 CASE WHEN 判断各种相交情况更简洁不易错。

坑3：时段边界是左闭右开

"00:00-03:59"实际覆盖 00:00:00 到 03:59:59，分钟边界用 [0, 240) 表示。如果 04:00 整点恰好同时是上一时段结束和下一时段开始，需明确归属——本方案用 slot_end_min = 240 且下一时段 slot_start_min = 240，重叠公式保证边界点只算到下一时段（因为 end_min > slot_start_min 才计入）。

五、知识点总结

考点	说明
union all 构建维度表	用多段 union all 生成6个时段的分钟边界，作为交叉连接的维度
cross join + least/greatest	充电记录与时段维度交叉，用区间重叠公式计算每条记录在各时段的占用分钟
substr + cast 提取分钟	从 yyyy-MM-dd HH:mm:ss 提取时分并转当天分钟数，替代 MySQL 的 hour()/minute()
子查询分层 + join 聚合	先算重叠分钟、再聚合、最后 join 桩数求使用率，逻辑清晰

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t2_charge_log (
    pile_id    string comment '充电桩ID',
    station_id string comment '充电站ID',
    start_time string comment '开始充电时间',
    end_time   string comment '结束充电时间'
) comment '充电桩使用记录表';

-- 数据插入
insert into t2_charge_log values
('P01', 'A', '2024-01-01 01:00:00', '2024-01-01 03:30:00'),
('P02', 'A', '2024-01-01 02:00:00', '2024-01-01 05:00:00'),
('P03', 'A', '2024-01-01 08:00:00', '2024-01-01 09:30:00'),
('P01', 'A', '2024-01-01 13:00:00', '2024-01-01 14:00:00'),
('P02', 'A', '2024-01-01 15:00:00', '2024-01-01 17:30:00'),
('P04', 'B', '2024-01-01 08:00:00', '2024-01-01 10:00:00'),
('P05', 'B', '2024-01-01 09:00:00', '2024-01-01 11:00:00'),
('P04', 'B', '2024-01-01 16:00:00', '2024-01-01 18:00:00');

📱关注公众号

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

💬加群交流

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