Z-Score是标准化指标，公式为(x−μ)/σ，SQL中不能直接用AVG()和STDDEV()因数据库对标准差默认行为不一（总体vs样本）、NULL处理隐晦且易致错位，需显式选用STDDEV_SAMP()、过滤NULL并统一窗口定义。
什么是Z-Score，为什么不能直接用 AVG() 和 STDDEV() 算？
Z-Score 公式是 (x - μ) / σ，其中 μ 是均值、σ 是标准差。在 SQL 里看似只要套用窗口函数就能算，但关键陷阱在于：不同数据库对 STDDEV() 的默认行为不一致——STDDEV_POP() 计算总体标准差，STDDEV_SAMP() 计算样本标准差（分母为 n−1）。用错会导致 Z-Score 偏离真实分布，尤其在小数据集上偏差明显。
更隐蔽的问题是 NULL 处理：AVG() 和 STDDEV_* 默认忽略 NULL，但如果业务字段本身允许 NULL，且你没提前过滤或标记，计算出的 μ 和 σ 就只基于非 NULL 行，而后续 Z-Score 却可能被应用到含 NULL 的原始行上，导致结果错位。
- 务必确认业务场景需要的是总体还是样本标准差；多数异常检测用
STDDEV_SAMP() - 先用
WHERE value IS NOT NULL显式排除 NULL，避免隐式行为干扰 - 如果字段有业务含义的“空值”（比如 0 表示未上报），别依赖 NULL 过滤，得按业务规则清洗
PostgreSQL / MySQL 8.0+ / BigQuery 中怎么写 Z-Score 窗口表达式？
核心是把 AVG() 和 STDDEV_SAMP() 同时作为窗口函数嵌套进计算，且必须共用完全一致的 PARTITION BY 和 ORDER BY（如果不需要排序，就都不写 ORDER BY）。
以 PostgreSQL 为例，计算每个用户最近 7 天订单金额的 Z-Score：
SELECT
user_id,
order_amount,
(order_amount - AVG(order_amount) OVER (PARTITION BY user_id ORDER BY order_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW))
/ NULLIF(STDDEV_SAMP(order_amount) OVER (PARTITION BY user_id ORDER BY order_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0)
AS z_score
FROM orders
WHERE order_amount IS NOT NULL;-
NULLIF(..., 0)防止标准差为 0 时除零错误，返回 NULL 而非报错 -
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义滑动窗口，确保每行用自己及前 6 行算 μ 和 σ - MySQL 8.0+ 写法几乎一致，但需注意其
STDDEV_SAMP()在窗口中支持较晚（8.0.22+ 更稳） - BigQuery 用
STDDEV_SAMP()同样有效，但若用SAFE_DIVIDE()替代/ NULLIF()更直观
SQL Server 和 Oracle 怎么处理没有 STDDEV_SAMP() 窗口版的情况？
SQL Server 2016+ 支持 STDEV() 窗口函数，但它等价于 STDDEV_SAMP()；Oracle 12c+ 的 STDDEV() 默认也是样本标准差——但文档常不明确，必须验证。
稳妥做法是绕过内置函数，手动实现样本标准差（虽然性能略低，但可控）：
(order_amount - AVG(order_amount) OVER w)
/ SQRT(
AVG(order_amount * order_amount) OVER w
- POWER(AVG(order_amount) OVER w, 2)
) AS z_score
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY order_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW);- 这个公式基于方差恒等式：
VAR_SAMP = E[X²] − E[X]²，再开方得标准差 - 适用于所有支持窗口
AVG()和POWER()的数据库，包括旧版 SQL Server - 注意：当窗口内只有 1 行时，
VAR_SAMP理论上无定义（分母为 0），此公式会返回NaN或报错，需额外CASE WHEN COUNT(*) OVER w > 1 THEN ... ELSE NULL END
Z-Score 异常分数上线前必须检查的三件事
跑出 Z-Score 不等于能直接当异常信号用。真实环境中，以下三点漏掉一个就可能误报满天飞：
- 检查窗口内实际行数：
COUNT(*) OVER (PARTITION BY ...) AS window_size，若大量窗口 size ≤ 2，则 Z-Score 波动极大，应过滤或降权 - 验证分布偏态：Z-Score 假设近似正态，但订单金额、响应时间往往是长尾分布。先用
HISTOGRAM或抽样PERCENTILE_CONT(0.95)看是否右偏严重，偏斜 > 2 时考虑先做 log 变换再算 Z-Score - 确认业务容忍阈值：|Z| > 3 是统计学常见阈值，但电商秒杀场景可能 |Z| > 5 才算异常，而 IoT 设备心跳间隔可能 |Z| > 1 就要告警——阈值必须和业务同学对齐，不能只看数字
窗口函数算 Z-Score 的本质是局部标准化，它依赖窗口内数据的代表性。窗口切得太大失去灵敏度，太小又不稳定，这个平衡点没法靠语法解决，得靠业务场景反复试。
