开窗函数通过AVG()和STDDEV()配合OVER(PARTITION BY)动态计算分组均值与标准差，再用“均值±2倍标准差”判定离群值，确保每行基于其所属分组的统计基准实时识别异常。
开窗函数怎么识别离群值：用 AVG() 和 STDDEV() 搭配 OVER()
离群值不是靠肉眼判断的，得量化。常见做法是“均值 ± 2 倍标准差”，这个范围外的值就标为异常。关键在于：不能先算全表统计再 JOIN，那样会丢失窗口上下文；必须用开窗函数在每行动态计算当前分组的基准。
比如按 user_id 分组检测单次订单金额是否异常：
SELECT
order_id,
amount,
AVG(amount) OVER (PARTITION BY user_id) AS avg_amt,
STDDEV(amount) OVER (PARTITION BY user_id) AS std_amt,
CASE
WHEN amount > AVG(amount) OVER (PARTITION BY user_id) + 2 * STDDEV(amount) OVER (PARTITION BY user_id)
OR amount < AVG(amount) OVER (PARTITION BY user_id) - 2 * STDDEV(amount) OVER (PARTITION BY user_id)
THEN 1
ELSE 0
END AS is_outlier
FROM orders;注意：STDDEV() 在 PostgreSQL / Oracle 中默认是样本标准差（STDDEV_SAMP），MySQL 8.0+ 才支持；若用 MySQL 5.7 或早期版本，得换用 STD() 或手动算方差。
为什么不能直接用 WHERE 筛开窗结果：子查询或 CTE 是必经之路
SQL 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而开窗函数在 SELECT 阶段才计算，所以你不能在同一个层级的 WHERE 里引用 is_outlier 列——会报错 column "is_outlier" does not exist。
正确做法只有两个：
- 把开窗逻辑包进子查询，外层再
WHERE is_outlier = 1 - 用 CTE（
WITH子句），语义更清晰，也方便复用中间统计值
CTE 写法示例（推荐）：
WITH stats AS (
SELECT
order_id,
amount,
AVG(amount) OVER (PARTITION BY user_id) AS avg_amt,
STDDEV(amount) OVER (PARTITION BY user_id) AS std_amt
FROM orders
)
SELECT order_id, amount
FROM stats
WHERE amount > avg_amt + 2 * std_amt
OR amount < avg_amt - 2 * std_amt;
PERCENT_RANK() 和 NTILE() 适合什么场景的离群筛选
当数据分布严重偏斜（比如大量零值 + 少量极大值），标准差法容易被极端值反向拉高阈值，导致漏判。这时百分位数法更鲁棒。
PERCENT_RANK() 返回 0–1 区间内相对排序位置，NTILE(100) 把数据等分为 100 组，两者都能定位“最顶端 1%”这类业务定义明确的异常：
- 用
PERCENT_RANK() OVER (ORDER BY amount DESC) 找金额最高的 1% - 用
NTILE(100) OVER (ORDER BY amount DESC) = 1效果类似，但更易理解 - 注意：两者都默认按整个结果集排序；如需分组内找离群，必须加
PARTITION BY
例如按城市找高价订单中的前 1%：
SELECT city, amount
FROM (
SELECT
city,
amount,
PERCENT_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS prk
FROM orders
) t
WHERE prk < 0.01;性能和 NULL 处理的坑：别让 STDDEV() 返回 NULL 搞垮整行判断
如果某个分组只有 1 行，STDDEV() 在多数数据库中返回 NULL（因为样本标准差至少需要 2 个值），这会导致整个 CASE 表达式结果为 NULL，而不是 0——后续筛选可能意外丢掉该行。
应对方式很实在：
- 加
COUNT(*) OVER (PARTITION BY ...)判断分组大小，COUNT = 1时跳过标准差逻辑 - 用
COALESCE(STDDEV(...), 0)强制补 0，但要清楚补 0 后的阈值含义已变（相当于只用均值判断） - PostgreSQL 可用
STDDEV_POP()（总体标准差），它对单值返回 0，但语义上不如样本标准差常用
真正容易被忽略的是：不同数据库对空组、全 NULL 列的开窗聚合行为不一致。上线前务必用真实小数据集验证边界 case，尤其关注只有 1 条或全是 NULL 的分组输出。
