在做SQL数据分析时，原始表里经常混着少数极端异常值，比如订单金额录成了百万级、用户在线时长算出负数。这类数据若直接参与聚合，均值和方差都会被严重扭曲，导致报表结论失真。只靠人工盯日志或写死固定阈值来删数，既难覆盖不同业务线的分布差异，也会把正常但偏高的记录误杀。
窗口函数提供了在不折叠行的前提下做分组统计的能力。我们可以为每一行标注它所在分组的集中趋势与离散程度，再基于统计距离判断该行是否异常。这种方式比先GROUP BY再JOIN回原表更简洁，也更容易下沉到视图里复用。
一、为什么需要偏离度而不是固定阈值
固定阈值最大的问题是脱离数据本身分布。例如A地区日均销售额两万，B地区日均二十万，统一用大于五万就删，会误伤B地区大量正常单。偏离度思想是把每行值和自己所属组的均值做比较，用标准差做尺度，得到类似（值减均值）除以标准差的分数，分数过大才视为异常。
这种思路对分组内波动敏感，又能自动适应各组量级。配合窗口函数，我们无需事先知道每个组该用什么数当门槛，SQL自己算出来。对于后续要保留明细又要排雷的场景，偏离度过滤几乎是必选方案。
1.1 常见偏离度公式
最常用的是标准分数z = (x - mean) / stddev。当绝对值大于2或3时，在正常分布下属于小概率事件。若数据严重偏态，也可改用中位数与绝对中位差，但窗口函数对中位差支持不如均值标准差直观，因此本文以均值标准差为主。
在SQL里，AVG(x) OVER(PARTITION BY 组) 给出组均值，STDDEV(x) OVER(PARTITION BY 组) 给出组标准差，两者都不需要GROUP BY聚合整表，每一行都能拿到对应值，非常适合做行级标注。
二、基础检测SQL写法
假设有销售表 sales(order_id, region, amount)，我们想按地区检测金额异常。下面的查询给每行加上该地区均值、标准差和偏离度，并标记是否异常。
SELECT
order_id,
region,
amount,
AVG(amount) OVER(PARTITION BY region) AS region_avg,
STDDEV(amount) OVER(PARTITION BY region) AS region_std,
(amount - AVG(amount) OVER(PARTITION BY region))
/ NULLIF(STDDEV(amount) OVER(PARTITION BY region), 0) AS z_score,
CASE
WHEN ABS(
(amount - AVG(amount) OVER(PARTITION BY region))
/ NULLIF(STDDEV(amount) OVER(PARTITION BY region), 0)
) > 2 THEN '异常'
ELSE '正常'
END AS flag
FROM sales;
这里用 NULLIF 避免标准差为0时除以零报错，比如某地区只有一笔订单。z_score 就是偏离度，绝对值大于2判为异常。注意 STDDEV 在部分数据库返回样本标准差，若需总体标准差可用 STDDEV_POP，按业务约定选择即可。
上述语句只是检测，不删数据，方便先抽样核对被标异常的行是否真有问题。确认逻辑后，再把它套一层子查询做剔除。
2.1 剔除异常值的最终查询
把上面查询作为子查询，只保留正常标记的行，就得到清洗后的明细。
SELECT order_id, region, amount
FROM (
SELECT
order_id,
region,
amount,
CASE
WHEN ABS(
(amount - AVG(amount) OVER(PARTITION BY region))
/ NULLIF(STDDEV(amount) OVER(PARTITION BY region), 0)
) > 2 THEN '异常'
ELSE '正常'
END AS flag
FROM sales
) t
WHERE flag = '正常';
这样写的好处是原表不动，异常行可在审计表里另行存档。如果数据库支持CTE，用 WITH 子句可读性更好，但逻辑完全一致。
当数据量很大时，窗口函数会按分区做排序和计算，建议 region 字段有索引或统计信息较新，以免计划走偏。若只需聚合结果不要明细，也可先算出各组均值标准差再关联，但代码会啰嗦不少。
三、多维度分组与边界处理
真实业务常要按地区加品类双维度检测，只需把 PARTITION BY 后面加上多列。例如 PARTITION BY region, category 让偏离度只在同地区同品类中比较，避免跨品类量级差造成误判。
SELECT order_id, region, category, amount, (amount - AVG(amount) OVER(PARTITION BY region, category)) / NULLIF(STDDEV(amount) OVER(PARTITION BY region, category), 0) AS z FROM sales;
另一个边界是极端值本身会拉高均值和标准差，让偏离度变小漏检。对此可先做一次粗筛，比如用百分位函数 PERCENTILE_CONT 找上下限，或迭代剔除：第一轮标异常删掉后重算第二轮。SQL里可用递归CTE或临时表分步做，虽稍复杂但能提升鲁棒性。
还有 NULL 值处理，窗口函数默认忽略 NULL，若 amount 为空则那行 z_score 也为空，不会被标异常，一般符合预期，但要在下游过滤时显式排除 NULL 以免混入统计。
3.1 与HAVING后聚合的对比
有人习惯先 GROUP BY region 算好均值标准差，再 JOIN 回原表过滤。那种写法在只按单维度且不需行级复用时还行，但每加一个维度就要改两处，且难以在同一 SELECT 里直接看偏离度。窗口函数把计算和明细合在一层，逻辑集中、易测试。
| 方案 | 代码复杂度 | 是否保留明细 | 多维度扩展 |
|---|---|---|---|
| GROUP BY后JOIN | 中 | 是 | 麻烦 |
| 窗口函数 | 低 | 是 | 简单 |
从表里能看出，窗口函数在可维护性上优势明显，也是现代SQL分析的主流做法。
四、在报表中直接应用
如果报表工具允许写SQL视图，可把带 flag 的查询存成视图 v_sales_clean，前端直接 WHERE flag='正常' 拿数。分析人员也能临时改阈值，比如把2改成3以容忍更高波动，不需要改底层表。
对于定时任务，可先把异常订单插入专门表，再让清洗查询排除这些 id，形成闭环。这样既剔除了极端异常值，又保留了追溯能力，不会因误删导致复盘无门。
整体来看，用窗口函数检测偏离度剔异常，核心就是分区算统计、行级算分数、按阈值过滤三步。它把统计思维和SQL表达自然结合，是数据分析岗应当熟练掌握的基础技巧。