必须用窗口函数先计算组内均值和标准差再过滤，不可在WHERE中直接调用聚合函数；正确写法是AVG(value) OVER (PARTITION BY user_id)配合COUNT(*)>2等前置校验，避免NULL和小样本误判。
用 AVG() 和 STDDEV() 窗口函数实时计算组内基准值
直接在 WHERE 子句里调用聚合函数会报错，必须先用窗口函数把每行对应的组平均值、标准差“带下来”。常见错误是写成 WHERE value > AVG(value) + 2 * STDDEV(value) —— 这语法不合法，且没指定分组粒度。
正确做法是：用 OVER (PARTITION BY group_col) 明确按业务维度（如用户ID、日期、设备类型）分别算均值和标准差。若漏掉 PARTITION BY，整张表只算一个全局值，失去“组内异常”的意义。
-
AVG(value) OVER (PARTITION BY user_id)给每个用户的每条记录附上该用户的平均值 -
STDDEV(value) OVER (PARTITION BY user_id)同理附上该用户的样本标准差（PostgreSQL/Oracle 默认；MySQL 8.0+ 用STDDEV_SAMP()） - 注意：SQL Server 的等价函数是
STDEV()，且对空值敏感——若某组全为 NULL，STDEV()返回 NULL，后续比较会失效，需加COALESCE(..., 0)
过滤逻辑要避开 NULL 和极小样本导致的误判
标准差在单条或两条记录时无统计意义，直接套用 ±2σ 会把所有值都标为异常。更糟的是，当某组只有 1 行时，多数数据库返回 NULL（如 PostgreSQL），导致整行被 WHERE 条件过滤掉——不是因为异常，而是因为无法计算。
- 加前置判断：
COUNT(*) OVER (PARTITION BY user_id) >= 3，确保每组至少 3 条才参与异常计算 - 用
ABS(value - avg_val) > 2 * COALESCE(std_val, 0)避免 NULL 传播；但要注意：若std_val为 0（所有值相同），则2 * 0 = 0，此时只要value != avg_val就触发异常——这合理，因为确实出现了偏离 - 某些场景下，用
STDDEV_POP()（总体标准差）比STDDEV_SAMP()（样本标准差）更稳妥，尤其当分组数据就是全部总体时（如某天全部订单）
性能关键：避免重复计算与索引失效
窗口函数本身不走索引，但嵌套太深会拖慢执行。典型反模式是写两层子查询：外层再对窗口结果做 WHERE。其实可以一步到位：
SELECT *
FROM (
SELECT *,
AVG(value) OVER (PARTITION BY user_id) AS avg_val,
STDDEV_SAMP(value) OVER (PARTITION BY user_id) AS std_val,
COUNT(*) OVER (PARTITION BY user_id) AS cnt
FROM events
) t
WHERE cnt >= 3
AND ABS(value - avg_val) > 2 * COALESCE(std_val, 0);
如果 user_id 和 value 是高频筛选字段，建议在物理表上建组合索引：CREATE INDEX idx_user_value ON events(user_id, value); —— 虽不能加速窗口计算，但能加快初始扫描和 PARTITION BY 分组排序。
- 别在窗口函数里用复杂表达式（如
LOG(value + 1)）替代原始列，会导致优化器放弃估算行数 - MySQL 8.0 对
STDDEV_SAMP()窗口函数支持较晚，低于 8.0.13 版本会报错，务必查版本
为什么不用 PERCENT_RANK() 或 NTILE() 替代？
百分位或分桶法（比如取 top 1%）看起来更“鲁棒”，但它不反映偏离程度，只反映相对位置。例如一组值 [1, 1, 1, 1, 100]，100 是唯一最大值，PERCENT_RANK() 接近 1，会被筛出；但若另一组是 [10, 20, 30, 40, 100]，100 的百分位仍是 1，却未必异常——它只是线性增长的终点。而 ±2σ 能捕捉“偏离集中趋势的程度”，更适合检测突刺、录入错误、传感器漂移这类问题。
不过要注意：正态分布假设在真实数据中常不成立。如果直方图明显右偏，用 LOG(value) 预处理后再算 σ，比硬套原值更可靠；这个变换步骤必须放在窗口函数内部，否则分组基准就乱了。
