avg over 不能实时监控，因其是静态窗口函数，仅在查询时计算历史数据滑动平均，不感知新数据；实时需依赖定时查询、流引擎或轮询等外部机制驱动反复执行。
AVG OVER 为什么不能直接“实时监控”
AVG OVER 是窗口函数，它只在查询执行那一刻计算历史数据的滑动平均，本身不感知新数据到达、不触发通知、不持续刷新。所谓“实时监控”，实际是靠外部机制（如定时查询、流式引擎、应用层轮询）驱动 AVG OVER 反复计算。如果你期望写一条 SQL 就自动告警或写入告警表，那它做不到——必须搭配调度或流处理。
用 AVG OVER 计算温度偏离度的典型写法
假设设备温度表 sensor_data 有字段：device_id、temp_c、ts（时间戳）。你想对每个设备，基于最近 10 条记录算平均温度，再求当前记录偏离多少：
SELECT
device_id,
temp_c,
ts,
temp_c - AVG(temp_c) OVER (
PARTITION BY device_id
ORDER BY ts
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
) AS deviation
FROM sensor_data
WHERE ts >= NOW() - INTERVAL '5 minutes';
注意几个关键点：
-
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW表示“含自己共 10 行”，不是“过去 10 分钟”——时间范围需靠WHERE预过滤，否则窗口可能跨太长时间，拉低响应性 -
PARTITION BY device_id必须加，否则所有设备混在一起算平均，偏离值无意义 - 若
ts有重复或乱序，ORDER BY ts可能导致窗口错位；生产环境建议加ORDER BY ts, id消除歧义
常见错误：用 RANGE 代替 ROWS 导致结果漂移
有人会写 RANGE BETWEEN INTERVAL '1 minute' PRECEDING AND CURRENT ROW 想按时间窗口算均值。问题在于：
- PostgreSQL 支持
RANGE时间间隔，但 MySQL 不支持，SQL Server 仅支持数值列 - 如果某设备在 1 分钟内没上报数据，
RANGE窗口可能只包含 1 行（甚至 0 行），AVG结果不稳定，偏离度忽大忽小 -
ROWS能保证参与计算的样本数可控，更适合监控场景中“最近 N 次”的语义
真正落地时绕不开的三个配套动作
单靠 AVG OVER 输出偏离值只是第一步。要形成监控闭环，还得做：
- 把查询封装成视图（如
dev_temp_deviation_v），方便上层工具（Grafana、自研看板）直接查，避免每次拼复杂窗口逻辑 - 加
WHERE deviation > 5 OR deviation 过滤出异常点——别指望前端全量拉取再判断，数据库层过滤更省带宽和内存 - 如果需要存告警记录，得用
INSERT INTO alert_log SELECT ... FROM (...) WHERE deviation NOT BETWEEN -3 AND 3，且确保该语句在事务中执行，避免重复插入
最易被忽略的是时间精度：数据库时区、应用上报时区、NOW() 和 CURRENT_TIMESTAMP 的行为差异，会导致“最近 5 分钟”在不同环节对不上。统一用 UTC 存储 + 显式时区转换，比后期排查强得多。
