蔚来大数据面试SQL-充电桩使用率分析
⚠️ 待修正
一、题目背景
这道题来自蔚来的数据分析岗面试。蔚来是高端新能源车企，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：车联网相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张充电桩使用记录表 t1_charge_log，记录了每个充电桩每次被使用的起止时间。请计算每个充电站在 2024-01-01 这天各时段（每4小时为一个时段：00:00-03:59, 04:00-07:59, ..., 20:00-23:59）的充电桩使用率。
使用率 = 该时段内充电桩总占用时长 / (时段小时数 * 充电桩数量 * 60 分钟)
充电记录表 t1_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 |
+----------+-------------+---------------------+---------------------+
三、思路分析
本题考察时间区间分段统计，需要将每条充电记录的时长按小时拆分到各个时段中。
解题步骤：
- 先统计每个充电站的充电桩总数（去重
pile_id）； - 构建 24 小时的时间维度表，按 4 小时间隔分 6 个时段；
- 将充电记录与时段维度关联：充电记录如果在某个时段内有时间重合，则计算重合部分的分钟数；
- 按充电站和时段分组，聚合占用分钟数，除以（时段小时数 * 桩数 * 60）得到使用率。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 统计每个充电站的充电桩数量
执行SQL
select station_id,
count(distinct pile_id) as pile_cnt
from t1_charge_log
group by station_id
执行结果
+-------------+----------+
| station_id | pile_cnt |
+-------------+----------+
| A | 3 |
| B | 2 |
+-------------+----------+
2. 按时段计算充电桩使用率
执行SQL
select station_id,
time_slot,
round(total_minutes / (4 * pile_cnt * 60), 4) as usage_rate
from (
select c.station_id,
p.pile_cnt,
case
when hour(c.start_time) < 4 then '00:00-03:59'
when hour(c.start_time) < 8 then '04:00-07:59'
when hour(c.start_time) < 12 then '08:00-11:59'
when hour(c.start_time) < 16 then '12:00-15:59'
when hour(c.start_time) < 20 then '16:00-19:59'
else '20:00-23:59'
end as time_slot,
sum(timestampdiff(minute, c.start_time, c.end_time)) as total_minutes
from t1_charge_log c
join (
select station_id, count(distinct pile_id) as pile_cnt
from t1_charge_log
group by station_id
) p on c.station_id = p.station_id
group by c.station_id, p.pile_cnt, time_slot
) t
order by station_id, time_slot
执行结果
+-------------+-------------+------------+
| station_id | time_slot | usage_rate |
+-------------+-------------+------------+
| A | 00:00-03:59 | 0.3333 |
| A | 04:00-07:59 | 0.0833 |
| A | 08:00-11:59 | 0.1250 |
| A | 12:00-15:59 | 0.0833 |
| A | 16:00-19:59 | 0.2083 |
| B | 08:00-11:59 | 0.4167 |
| B | 16:00-19:59 | 0.2500 |
+-------------+-------------+------------+
五、常见坑点
坑1：分子分母的业务定义 — 明确分子是"延迟订单数"还是"延迟用户数"，分母对应"总订单数"还是"总用户数"，口径不同结果差异大。
坑2：时间差计算的单位 — unix_timestamp 返回秒，除以60才是分钟。忘记单位转换会导致判断完全错误。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_charge_log (
pile_id string COMMENT '充电桩ID',
station_id string COMMENT '充电站ID',
start_time string COMMENT '开始充电时间',
end_time string COMMENT '结束充电时间'
) COMMENT '充电桩使用记录表';
-- 数据插入
INSERT INTO t1_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真题
