sql中识别离群值必须用窗口函数预计算统计基准、条件表达式标记、子查询/cte过滤三步组合，因avg()等聚合函数无判断逻辑，且窗口函数在select阶段才执行，不能在where中直接引用。
直接用 AVG() 或 SUM() 等聚合函数本身无法识别离群值——它们只负责汇总，不带判断逻辑。真正在 SQL 中识别离群值，必须靠窗口函数预计算统计基准 + 条件表达式标记 + 子查询/CTE 过滤三步组合。
为什么不能在 WHERE 里直接写 AVG() OVER？
SQL 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而 AVG() OVER 属于 SELECT 阶段才计算。你在 WHERE 里引用它，数据库会直接报错：window functions are not allowed in WHERE。
- 常见错误写法：
WHERE amount > AVG(amount) OVER (PARTITION BY user_id) + 2 * STDDEV(amount) OVER (PARTITION BY user_id) - 正确路径只有一条：先把窗口结果“带下来”，再外层过滤
- 推荐用 CTE，语义清晰且方便复用中间列（比如
avg_amt、std_amt）
PERCENTILE_CONT() 比 AVG() + STDDEV() 更抗噪
当数据有严重偏态（比如大量 0 值 + 少量极大值），标准差会被拉高，导致阈值变宽、漏判异常。此时用四分位距（IQR）法更稳：
- 必须写成
PERCENTILE_CONT(0.25) OVER (PARTITION BY group_col ORDER BY value)，漏掉ORDER BY或PARTITION BY会出错或结果错乱 - PostgreSQL / SQL Server / Oracle / BigQuery 原生支持；MySQL 8.0+ 需用
ROW_NUMBER()+COUNT(*)模拟，但精度可控、性能尚可 - IQR 下界 =
q1 - 1.5 * (q3 - q1)，上界同理；别用固定百分位（如PERCENTILE_CONT(0.05)），鲁棒性差
小样本和 NULL 怎么不误删正常数据？
标准差在单行或两行时无意义，多数数据库（如 PostgreSQL）对 STDDEV_SAMP() 返回 NULL，若不做防护，整行会在 WHERE 中被静默丢弃——不是因为异常，而是因为算不出来。
- 加前置保护：
COUNT(*) OVER (PARTITION BY group_col) >= 3，确保每组至少 3 行才参与判断 - 用
COALESCE(std_amt, 0)防止NULL导致比较失效 - 用
NULLIF(std_amt, 0)或COALESCE(std_amt, 1e-9)避免除零（全等值时std_amt = 0） - 首行
LAG()返回NULL也一样：得用COALESCE(LAG(value) OVER (ORDER BY ts), value)填充
真正容易被忽略的点是：离群值识别永远依赖上下文。全局均值对分组数据无效，固定阈值对业务分布失真，而 PERCENTILE_CONT 若没配对 ORDER BY 和 PARTITION BY，结果就完全不可信——它不报错，但返回的是垃圾。
