ROW_NUMBER()可标记每组内排序位置以清洗异常值，需嵌套子查询使用，注意NULL处理、避免多窗口性能问题，并结合业务规则判断异常边界。
用 ROW_NUMBER() 标记每组内排序位置，再过滤掉头部/尾部异常值
清洗分组数据时，常遇到某组内个别记录明显偏离（比如销售额突增10倍、响应时间飙升到小时级），但又不能整组丢弃。这时靠 ROW_NUMBER() 按业务指标排序后取前N或后N行最直接。
注意它不跳过重复值：相同数值会按任意顺序分配不同序号。如果需要“并列排名”，得换 RANK() 或 DENSE_RANK()。
- 示例：剔除每个
user_id分组中响应时间最长的2条记录 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY response_time DESC) AS rn FROM logs ) t WHERE rn > 2- 别在
WHERE中直接写窗口函数——SQL 不允许，必须套一层子查询或 CTE - ORDER BY 里用
NULLS LAST（PostgreSQL/Oracle）或等效写法，避免空值被排到顶部干扰判断
用 AVG() OVER 和 ABS() 识别偏离均值过远的行
比单纯看极值更稳健的做法，是计算每组的均值和标准差，再筛选偏离超过 2~3 倍标准差的点。窗口函数让这个过程可单次扫描完成，无需自连接或聚合后 JOIN。
但要注意：均值对异常值敏感，若组内本就含大量离群点，AVG() OVER 本身会被拉偏。此时应先用中位数（需配合 PERCENTILE_CONT）或截断均值（去掉首尾10%后再算）。
- 示例：保留每个
product_id组内价格与组内均值偏差绝对值 ≤ 2 倍标准差的记录 SELECT * FROM ( SELECT *, ABS(price - AVG(price) OVER (PARTITION BY product_id)) / NULLIF(STDDEV(price) OVER (PARTITION BY product_id), 0) AS z_score FROM sales ) t WHERE z_score-
NULLIF(..., 0)防止某组仅1条数据导致标准差为0、除零错误 - MySQL 8.0+ 支持
STDDEV()窗口函数；旧版本需用STDDEV_POP()或自行近似
用 COUNT(*) OVER + 条件判断过滤低质量分组
有些“异常”不在单行，而在分组维度：比如某用户只有一条记录（可能是测试数据）、某设备上报频率低于5分钟一次（疑似故障）、某地区订单数少于3单（统计不可靠）。这类问题要先统计每组基础量，再决定是否整组剔除。
关键不是“怎么算数量”，而是“什么时候该用 COUNT(*) OVER 而非 GROUP BY”。答案是：你还想保留原始明细行，只是加个标记用于后续过滤。
- 示例：剔除所有上报次数少于5次的
device_id对应的全部原始记录 SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY device_id) AS cnt FROM telemetry ) t WHERE cnt >= 5- 别写成
COUNT(1) OVER——虽然结果一样，但语义不清；COUNT(*)明确表达“计数所有行” - 若分组键含 NULL，不同数据库处理不同（PostgreSQL 把 NULL 当独立组，MySQL 可能忽略），建议提前
COALESCE(device_id, 'unknown')
窗口函数嵌套导致性能骤降的典型场景
一个常见误操作：在同一个查询里叠用多个开窗函数，且 PARTITION BY 字段不同、ORDER BY 又带复杂表达式。数据库可能为每个窗口单独排序，IO 和内存开销翻倍。
比如同时计算「每组销量均值」+「每组按时间排序的累计销量」+「每组销量排名」，三者分区字段不同，引擎大概率执行三次独立排序。
- 优化方向优先是合并逻辑：能否用一个
PARTITION BY覆盖多个需求？能否把RANK()换成ROW_NUMBER()减少稳定性要求？ - 在 PostgreSQL 中，可查
EXPLAIN ANALYZE看是否有多个WindowAgg节点；在 MySQL 8.0+，关注Using filesort是否多次出现 - 实在无法合并，考虑物化中间结果：先用 CTE 算出高频复用的分组统计，再 JOIN 回原表——有时比纯窗口快一个数量级
实际清洗中，最难的不是写出某个窗口函数，而是判断“这组数据到底该不该留”。窗口函数只是工具，背后得有业务定义的异常边界——比如“响应时间 > 5 秒算异常”这种规则，没人能从 SQL 里自动推出来。
