SQL 电池健康度衰减分析：续航里程随时间变化（蔚来面试题）

一、题目

现有一张电池健康度检测记录表 t3_battery_health，记录了每辆车每次检测时的电池健康度（SOH，State of Health，以百分比表示）。请计算每辆车电池健康度的衰减情况：

• 首次检测的健康度
• 最近一次检测的健康度
• 总衰减幅度（首次 - 最近）
• 相邻两次检测之间的单次最大衰减幅度

电池健康度表 t3_battery_health：

+---------+-------------+-------+
| car_id  | check_date  |  soh  |
+---------+-------------+-------+
| C001    | 2023-06-01  | 99.8  |
| C001    | 2023-09-01  | 98.5  |
| C001    | 2023-12-01  | 97.2  |
| C001    | 2024-03-01  | 96.0  |
| C001    | 2024-06-01  | 94.8  |
| C002    | 2023-08-01  | 99.5  |
| C002    | 2023-11-01  | 98.0  |
| C002    | 2024-02-01  | 97.5  |
| C003    | 2024-01-01  | 98.2  |
| C003    | 2024-04-01  | 96.8  |
+---------+-------------+-------+

二、思路分析

本题考察窗口函数的综合运用，需要结合 FIRST_VALUE 和 LAG 来计算衰减指标。

解题步骤：

1. 使用 FIRST_VALUE(soh) OVER (PARTITION BY car_id ORDER BY check_date) 获取每辆车的首次健康度；
2. 使用 FIRST_VALUE(soh) OVER (PARTITION BY car_id ORDER BY check_date DESC) 获取最近一次健康度；
3. 使用 LAG(soh, 1) OVER (PARTITION BY car_id ORDER BY check_date) 获取上一次检测的健康度，计算相邻衰减 prev_soh - soh；
4. 按车聚合，取 max(first_soh)、max(latest_soh)、max(single_decay)，计算总衰减。

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

三、逐步推导

1. 使用窗口函数计算各类指标

执行SQL

select car_id,
       check_date,
       soh,
       first_value(soh) over (partition by car_id order by check_date) as first_soh,
       first_value(soh) over (partition by car_id order by check_date desc) as latest_soh,
       lag(soh, 1) over (partition by car_id order by check_date) as prev_soh,
       round(lag(soh, 1) over (partition by car_id order by check_date) - soh, 2) as single_decay
from t3_battery_health

执行结果

+---------+-------------+-------+------------+-------------+-----------+---------------+
| car_id  | check_date  |  soh  | first_soh  | latest_soh  | prev_soh  | single_decay  |
+---------+-------------+-------+------------+-------------+-----------+---------------+
| C001    | 2024-06-01  | 94.8  | 99.8       | 94.8        | 96.0      | 1.2           |
| C001    | 2024-03-01  | 96.0  | 99.8       | 94.8        | 97.2      | 1.2           |
| C001    | 2023-12-01  | 97.2  | 99.8       | 94.8        | 98.5      | 1.3           |
| C001    | 2023-09-01  | 98.5  | 99.8       | 94.8        | 99.8      | 1.3           |
| C001    | 2023-06-01  | 99.8  | 99.8       | 94.8        | NULL      | NULL          |
| C002    | 2024-02-01  | 97.5  | 99.5       | 97.5        | 98.0      | 0.5           |
| C002    | 2023-11-01  | 98.0  | 99.5       | 97.5        | 99.5      | 1.5           |
| C002    | 2023-08-01  | 99.5  | 99.5       | 97.5        | NULL      | NULL          |
| C003    | 2024-04-01  | 96.8  | 98.2       | 96.8        | 98.2      | 1.4           |
| C003    | 2024-01-01  | 98.2  | 98.2       | 96.8        | NULL      | NULL          |
+---------+-------------+-------+------------+-------------+-----------+---------------+
10 rows selected (0.539 seconds)(https://www.dwsql.com)

2. 汇总每辆车的衰减分析

执行SQL

select car_id,
       max(first_soh)                                               as first_soh,
       max(latest_soh)                                              as latest_soh,
       round(max(first_soh) - max(latest_soh), 2)                   as total_decay,
       max(single_decay)                                            as max_single_decay
from (
    select car_id,
           first_value(soh) over (partition by car_id order by check_date)      as first_soh,
           first_value(soh) over (partition by car_id order by check_date desc)  as latest_soh,
           round(lag(soh, 1) over (partition by car_id order by check_date) - soh, 2) as single_decay
    from t3_battery_health
) t
group by car_id
order by total_decay desc

执行结果

+---------+------------+-------------+--------------+-------------------+
| car_id  | first_soh  | latest_soh  | total_decay  | max_single_decay  |
+---------+------------+-------------+--------------+-------------------+
| C001    | 99.8       | 94.8        | 5.0          | 1.3               |
| C002    | 99.5       | 97.5        | 2.0          | 1.5               |
| C003    | 98.2       | 96.8        | 1.4          | 1.4               |
+---------+------------+-------------+--------------+-------------------+
3 rows selected (0.709 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：LAG首条记录返回NULL — 每辆车的第一条检测记录没有"上一次检测"，LAG(soh, 1) 返回NULL，导致 single_decay 为NULL。这本身是正确的业务语义（首条记录不存在相邻衰减），外层 max(single_decay) 会自动跳过NULL值，无需额外处理。

坑2：first_value窗口函数的默认窗口帧 — FIRST_VALUE 的默认窗口帧是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，恰好从分区第一行到当前行，能正确取到"首次检测值"。但如果使用 LAST_VALUE 获取最近一次检测值，必须显式指定 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING，否则只会返回当前行的值而非分区最后一行。因此直接用 FIRST_VALUE(...ORDER BY ... DESC) 更简洁安全。

坑3：单次异常衰减的识别 — 电池健康度正常情况下是缓慢衰减的（每季度约0.5%~1.5%）。如果某次检测 single_decay 突然超过3%，很可能是检测误差（如低温环境下测量不准、检测设备校准问题），而非真实的电池容量跳水。生产环境中建议：对 max_single_decay 异常偏大的车辆打标，结合温度、里程等维度做二次确认，避免误判电池质量问题。

五、知识点总结

考点	说明
FIRST_VALUE	获取分区内首条/末条记录值，配合 ORDER BY ASC/DESC 分别取首尾，注意默认窗口帧为 UNBOUNDED PRECEDING TO CURRENT ROW
LAG / LEAD	获取前/后一行数据，用于环比计算、相邻衰减分析，首条返回 NULL 需注意业务语义
窗口函数 + GROUP BY 聚合	先用窗口函数计算行级指标，再按维度聚合为最终结果，是数据分析的标准两段式写法
单次异常 vs 累积趋势	同时输出总衰减和单次最大衰减，兼顾长期趋势和短期异常，是健康度监控的核心指标体系

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t3_battery_health (
    car_id     string  comment '车辆ID',
    check_date string  comment '检测日期',
    soh        double  comment '电池健康度(SOH, %)'
) comment '电池健康度检测记录表';

-- 数据插入
insert into t3_battery_health values
('C001', '2023-06-01', 99.8),
('C001', '2023-09-01', 98.5),
('C001', '2023-12-01', 97.2),
('C001', '2024-03-01', 96.0),
('C001', '2024-06-01', 94.8),
('C002', '2023-08-01', 99.5),
('C002', '2023-11-01', 98.0),
('C002', '2024-02-01', 97.5),
('C003', '2024-01-01', 98.2),
('C003', '2024-04-01', 96.8);

📱关注公众号

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

💬加群交流

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