SQL 车辆远程诊断故障码统计：按车型/故障类型聚合（理想汽车面试题）

一、题目

现有一张车辆远程诊断记录表 t3_diag_log，其中 fault_codes 字段以逗号分隔存储每次诊断上报的故障码列表。请统计每个故障码的出现次数，按出现次数降序排列，找出最频繁的 Top 5 故障码。

远程诊断记录表 t3_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  |
+---------+----------------------+----------------------+

二、思路分析

本题考察字符串拆分和聚合统计，需要使用 SPLIT 将逗号分隔的故障码拆分为数组，再通过 EXPLODE 展开为多行，最后进行 GROUP BY 聚合统计。

解题步骤：

1. 使用 SPLIT(fault_codes, ',') 将故障码字段拆分为数组；
2. 使用 LATERAL VIEW EXPLODE 将数组展开为多行；
3. 按故障码分组统计出现次数；
4. 降序排列取 Top 5。

语法说明：LATERAL VIEW EXPLODE(...) 是 Hive 的语法，Spark SQL 同样兼容；Spark SQL 更原生的写法是 SELECT explode(split(fault_codes, ',')) AS fault_code 配合交叉连接（cross join）。两者展开效果一致，面试时能说明两种写法的差异是加分项。

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

三、逐步推导

1. 拆分故障码并展开

执行SQL

select car_id,
       diag_time,
       fault_code
from t3_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        |
| C001    | 2024-01-02 08:00:00  | P001        |
| C001    | 2024-01-02 08:00:00  | P005        |
| C004    | 2024-01-02 09:30:00  | P003        |
| C002    | 2024-01-02 10:45:00  | P001        |
| C002    | 2024-01-02 10:45:00  | P002        |
| C002    | 2024-01-02 10:45:00  | P003        |
| C005    | 2024-01-02 11:00:00  | P004        |
| C005    | 2024-01-02 11:00:00  | P005        |
| C003    | 2024-01-02 12:30:00  | P001        |
| C003    | 2024-01-02 12:30:00  | P002        |
| C001    | 2024-01-03 08:15:00  | P005        |
| C004    | 2024-01-03 09:45:00  | P001        |
| C004    | 2024-01-03 09:45:00  | P002        |
| C004    | 2024-01-03 09:45:00  | P003        |
| C004    | 2024-01-03 09:45:00  | P004        |
+---------+----------------------+-------------+
21 rows selected (0.336 seconds)(https://www.dwsql.com)

2. 统计故障码频次并取 Top 5

执行SQL

select fault_code,
       count(1) as cnt
from t3_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        | 6    |
| P002        | 5    |
| P003        | 4    |
| P004        | 3    |
| P005        | 3    |
+-------------+------+
5 rows selected (0.498 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：split + explode 展开逗号分隔字段 — split() 先按分隔符切成数组，再 explode() 一行拆多行。分隔符写错（如中文逗号 ，）或字段实际用其他符号分隔，拆分结果会出错，统计随之失真。

坑2：空 fault_codes 字段的处理 — split('', ',') 会返回 ['']，explode 后展开成一条空字符串记录并计入 count。展开前用 WHERE fault_codes IS NOT NULL AND fault_codes <> '' 过滤，或展开后 WHERE fault_code <> '' 排除空串。

坑3：lateral view explode 与 explode 的方言差异 — lateral view explode(...) 是 Hive 语法，Spark SQL 兼容；Spark 原生写法为 SELECT explode(split(fault_codes, ',')) AS fault_code，若要同时保留其他列需 cross join。两者效果一致，但注意 lateral view 写在 from 之后，而 explode 写在 select 里。

五、知识点总结

考点	说明
split + explode 展开	将逗号分隔字段拆成数组再一行拆多行，是列转行的核心手段
count 分组统计	按 fault_code 分组，count(1) 统计每个故障码出现次数
order by + limit 取 TopN	降序排列后 limit 5 取出最频繁的 Top 5 故障码

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t3_diag_log (
    car_id      string comment '车辆ID',
    diag_time   string comment '诊断时间',
    fault_codes string comment '故障码列表(逗号分隔)'
) comment '车辆远程诊断记录表';

-- 数据插入
insert into t3_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真题