用SQL聚合函数检测数据集中的高偏离度记录

先说说基本判断：想用SQL找数据集中那些“不太对劲”的记录，别指望靠着GROUP BY加HA VING一把梭。那俩兄弟是管“组”的，不是管“行”的。真要给每一行数据打个标签，看它到底有多离谱，那就得用窗口函数把统计量“带下来”，然后再用WHERE来过滤。

不能靠 GROUP BY + HA VING 直接筛出偏离度高的单条记录——它只管“组”，不管“行”。真要标记每行有多离谱，必须用窗口函数把统计量“带下来”，再用 WHERE 过滤。

用 PERCENT_RANK() 快速标出两端离谱的行

PERCENT_RANK() 返回 0 到 1 之间的相对排序位置，天生就是用来识别分布两端的：值越靠近 0 或者 1，偏离度就越高。相比固定的 ±3σ 标准，它对业务实际分布更友好，尤其当数据明显偏态的时候，效果更明显。

• 写法必须带 OVER (ORDER BY value)，漏掉 OVER 会报错
• 重复值会共享同一百分位——如果字段里大量是 0 或 NULL，PERCENT_RANK() = 0 可能覆盖几百行，误删正常集群
• 别和 CUME_DIST() 混用：CUME_DIST() 是“≤当前值的比例”，尾部累积效应强，右偏数据下容易高估异常比例
• 典型用法：WHERE PERCENT_RANK() OVER (ORDER BY amount) NOT BETWEEN 0.02 AND 0.98

用 PERCENTILE_CONT() 算 IQR 阈值做稳健过滤

IQR（四分位距）法比均值±标准差更抗噪，尤其适合含极端值或偏态分布的数据。核心是先算每组 q1、q3，再推导上下界。

• 必须写成 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY group_id)（PostgreSQL/Oracle）或 PERCENTILE_CONT(0.25) OVER (PARTITION BY group_id ORDER BY value)（SQL Server/BigQuery），具体看数据库文档
• 只写 ORDER BY value 不加 PARTITION BY，会做全局计算，不是按组独立算
• 阈值公式：上界 = q3 + 1.5 * (q3 - q1)，下界 = q1 - 1.5 * (q3 - q1)
• 务必加样本量保护：COUNT(*) OVER (PARTITION BY group_id) >= 5，否则分位数易失真

用 LAG()/LEAD() 检测相邻跳变型异常

这不是统计偏离，而是识别“前后突变”——比如日活从 1200 → 4500 → 1300，中间那条就是典型跳变点。适用于时间序列或有序业务流。

• 首行 LAG() 返回 NULL，直接参与 ABS(value - LAG(value)) 计算会导致整行被过滤，要用 COALESCE(LAG(value) OVER (ORDER BY ts), value) 填充
• 排序字段（如 ts）必须唯一；如有重复时间戳，得加第二排序键：ORDER BY ts, id
• 阈值别拍脑袋定：先跑一次 PERCENTILE_CONT(0.95) OVER (ORDER BY ABS(value - LAG(value))) 看历史差值分布
• 注意：该方法对“缓慢漂移”不敏感，只抓阶跃式变化

真正容易被忽略的是：所有这些偏离度计算，都默认你已经做过基础清洗——比如 WHERE value IS NOT NULL AND value > 0。没这一步，PERCENTILE_CONT 可能被一堆 0 值拉偏，LAG 在脏时间戳上完全失效。偏离度不是万能筛子，它是放大镜，前提是底片本身没污损。