LAG()需配合PARTITION BY device_id和ORDER BY collect_time, id获取上一行时间戳，再计算差值判断卡顿或丢包；首行NULL须用COALESCE或显式判断处理，时间字段必须为TIMESTAMP类型。
LAG函数怎么拿到上一行的时间戳
核心是用 LAG() 提取前一条记录的采集时间，再和当前行做差值判断是否超时。必须按设备ID + 时间顺序严格排序，否则差值毫无意义。
常见错误是漏写 ORDER BY 或排序字段不唯一（比如只按时间排，但同一秒有多条数据），导致 LAG() 返回随机上一行。
- 正确写法：
LAG(collect_time) OVER (PARTITION BY device_id ORDER BY collect_time, id) - 如果时间字段有重复，一定要补一个能打破平局的字段（如自增
id或log_seq） - 注意：MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle 支持；SQLite 需 3.25+ 且开启窗口函数
怎么定义“卡顿”和“丢包”的阈值
卡顿看时间间隔是否异常拉长，丢包则需识别连续缺失的采集点——但 SQL 本身不擅长“找空缺”，得换思路：用预期采集周期反推应有行数，再对比实际行数。
例如每10秒采一次，过去5分钟该有30条，结果只有22条 → 丢8包。但更实用的做法是检测“相邻时间差是否超过2倍周期”：
- 设正常周期为
@interval = 10秒，则卡顿条件：collect_time - LAG(collect_time) > @interval * 2 - 丢包常表现为一连串超时（比如连续3次差值 > 30秒），可用
COUNT() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)滚动统计 - 别直接用固定秒数（如“>60秒就报警”），不同设备/场景周期不同，应从配置表查
expected_interval字段动态带入
为什么LAG结果经常是NULL，怎么安全处理
LAG() 对每个分组第一行必然返回 NULL，直接参与减法会导致整行结果为 NULL，进而被 WHERE 过滤掉——你可能因此漏掉首条异常。
关键不是避免NULL，而是显式处理它：
- 用
COALESCE(LAG(collect_time), collect_time)把首行差值压成0（适合只关心突变） - 更合理的是保留NULL并单独判断：
WHERE lag_time IS NULL OR collect_time - lag_time > threshold - 警惕隐式类型转换：若
collect_time是字符串，减法会失败或得0；务必确认是TIMESTAMP或datetime类型
真实查询里怎么把卡顿和丢包标记出来
不要试图一查到底，先生成带差值的基础结果集，再外层筛选或聚合。一次性塞太多逻辑会让执行计划变差，尤其数据量大时。
示例（PostgreSQL/MySQL 8.0+）：
SELECT
device_id,
collect_time,
lag_time,
EXTRACT(EPOCH FROM (collect_time - lag_time)) AS diff_sec,
CASE
WHEN lag_time IS NULL THEN 'first'
WHEN collect_time - lag_time > INTERVAL '20 second' THEN 'stall'
WHEN collect_time - lag_time < INTERVAL '5 second' THEN 'noise' -- 可能时钟漂移
ELSE 'normal'
END AS status
FROM (
SELECT
device_id,
collect_time,
LAG(collect_time) OVER (
PARTITION BY device_id ORDER BY collect_time, log_id
) AS lag_time
FROM sensor_log
WHERE collect_time >= NOW() - INTERVAL '1 hour'
) t;真正麻烦的是“丢包”的间接推断——它依赖稳定周期假设。一旦设备重启、配置变更、NTP校时，周期就不可靠。这类边界情况得靠应用层打标（如日志里带 session_id）来辅助判断，纯SQL撑不住。
