理想汽车大数据面试SQL-车辆远程诊断故障码统计
⚠️ 待修正
一、题目背景
这道题来自理想汽车的数据分析岗面试。理想汽车是新能源车企，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：车联网相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张车辆远程诊断记录表 t1_diag_log，其中 fault_codes 字段以逗号分隔存储每次诊断上报的故障码列表。请统计每个故障码的出现次数，按出现次数降序排列，找出最频繁的 Top 5 故障码。
远程诊断记录表 t1_diag_log：
+----------+---------------------+---------------------------+
| car_id | diag_time | fault_codes |
+----------+---------------------+---------------------------+
| C001 | 2024-01-01 08:30:00 | P001,P002 |
| C002 | 2024-01-01 09:15:00 | P001 |
| C003 | 2024-01-01 10:00:00 | P002,P003,P004 |
| C001 | 2024-01-02 08:00:00 | P001,P005 |
| C004 | 2024-01-02 09:30:00 | P003 |
| C002 | 2024-01-02 10:45:00 | P001,P002,P003 |
| C005 | 2024-01-02 11:00:00 | P004,P005 |
| C003 | 2024-01-02 12:30:00 | P001,P002 |
| C001 | 2024-01-03 08:15:00 | P005 |
| C004 | 2024-01-03 09:45:00 | P001,P002,P003,P004 |
+----------+---------------------+---------------------------+
三、思路分析
本题考察字符串拆分和聚合统计，需要使用 EXPLODE 结合 SPLIT 将逗号分隔的故障码展开为多行，再进行 GROUP BY 聚合。
解题步骤：
- 使用
SPLIT(fault_codes, ',')将故障码字段拆分为数组； - 使用
LATERAL VIEW EXPLODE将数组展开为多行； - 按故障码分组统计出现次数；
- 降序排列取 Top 5。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 拆分故障码并展开
执行SQL
select car_id,
diag_time,
fault_code
from t1_diag_log
lateral view explode(split(fault_codes, ',')) t as fault_code
执行结果
+----------+---------------------+------------+
| car_id | diag_time | fault_code |
+----------+---------------------+------------+
| C001 | 2024-01-01 08:30:00 | P001 |
| C001 | 2024-01-01 08:30:00 | P002 |
| C002 | 2024-01-01 09:15:00 | P001 |
| C003 | 2024-01-01 10:00:00 | P002 |
| C003 | 2024-01-01 10:00:00 | P003 |
| C003 | 2024-01-01 10:00:00 | P004 |
| ... | ... | ... |
+----------+---------------------+------------+
2. 统计故障码频次并取 Top 5
执行SQL
select fault_code,
count(1) as cnt
from t1_diag_log
lateral view explode(split(fault_codes, ',')) t as fault_code
group by fault_code
order by cnt desc
limit 5
执行结果
+------------+------+
| fault_code | cnt |
+------------+------+
| P001 | 7 |
| P002 | 5 |
| P003 | 4 |
| P004 | 3 |
| P005 | 3 |
+------------+------+
五、常见坑点
坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。
坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。
六、举一反三
-
动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略
-
累计分布CDF：
SUM(cnt) OVER (ORDER BY bucket)算累计占比，画帕累托图 -
按时间维度对比：按天/周/月分区，观察分布的时序变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_diag_log (
car_id string COMMENT '车辆ID',
diag_time string COMMENT '诊断时间',
fault_codes string COMMENT '故障码列表(逗号分隔)'
) COMMENT '车辆远程诊断记录表';
-- 数据插入
INSERT INTO t1_diag_log VALUES
('C001', '2024-01-01 08:30:00', 'P001,P002'),
('C002', '2024-01-01 09:15:00', 'P001'),
('C003', '2024-01-01 10:00:00', 'P002,P003,P004'),
('C001', '2024-01-02 08:00:00', 'P001,P005'),
('C004', '2024-01-02 09:30:00', 'P003'),
('C002', '2024-01-02 10:45:00', 'P001,P002,P003'),
('C005', '2024-01-02 11:00:00', 'P004,P005'),
('C003', '2024-01-02 12:30:00', 'P001,P002'),
('C001', '2024-01-03 08:15:00', 'P005'),
('C004', '2024-01-03 09:45:00', 'P001,P002,P003,P004');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
