用SQL窗口函数识别数据序列中的异常跳跃值

先说一个核心判断：用窗口函数做跳跃检测，天花板不是语法，是阈值的动态性。固定值一刀切，容易把正常波动当成异常，或者让真正的异常悄悄溜过去。要解决这个问题，关键是对差值本身再做一层统计分析。

具体怎么落地？下面从最基础的差值计算开始，一步步推演。

用 LAG() 和 LEAD() 计算相邻差值

想识别跳跃值，第一步自然是把当前行跟上一行（或者下一行）之间的数值差算出来。窗口函数 LAG() 是最顺手的选择——它跨行取值时不依赖自连接或子查询，也不会被分组边界干扰。

不过这里有个坑：很多人直接用 LAG(value) 就完了，ORDER BY 忘了写。窗口函数在没有显式排序的情况下，行为是未定义的，结果每次跑可能都不一样，这肯定不行。

实操上要注意几点：

• OVER 子句里必须明确写 ORDER BY timestamp 或者 ORDER BY id，确保序列顺序唯一且稳定
• 差值计算建议用 value - LAG(value) OVER (ORDER BY ts)，而不是 LAG(value) - value，否则正负号容易搞混
• 第一行数据点，LAG() 会返回 NULL，差值也就跟着变成 NULL。后续过滤时记得加 WHERE diff IS NOT NULL

设定动态跳跃阈值：用 PERCENTILE_CONT() 替代固定数值

固定阈值（比如“差值 > 100 就报警”）的问题在于，数据本身波动大时，100 可能稀松平常；数据平缓时，20 就算异常。更稳妥的做法，是基于当前窗口内历史差值的分布来定界。

PostgreSQL、SQL Server 和 Oracle 都提供了 PERCENTILE_CONT(0.95)，可以算出过去 N 行差值的 95% 分位数，作为上限。

具体操作建议：

• 先用 CTE 或子查询算出每行的 diff，再套一层窗口算分位数，比如：PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY diff) OVER (ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)
• ROWS BETWEEN ... 的范围要足够大，覆盖至少 100 个历史点，否则分位数抖动会很厉害
• MySQL 8.0+ 不支持 PERCENTILE_CONT，得改用 APPROX_PERCENTILE（BigQuery）或者临时表加变量来模拟

处理时间非均匀序列：用 INTERVAL 约束窗口范围

传感器数据或日志经常会有采样不均匀的问题——这次更新隔了 5 分钟，下次隔了 2 小时。如果只是按行数滑动窗口（比如 ROWS BETWEEN 19 PRECEDING AND CURRENT ROW），实际时间跨度可能从 10 分钟跳到 3 天，阈值自然就不准了。

解决方案：

• 优先考虑时间范围窗口：ORDER BY ts RANGE BETWEEN INTERVAL '1 HOUR' PRECEDING AND CURRENT ROW
• PostgreSQL 可以直接写 INTERVAL，MySQL 需要转成秒数：RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW
• 注意，RANGE 要求排序字段是数值或日期类型，如果是字符串时间戳（比如 '2023-01-01 12:00'），必须先转成 TIMESTAMP

避免误标连续跳跃中的“正常”中间值

现实中的异常经常是“台阶式”出现的：数值从 10 跳到 105，然后跳到 108、107。单看差值，105 是异常，但 108 和 107 跟它比差值很小，很容易被放过——其实它们都是异常状态的延续，不是回归正常。

要解决这个问题，不能只看单步变化，而是要看当前值是否脱离了“近期合理区间”。可以用 MIN()/MAX() 这类窗口聚合来构建动态基线：

• 计算最近 20 行的 A VG(value) 和 STDDEV(value)，把超出 A VG ± 3×STDDEV 的点标为异常
• 或者更鲁棒的做法：用 PERCENTILE_CONT(0.25) 和 PERCENTILE_CONT(0.75) 算 IQR，异常区间设为 [Q1 - 1.5×IQR, Q3 + 1.5×IQR]
• 关键点：这类基线必须用 ROWS BETWEEN 19 PRECEDING AND CURRENT ROW，不能用 RANGE，否则时间空档会导致窗口内行数不足，基线失真

连续跳跃最难的不是检测，而是定义“何时算恢复”。这个边界往往得结合业务——比如温度传感器连续 5 分钟高于阈值才触发告警，而不是单点超标就报。