本文介绍如何在单表中通过 sql 原生方式识别连续的“引擎启动（on）→ 引擎关闭（off）”行程对，避免全量拉取+应用层遍历，显著提升性能。
本文介绍如何在单表中通过 sql 原生方式识别连续的“引擎启动（on）→ 引擎关闭（off）”行程对，避免全量拉取+应用层遍历，显著提升性能。
在车联网、车队管理或车辆状态分析场景中，常需将离散的引擎状态记录聚合成有意义的“行程（Trip）”——即一次完整的“启动 → 运行 → 关闭”过程。理想情况下，一个 Trip 应以首个 engine = 'on' 记录为起点，以紧随其后的首个 engine = 'off' 记录为终点（且该 off 之前无更近的 off 干扰）。但注意：原始数据中存在连续 on 或连续 off，因此不能简单按值分组，而需识别状态转换的有序配对。
虽然问题答案中给出的子查询写法看似简洁：
SELECT * FROM cars WHERE `engine` = 'off' AND id IN (SELECT id + 1 FROM `cars` WHERE `engine` = 'on');
⚠️ 该写法存在严重逻辑缺陷：它仅匹配 off 记录的 id 恰好等于某个 on 记录 id + 1 的情况，即强制要求 on 和 off 必须物理相邻（ID 连续）。但真实数据中，ID 递增仅反映插入顺序，不保证状态严格交替；例如 ID=1(on)、ID=2(on)、ID=3(off)，此时 ID=3 的 off 实际对应的是 ID=1 的 on（首次启动），而非 ID=2 —— 上述查询会错误地将 ID=3 排除（因 ID=3 ≠ 1+1 且 ≠ 2+1），导致漏判。
✅ 正确解法应基于窗口函数识别状态变化边界（MySQL 8.0+）：
WITH status_changes AS (
SELECT
id,
car_id,
engine,
LAG(engine) OVER (PARTITION BY car_id ORDER BY id) AS prev_engine
FROM cars
),
trip_starts AS (
SELECT id AS start_id, car_id
FROM status_changes
WHERE engine = 'on' AND (prev_engine IS NULL OR prev_engine = 'off')
),
trip_ends AS (
SELECT id AS end_id, car_id
FROM status_changes
WHERE engine = 'off' AND prev_engine = 'on'
)
SELECT
s.start_id,
e.end_id,
s.car_id,
CONCAT('Trip_', ROW_NUMBER() OVER (ORDER BY s.start_id)) AS trip_label
FROM trip_starts s
INNER JOIN trip_ends e
ON s.car_id = e.car_id
AND e.end_id > s.start_id
WHERE NOT EXISTS (
-- 确保 e.end_id 是 s.start_id 之后第一个 off
SELECT 1 FROM trip_ends e2
WHERE e2.car_id = s.car_id
AND e2.end_id > s.start_id
AND e2.end_id < e.end_id
);该方案核心逻辑：
- status_changes：用 LAG() 获取每条记录前一状态，精准定位“由 off → on”（Trip 起点）和“由 on → off”（Trip 终点）；
- trip_starts / trip_ends：分别提取合法起点与终点；
- 最终 JOIN + NOT EXISTS 确保每个 start_id 匹配其后最近的一个 end_id，实现语义正确的行程配对。
? 注意事项：
- 若使用 MySQL < 8.0，请升级或改用变量模拟窗口逻辑（性能较低，不推荐生产环境）；
- 索引优化至关重要：务必在 (car_id, id) 上建立复合索引，加速 ORDER BY 和关联操作；
- 若存在长时间运行未关机的行程（即无对应 off），需额外处理“进行中 Trip”，可在 trip_starts 后 LEFT JOIN 并判断 e.end_id IS NULL；
- 实际业务中建议增加时间戳字段（如 created_at）替代 id 排序，避免 ID 不连续或并发插入导致时序错乱。
总结：状态序列分析绝非简单值匹配，必须依托有序上下文识别转换点。借助窗口函数可将原本需应用层 O(n²) 遍历的逻辑，下沉为数据库内高效集合运算，既提升响应速度，又保障结果准确性。
