在数据处理过程中，异常值会干扰统计结果的有效性，比如用户消费金额、设备传感器读数等场景中，少量极端值会让平均值失去参考意义。通过SQL窗口函数计算分组内的平均值和标准差，再基于正态分布的3σ原则过滤异常值，是直接在数据库层面完成数据清洗的高效方案。
核心原理说明
正态分布中，约99.7%的数据会落在平均值加减3倍标准差的范围内，超出这个范围的数据可以判定为异常值。窗口函数可以在不改变原表行数的前提下，为每个分组计算聚合指标，非常适合这种需要同时保留原始数据和分组统计结果的场景。
实现异常值筛选的核心步骤分为三步：
- 使用窗口函数按指定分组计算平均值和标准差
- 定义异常值判断阈值，通常取平均值±3倍标准差
- 过滤出不在阈值范围内的异常数据
基础实现示例
假设我们有一张用户消费记录表user_consume，包含用户IDuser_id、消费金额amount、消费时间consume_time字段，需要筛选出每个用户消费记录中的异常金额。
MySQL 8.0+ 实现代码
-- 计算每个用户的平均消费和标准差，筛选异常值
WITH consume_stats AS (
SELECT
user_id,
amount,
-- 窗口函数计算分组平均值
AVG(amount) OVER (PARTITION BY user_id) AS avg_amount,
-- 窗口函数计算分组标准差
STDDEV(amount) OVER (PARTITION BY user_id) AS std_amount
FROM user_consume
)
SELECT
user_id,
amount,
avg_amount,
std_amount
FROM consume_stats
-- 判断是否为异常值：超出平均值±3倍标准差
WHERE amount < avg_amount - 3 * std_amount
OR amount > avg_amount + 3 * std_amount;
PostgreSQL 实现代码
-- PostgreSQL中标准差函数为stddev，逻辑与MySQL一致
WITH consume_stats AS (
SELECT
user_id,
amount,
AVG(amount) OVER (PARTITION BY user_id) AS avg_amount,
STDDEV(amount) OVER (PARTITION BY user_id) AS std_amount
FROM user_consume
)
SELECT
user_id,
amount,
avg_amount,
std_amount
FROM consume_stats
WHERE amount < avg_amount - 3 * std_amount
OR amount > avg_amount + 3 * std_amount;
适配低版本数据库的方案
如果数据库不支持窗口函数，比如MySQL 5.7及以下版本，可以通过子查询先分组计算统计值，再关联原表实现相同效果：
-- 低版本MySQL实现异常值筛选
SELECT
t1.user_id,
t1.amount,
t2.avg_amount,
t2.std_amount
FROM user_consume t1
-- 关联分组统计结果
JOIN (
SELECT
user_id,
AVG(amount) AS avg_amount,
STD(amount) AS std_amount
FROM user_consume
GROUP BY user_id
) t2 ON t1.user_id = t2.user_id
-- 过滤异常值
WHERE t1.amount < t2.avg_amount - 3 * t2.std_amount
OR t1.amount > t2.avg_amount + 3 * t2.std_amount;
注意事项
- 如果分组内数据量小于2条，标准差计算结果为NULL，需要额外处理避免过滤失效，可以在WHERE条件中增加
std_amount IS NOT NULL的判断 - 3σ原则仅适用于近似正态分布的数据，如果数据分布偏态严重，可以调整阈值倍数，比如使用2倍标准差作为判断标准
- 窗口函数的计算会消耗一定数据库资源，如果数据量极大，建议先对数据进行采样验证，再全量执行
扩展应用场景
除了单字段异常值筛选，还可以结合多个窗口函数实现更复杂的逻辑，比如同时按用户和月份分组，筛选每个月内的异常消费；或者先计算移动平均值和移动标准差，筛选时间序列数据中的异常波动点。
-- 按用户和月份分组筛选异常消费
WITH monthly_stats AS (
SELECT
user_id,
DATE_FORMAT(consume_time, '%Y-%m') AS consume_month,
amount,
AVG(amount) OVER (PARTITION BY user_id, DATE_FORMAT(consume_time, '%Y-%m')) AS month_avg,
STDDEV(amount) OVER (PARTITION BY user_id, DATE_FORMAT(consume_time, '%Y-%m')) AS month_std
FROM user_consume
)
SELECT
user_id,
consume_month,
amount,
month_avg,
month_std
FROM monthly_stats
WHERE amount < month_avg - 3 * month_std
OR amount > month_avg + 3 * month_std;