如何通过SQL子查询进行异常数据检测

优先用 exists 而非 in 做存在性判断，因 in 遇 null 会整行过滤，exists 不受 null 影响且语义清晰；大表关联时 exists 执行更可控，in 可能生成临时表拖慢性能。

如何通过sql子查询进行异常数据检测_基准对比

WHERE 子查询里用 EXISTS 还是 IN？看数据量和 NULL

EXISTS 和 IN 都能做基准对比，但行为完全不同。用 IN 时，如果子查询返回 NULL，整行直接被过滤掉（哪怕其他条件都满足），这是最常踩的坑。比如查“订单表中客户ID不在客户主表里的异常记录”，写成 WHERE customer_id NOT IN (SELECT id FROM customers)，只要 customers.id 里有一个 NULL，结果就是空——不是没异常，是 SQL 自己失效了。

优先用 EXISTS 做存在性判断，它不care NULL，语义也更清晰
NOT EXISTS 比 NOT IN 更安全，尤其在子查询涉及左连接或可能含空值的字段时
如果必须用 IN，显式排除 NULL：WHERE customer_id NOT IN (SELECT id FROM customers WHERE id IS NOT NULL)
大表关联时，EXISTS 通常走半连接，执行计划更可控；IN 在某些数据库（如 MySQL 5.7）可能转成临时表，拖慢速度

用窗口函数替代自连接做动态基准对比

自连接写“每个用户最近一笔订单金额 vs 全体平均”这类对比，SQL 又长又慢，还容易漏掉单条记录的边界情况。窗口函数才是正解：AVG(amount) OVER() 直接拿到全局均值，AVG(amount) OVER (PARTITION BY user_id ORDER BY create_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 就能算出“当前订单前的历史均值”。

窗口函数避免重复扫描，性能提升明显，尤其在百万级订单表上
注意 ORDER BY 必须明确，否则 ROWS BETWEEN 行为不可控（PostgreSQL 和 MySQL 8.0+ 要求排序）
如果基准要按时间范围滑动（比如“过去7天均值”），用 RANGE INTERVAL '7 days' PRECEDING（PostgreSQL）或变量模拟（MySQL）
窗口函数不能直接出现在 WHERE 中，异常检测逻辑得包一层子查询或 CTE

GROUP BY 后 HAVING 检测聚合异常，别在 WHERE 里硬塞

想查“单日下单量突增3倍的日期”，有人会试图在 WHERE 里写 count(<em>) > (SELECT AVG(c) </em> 3 FROM (SELECT COUNT(*) c FROM orders GROUP BY DATE(create_time)) t) ——这不仅语法错（子查询不能直接引用外层分组），而且执行效率极差。

异常检测涉及聚合比较，必须先 GROUP BY，再用 HAVING 过滤
基准值（比如历史7日均值）建议提前算好，存到临时表或 CTE，避免每次重算

示例：

WITH daily_cnt AS (
SELECT DATE(create_time) d, COUNT(*) c
FROM orders
WHERE create_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(create_time)
),
baseline AS (SELECT AVG(c) * 3 AS threshold FROM daily_cnt)
SELECT d, c
FROM daily_cnt, baseline
WHERE c > threshold;

HAVING 是唯一能在分组后对聚合结果做条件筛选的地方，绕不开

LEFT JOIN + IS NULL 检出缺失基准数据的记录

基准对比的前提是“有基准”。但实际中，配置表、维度表经常缺数据——比如商品表里 category_id = 999，但分类表里根本没有这条记录。这种缺失本身就要告警。

用 LEFT JOIN 关联基准表，再用 WHERE base.id IS NULL 直接揪出脏数据
别用 NOT IN 或 NOT EXISTS 替代，它们对空值敏感，且无法区分“值不存在”和“基准表为空”
加上 COUNT(*) 和 GROUP BY 能快速定位缺失集中在哪几个外键字段
生产环境建议加索引：CREATE INDEX idx_orders_category_id ON orders(category_id);，否则大表 JOIN 会卡死