本文介绍如何利用 MySQL 8.0+ 的 LAG() 窗口函数识别时间区间空隙，并生成对应 status = 'stopped' 的补全记录，支持直接 INSERT 或作为 ETL 步骤集成到 PHP 脚本中。
本文介绍如何利用 mysql 8.0+ 的 lag() 窗口函数识别时间区间空隙，并生成对应 `status = 'stopped'` 的补全记录，支持直接 insert 或作为 etl 步骤集成到 php 脚本中。
在监控类、工单系统或设备运行日志等场景中，常需将离散的“运行时段”（status = 'running'）补全为连续的全天时间线——即在相邻运行段之间、首段之前、末段之后自动填充 status = 'stopped' 的空白时段。MySQL 8.0 引入的窗口函数为此类时间对齐任务提供了高效、声明式的解决方案，无需依赖应用层循环或临时表。
核心思路：用 LAG() 定位时间断点
关键在于将每条记录的 startdate + starttime 转换为完整 DATETIME，再通过 LAG() 获取前一条记录的 enddate + endtime。若当前起始时间 ≠ 前一条结束时间，说明中间存在空隙，即可构造一条新记录：
- 新 startdate / starttime ← 前一条的 enddate / endtime
- 新 enddate / endtime ← 当前记录的 startdate / starttime
- 新 status ← 'stopped'
⚠️ 注意：该逻辑默认按 enddate, endtime 排序（即按事件自然结束顺序），确保时间轴连续性。若数据存在跨天或乱序，需先清洗或调整 ORDER BY 子句。
完整 SQL 实现（含 INSERT）
以下 CTE 查询返回所有待插入的 'stopped' 区间。如需直接写入原表，请将 SELECT 替换为 INSERT INTO your_table (...) SELECT ...：
WITH cte AS (
SELECT *,
-- 将前一条记录的 enddate+endtime 合并为 DATETIME；首行为 NULL
LAG(
STR_TO_DATE(CONCAT(enddate, ' ', endtime), '%Y-%m-%d %H:%i:%s'),
1,
NULL
) OVER (ORDER BY enddate, endtime) AS prev_end_datetime,
-- 当前记录的起始时间（用于比对）
STR_TO_DATE(CONCAT(startdate, ' ', starttime), '%Y-%m-%d %H:%i:%s') AS start_datetime
FROM your_table
)
SELECT
COALESCE(
DATE(prev_end_datetime),
startdate -- 若无前驱（首条），则从当天 00:00:00 开始
) AS startdate,
COALESCE(
TIME(prev_end_datetime),
'00:00:00'
) AS starttime,
startdate AS enddate,
starttime AS endtime,
'stopped' AS status
FROM cte
WHERE
start_datetime != prev_end_datetime
OR prev_end_datetime IS NULL;✅ 输出示例（基于原始数据）： | startdate | starttime | enddate | endtime | status | |------------|-----------|------------|----------|---------| | 2020-03-04 | 00:00:00 | 2020-03-04 | 04:30:00 | stopped | | 2020-03-04 | 09:00:00 | 2020-03-04 | 11:30:00 | stopped | | 2020-03-04 | 19:30:00 | 2020-03-05 | 05:00:00 | stopped | | 2020-03-05 | 11:15:00 | 2020-03-05 | 12:30:00 | stopped |
集成到 PHP 脚本（可选增强）
若需在业务逻辑中动态执行，推荐封装为安全可复用的函数：
<?php
function insertStoppedIntervals(PDO $pdo, string $table): int {
$sql = "WITH cte AS (
SELECT *,
LAG(STR_TO_DATE(CONCAT(enddate, ' ', endtime), '%Y-%m-%d %H:%i:%s'), 1, NULL)
OVER (ORDER BY enddate, endtime) AS prev_end_datetime,
STR_TO_DATE(CONCAT(startdate, ' ', starttime), '%Y-%m-%d %H:%i:%s') AS start_datetime
FROM {$table}
)
INSERT INTO {$table} (startdate, starttime, enddate, endtime, status)
SELECT
COALESCE(DATE(prev_end_datetime), startdate),
COALESCE(TIME(prev_end_datetime), '00:00:00'),
startdate,
starttime,
'stopped'
FROM cte
WHERE start_datetime != prev_end_datetime OR prev_end_datetime IS NULL";
return $pdo->exec($sql);
}
// 使用示例
try {
$pdo = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$affected = insertStoppedIntervals($pdo, 'operation_log');
echo "成功插入 {$affected} 条 'stopped' 记录。\n";
} catch (PDOException $e) {
error_log("SQL Error: " . $e->getMessage());
}
?>注意事项与最佳实践
- ✅ 版本要求：必须使用 MySQL 8.0+，低版本需改用自连接或存储过程模拟 LAG 行为（性能显著下降）。
- ✅ 索引优化：确保 (enddate, endtime) 上有复合索引，加速 ORDER BY 和窗口计算。
- ⚠️ 边界处理：当前逻辑未覆盖“末段结束后至当日 23:59:59”的停机区间。如需补全全天，可在最终结果后追加 UNION ALL 查询当日最后运行段之后的剩余时段。
- ⚠️ 数据一致性：执行 INSERT 前建议先用 SELECT 验证结果；生产环境务必在事务中操作，并添加 WHERE NOT EXISTS 防止重复插入。
- ? 增量更新：若数据持续写入，可定期（如每日凌晨）运行该脚本，或监听新增记录触发补全逻辑。
通过该方案，你不仅能精准识别时间缝隙，还能以声明式 SQL 实现高可读、易维护的时段补全逻辑，大幅提升时序数据治理效率。
