突增异常检测应优先用LAG()计算相对变化率并结合滑动窗口均值基线，严格按序号或复合时间戳排序，去重后在CTE中计算再过滤，避免性能与语义错误。
用 LAG() 和百分比变化判断突增是否异常
传感器数据突增的本质是当前值相对于前一时刻的剧烈偏离，LAG() 是最直接的窗口函数选择。它能安全获取上一行的值，避免自连接或子查询带来的性能和时序错位风险。
关键不是看绝对差值，而是相对变化率——比如温度从 25℃ 跳到 38℃ 可能正常，但电流从 0.2A 跳到 1.6A 就要报警。计算公式为：(current_value - LAG(current_value)) / NULLIF(LAG(current_value), 0)，其中 NULLIF 防止除零错误。
- 必须按时间戳严格排序：
ORDER BY timestamp ASC，否则LAG()返回的是逻辑上“前一条”，不一定是时间上前一个采样点 - 对同一设备多通道数据，务必加
PARTITION BY sensor_id，否则跨设备混用历史值会误报 - 浮点精度问题：传感器原始值常为
REAL或DOUBLE PRECISION，建议用ROUND(..., 4)再比较，避免因精度误差触发边界条件
用 AVG() OVER(... ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) 动态基线过滤毛刺
单纯用前后两点比对容易把高频噪声当成突增。更稳健的做法是构建一个滑动窗口均值作为“短期基线”，再判断当前值是否显著偏离该基线。
例如取最近 5 条（含当前）的均值，再设阈值为基线的 2.5 倍：value > 2.5 * AVG(value) OVER (PARTITION BY sensor_id ORDER BY timestamp ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)。
-
ROWS BETWEEN ...比RANGE更可靠：传感器采样可能不等间隔，RANGE会因时间跨度不同导致窗口行数波动 - 窗口太小（如 2 行）抗噪弱；太大（如 20 行）响应慢，5–10 行是常见折中，具体需结合采样频率调整
- 首次几行因窗口不足会返回
NULL，可用COUNT(*) OVER (...)判断有效窗口长度，只对满窗行做判断
处理时序断点与重复时间戳的典型陷阱
真实传感器数据常有乱序写入、重传、或同一毫秒内多条记录。这时 ORDER BY timestamp 可能无法唯一确定顺序，导致 LAG() 返回错误的“前值”。
- 优先用带序列号的字段（如
seq_no）代替纯时间戳排序：ORDER BY seq_no，若无则至少加二级排序：ORDER BY timestamp, received_at - 用
ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY timestamp, id)标记逻辑序号，再用该序号做差值检测，比依赖时间精度更鲁棒 - 重复时间戳必须去重：先用
ROW_NUMBER() OVER (PARTITION BY sensor_id, timestamp ORDER BY created_at DESC)留最新一条，否则窗口函数会在相同时间点内随机选行
在 WHERE 子句中引用窗口函数结果的写法
SQL 标准不允许在 WHERE 中直接用窗口函数（会报错 invalid reference to FROM-clause entry），必须用子查询或 CTE 包裹。
正确写法是外层筛选：
SELECT * FROM (
SELECT sensor_id, timestamp, value,
(value - LAG(value) OVER (PARTITION BY sensor_id ORDER BY timestamp)) / NULLIF(LAG(value) OVER (PARTITION BY sensor_id ORDER BY timestamp), 0) AS pct_change
FROM sensor_readings
) t
WHERE ABS(pct_change) > 1.5;- 别在子查询里算两次
LAG()——虽然语义等价，但多数数据库（PostgreSQL、Snowflake）不会自动复用，会重复计算，拖慢性能 - 如果数据量大，先用
WHERE timestamp > NOW() - INTERVAL '1 hour'限定范围再开窗，避免全表扫描 - MySQL 8.0+ 支持 CTE，但早期版本需用派生表；SQLite 不支持窗口函数，得换思路（如自连接 + LIMIT 1）
真正难的不是写出窗口函数，而是确认你定义的“异常”在物理意义上是否成立——比如电流突增可能是开关机，也可能是短路，需要结合设备状态码、上下文事件日志交叉验证，窗口函数只是第一道筛子。
