在车联网和设备监控业务中，车辆或机器的引擎状态会不断上报，每条记录带有时间戳与引擎开关标记。所谓一次行程（Trip），通常是指从引擎启动到下一次停止之间连续产生的数据片段。要在MySQL中按引擎启停序列把原始数据聚合成行程，核心是先找出状态跳变点，再对跳变后的连续启动段编号，最后按编号分组。
一、表结构与示例数据
假设有一张引擎状态表 engine_log，字段如下：
| 字段 | 类型 | 说明 |
|---|---|---|
| id | bigint | 自增主键 |
| device_id | varchar(32) | 设备编号 |
| log_time | datetime | 上报时间 |
| engine_on | tinyint | 1启动 0停止 |
示例插入数据：
INSERT INTO engine_log(device_id, log_time, engine_on) VALUES
('D1', '2023-01-01 08:00:00', 0),
('D1', '2023-01-01 08:01:00', 1),
('D1', '2023-01-01 08:05:00', 1),
('D1', '2023-01-01 08:30:00', 0),
('D1', '2023-01-01 09:00:00', 1),
('D1', '2023-01-01 09:20:00', 0);
二、基于会话变量的识别方案
思路是：按设备与时间排序，当 engine_on 由 0 变 1 时开启新行程，用变量记录当前行程号。以下查询在 MySQL 5.7 等旧版本也可运行。
SET @trip_no := 0;
SET @prev_on := 0;
SELECT
device_id,
trip_no,
MIN(log_time) AS trip_start,
MAX(log_time) AS trip_end,
COUNT(*) AS record_cnt
FROM (
SELECT
id,
device_id,
log_time,
engine_on,
@trip_no := IF(engine_on = 1 AND @prev_on = 0, @trip_no + 1, @trip_no) AS trip_no,
@prev_on := engine_on
FROM engine_log
WHERE engine_on = 1
ORDER BY device_id, log_time
) t
GROUP BY device_id, trip_no;
逻辑说明
- 子查询中只保留 engine_on=1 的记录，减少无关停止行。
- @prev_on 保存上一行状态，遇到 0 变 1 则行程号加一。
- 外层按设备与行程号聚合，得到起止时间与记录数。
三、基于窗口函数的现代写法
MySQL 8.0 支持窗口函数，可以用 LAG() 判断前一行状态，再用求和产生行程组标识。
SELECT
device_id,
trip_id,
MIN(log_time) AS trip_start,
MAX(log_time) AS trip_end,
COUNT(*) AS record_cnt
FROM (
SELECT
device_id,
log_time,
engine_on,
SUM(CASE WHEN prev_on = 0 AND engine_on = 1 THEN 1 ELSE 0 END)
OVER (PARTITION BY device_id ORDER BY log_time) AS trip_id
FROM (
SELECT
device_id,
log_time,
engine_on,
LAG(engine_on, 1, 0) OVER (PARTITION BY device_id ORDER BY log_time) AS prev_on
FROM engine_log
) s1
WHERE engine_on = 1
) s2
GROUP BY device_id, trip_id;
优点
- 无需会话变量，语句可重入且易读。
- 分区与排序由数据库优化器处理，大数据量下更稳定。
四、性能与索引建议
无论采用哪种写法，都应建立复合索引加速排序与过滤：
CREATE INDEX idx_dev_time_on ON engine_log(device_id, log_time, engine_on);
如果设备数量极多，可考虑按 device_id 做分区表，或将行程识别放入定时任务写入独立 trip 表，查询时直接读取结果。
五、小结
按引擎启停序列识别 Trip，本质是状态跳变检测与连续段分组。旧版本用会话变量打标，新版本用 LAG 与累加窗口更优雅。配合合理索引，即使在千万级日志中也能秒级完成行程切分。