SQL OTA升级完成率：已升级车辆/总车辆（理想汽车面试题）

一、题目

现有一张 OTA 升级记录表 t2_ota_record，记录了每辆车每次 OTA 升级的状态信息。请统计各车型的 OTA 升级完成率（升级成功数 / 总升级发起数），按完成率降序排列。

OTA升级记录表 t2_ota_record：

+---------+--------+--------------+--------------+
| car_id  | model  | ota_version  |    status    |
+---------+--------+--------------+--------------+
| C001    | L7     | v4.5.0       | success      |
| C002    | L9     | v4.5.0       | success      |
| C003    | L8     | v4.5.0       | success      |
| C004    | L7     | v4.5.0       | failed       |
| C005    | L9     | v4.5.0       | success      |
| C006    | L7     | v4.5.0       | success      |
| C007    | L8     | v4.5.0       | downloading  |
| C008    | L9     | v4.5.0       | failed       |
| C009    | L7     | v4.5.0       | success      |
| C010    | L8     | v4.5.0       | success      |
+---------+--------+--------------+--------------+

二、思路分析

本题考察条件聚合和分组统计。核心在于正确区分"已发起"和"已成功"的升级记录。

解题步骤：

1. 按 model 分组统计总升级次数和成功次数；
2. 使用 COUNT(1) 统计总数，COUNT(CASE WHEN status='success' THEN 1 END) 统计成功数；
3. 计算完成率 = 成功数 / 总数，按完成率降序。

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

三、逐步推导

1. 按车型统计升级总数和成功数

执行SQL

select model,
       count(1)                                              as total_cnt,
       count(case when status = 'success' then 1 end)        as success_cnt
from t2_ota_record
group by model

执行结果

+--------+------------+--------------+
| model  | total_cnt  | success_cnt  |
+--------+------------+--------------+
| L8     | 3          | 2            |
| L7     | 4          | 3            |
| L9     | 3          | 2            |
+--------+------------+--------------+
3 rows selected (0.814 seconds)(https://www.dwsql.com)

2. 计算完成率并排序

执行SQL

select model,
       count(1)                                                   as total_cnt,
       count(case when status = 'success' then 1 end)             as success_cnt,
       round(count(case when status = 'success' then 1 end) / count(1), 4) as completion_rate
from t2_ota_record
group by model
order by completion_rate desc

执行结果

+--------+------------+--------------+------------------+
| model  | total_cnt  | success_cnt  | completion_rate  |
+--------+------------+--------------+------------------+
| L7     | 4          | 3            | 0.75             |
| L8     | 3          | 2            | 0.6667           |
| L9     | 3          | 2            | 0.6667           |
+--------+------------+--------------+------------------+
3 rows selected (0.637 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：条件计数用 COUNT 而非 SUM — 统计满足条件的记录数应使用 count(case when status='success' then 1 end)，它只统计非 NULL 的值；sum(case when ... then 1 else 0 end) 语义上是"求和"而非"计数"，漏写 ELSE 时 NULL 会参与求和导致结果异常。

坑2：状态值口径不明确 — status 有 success / failed / downloading 等多种取值，只有 success 才算升级完成，downloading（下载中）和 failed（失败）都不能计入成功数，口径混乱会导致完成率偏高。

坑3：同一辆车多次升级记录 — 同一辆车可能多次发起 OTA 升级，若以"车辆数"为口径需先按 car_id 去重，否则重复记录会稀释或放大完成率，需明确统计对象是"升级次数"还是"升级车辆数"。

五、知识点总结

考点	说明
COUNT(CASE WHEN) 条件计数	只统计满足条件的非 NULL 值，实现"成功数/总数"的条件计数
ROUND 精度控制	完成率结果用 round(x, 4) 保留小数位，结果更清晰
ORDER BY 排序	按完成率降序排列，快速定位完成率最高/最低的车型

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t2_ota_record (
    car_id      string comment '车辆ID',
    model       string comment '车型',
    ota_version string comment 'OTA版本号',
    status      string comment '升级状态(success/failed/downloading)'
) comment 'OTA升级记录表';

-- 数据插入
insert into t2_ota_record values
('C001', 'L7', 'v4.5.0', 'success'),
('C002', 'L9', 'v4.5.0', 'success'),
('C003', 'L8', 'v4.5.0', 'success'),
('C004', 'L7', 'v4.5.0', 'failed'),
('C005', 'L9', 'v4.5.0', 'success'),
('C006', 'L7', 'v4.5.0', 'success'),
('C007', 'L8', 'v4.5.0', 'downloading'),
('C008', 'L9', 'v4.5.0', 'failed'),
('C009', 'L7', 'v4.5.0', 'success'),
('C010', 'L8', 'v4.5.0', 'success');

📱关注公众号

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

💬加群交流

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