sql server中stdev()窗口函数遇null即返回null，需用where过滤或coalesce处理；滚动窗口（rows between）比全局计算更灵敏；lag()结合滚动标准差可精准检测阶跃异常；性能优化需索引支持与分步计算。
STDEV() OVER 不能直接用，得配 WHERE 过滤 NULL
SQL Server 的 STDEV() 作为窗口函数时，只要参与计算的列中存在任意一个 NULL，整个窗口结果就返回 NULL——不是跳过，也不是报错，而是静默失效。比如查用户订单金额波动：STDEV(amount) OVER (PARTITION BY user_id)，一旦某用户有未支付订单（amount IS NULL），该用户的整行标准差都是 NULL，后续 WHERE amount > AVG(amount) + 3 * STDEV(amount) 就全失效。
必须显式过滤：WHERE amount IS NOT NULL 放在子查询或 CTE 中；若业务上 NULL 确实代表 0，则改用 COALESCE(amount, 0) 再套窗口函数。
-
STDEV()和STDEVP()都有窗口用法，但STDEV()是样本标准差（分母n-1），更适用于大多数分析场景 - 别在
SELECT里直接写STDEV(col) OVER () > 100做布尔判断——SQL Server 不支持窗口函数直接用于WHERE，得先放进 CTE 或子查询 - 排序字段不唯一会导致窗口帧行为异常，比如按
order_date排序但多笔同天订单，应补ORDER BY order_date, order_id
用 ROWS BETWEEN 定义滚动窗口，避免全局偏差失真
全局标准差对局部突变不敏感。比如某用户过去 100 笔订单平均 200 元、标准差 50 元，第 101 笔突然 2000 元，用全局 STDEV() 很难抓出——因为被稀释了。正确做法是定义滚动窗口：
STDEV(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
这表示“每个用户按时间排序，只看最近 30 笔订单的标准差”，能及时响应行为变化。注意：
-
ROWS BETWEEN比RANGE BETWEEN更稳定，后者对重复排序值会合并行，导致窗口大小浮动 - 首 29 行因前序不足，
STDEV()返回NULL，可用COALESCE(STDEV(...) OVER (...), 0)填充，但填充值要和业务逻辑一致 - 滚动窗口不能跨分区，
PARTITION BY user_id后，每个用户的窗口完全独立，不会混入他人数据
结合 LAG() 检测阶跃型异常，比标准差更准
标准差适合检测“偏离均值”的离群点，但对“前后突变”类异常（如日活从 5000 → 15000）反应迟钝。LAG() 更直接：
ABS(amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date)) > 3 * COALESCE(STDEV(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 0)
这个组合既考虑了历史波动基线（分母用滚动标准差），又聚焦相邻变化量。关键细节：
-
LAG()在首行返回NULL，直接参与减法会导致整行NULL，所以必须用COALESCE(LAG(...), amount)把首行差值设为 0 - 阈值别硬写死 3 倍，先跑
PERCENTILE_CONT(0.95) OVER (...)看历史差值分布，再定倍数 - 如果排序字段（如
order_date）有大量重复，务必加第二排序键（如order_id），否则LAG()取值顺序不可控
STDEV() 窗口性能陷阱：大数据量下慎用全排序
当表超千万行，又没索引支撑时，STDEV() OVER (ORDER BY ...) 会强制全表排序，即使你只关心 top 10 异常。比方说想查“单笔金额 > 均值 + 3 倍滚动标准差”的订单，直接写：
WHERE amount > AVG(amount) OVER (...) + 3 * STDEV(amount) OVER (...)
——SQL Server 会为每一行都算一遍窗口，开销爆炸。更优路径是两步走：
- 先用子查询算出滚动标准差阈值：
(SELECT AVG(amount) + 3 * STDEV(amount) FROM t WHERE ...)，配合WHERE条件缩小数据集 - 或建覆盖索引：
CREATE INDEX IX_orders_user_date_amount ON orders(user_id, order_date) INCLUDE (amount)，让窗口排序走索引 - 别用
ROW_NUMBER()手动模拟标准差——它必须全排序，而STDEV()窗口函数底层有优化，实际快得多
最容易被忽略的是：没确认 amount 列是否已清洗（比如含单位混用、负值、测试数据），也没验证窗口范围是否匹配业务周期（比如用 30 天滚动窗去分析季度促销数据，天然失真）。
