1. 动手前先弄明白3sigma到底在干什么1.1 3sigma的统计学基础和适用边界先把3sigma这个事说透。它的源头是正态分布如果一组数据近似服从正态分布那么约有68.3%的数据落在均值加减1个标准差范围内95.4%落在2个标准差范围内99.7%落在3个标准差范围内。换句话说超出“均值±3倍标准差”这个区间之外的数据出现的概率只有0.3%。统计学上把这0.3%视为小概率事件在实际业务中通常认为这些点已经不属于“正常波动”而是“极端值”。公式很简单上限 平均值 3 × 标准差下限 平均值 - 3 × 标准差凡是小于下限或者大于上限的记录就是我们要处理的极端值。很多做数据分析、数据开发的同学对这个概念耳熟能详但真正落地时容易忽略一个前提3sigma原则对数据分布是有要求的它默认数据近似正态分布。如果你的数据严重偏态比如收入、订单金额、用户点击量这类长尾分布直接用均值加减3个标准差去切大概率会把一大票正常的高值数据误杀结果让你怀疑人生。我自己处理过一批“用户单次访问时长”的数据均值低、方差极大直接套3sigma结果P99以上的用户全被当成异常剔掉了。后来改成先对数据做log变换再计算sigma边界效果才正常。所以用3sigma之前先问自己一个问题这批数据长得像不像“钟形曲线”如果均值和中位数差得很远偏态系数明显那就要谨慎了。1.2 用这个方法的先决条件你的数据得“乖”除了正态分布这个大前提还有几个实际约束需要提前确认。第一数据不能有太多的离群点。这句话听起来像废话但真的很多人没意识到极端值本身会把标准差拉大导致界限外扩某些真正的异常点反而可能被“保护”下来。我常用的办法是先用分位数P1/P99或者IQR四分位距粗筛一遍把明显离谱的数据先标记出来再看剩余数据的分布形态最后决定要不要用3sigma。第二样本量不能太小。如果只有二三十条记录标准差本身估计就很不可靠算出来的3sigma边界没有实际意义。一般我至少要求样本量在100条以上才考虑用3sigma低于这个量级直接去看业务口径或者人工确认。第三数据本身要“可运算”。如果列类型是字符串里面混着“0.00”“-”“N/A”这种东西直接AVG和STDDEV必然报错或者得到错误结果。先做类型转换和数据质量排查这些脏数据不处理干净后面算出来的均值、标准差全是错的3sigma自然也是错的。提示3sigma用于数据清洗本质上是一种“一刀切”的规则。它的优势是简单、可解释、可复现劣势是对分布敏感、对异常值敏感。所以在不满足前提条件时宁可改用分位数法如P1/P99或IQR法也别硬套。2. 整体方案设计跨库清洗怎么选技术栈2.1 三种主流做法对比回到实际场景有tablea源表和tableb目标表它们分别处在不同的数据库中现在要做的就是把tablea里的极端值去掉把干净数据送到tableb。这个“跨库”的动作决定了我们不能简单地在一边执行完SQL就结束。常见的方案无非三种方案核心思路优点缺点适用场景纯SQL 跨库连接利用dblink、外部表等机制在数据库层直接跨库读取和写入数据不落中间层链路短、吞吐高依赖网络连通性、权限配置和数据库方言源和目标都是同类型数据库且具备直连条件ETL脚本Python/Java先用SQL或接口把源表数据拉出来在脚本里用pandas等工具清洗再写入目标表灵活、易调试可以随时看中间结果需要额外部署运行环境数据要多走一跳网络数据库不能直连或清洗逻辑复杂、需要多次迭代存储过程定时调度编写PL/SQL存储过程调度任务定期执行可自动化、可运维符合企业级规范性能瓶颈在数据库复杂逻辑难维护生产环境有固定调度要求且逻辑相对稳定2.2 我为什么推荐直接SQL/PL/SQL干如果是Oracle到Oracle的场景我强烈建议走dblink INSERT INTO ... SELECT的方案。原因很简单数据越少搬动越安全。你把几百万行数据拉到Python里清洗再推回数据库中间占用网络带宽、内存、临时磁盘还可能在序列化和类型转换上栽跟头。而用纯SQL统计和过滤都在数据库内部完成走的是数据库优化器那套成熟的执行计划性能通常是最优的。另外数据库事务保证也是一个不能忽视的因素。纯SQL的方案里INSERT和COMMIT是一个完整事务源表数据在统计和写入过程中如果发生了变化至少你能通过事务隔离级别和加锁策略控制一致性。而Python脚本方案你拉数据、清洗、写回是三个独立步骤中间任何一步出错都可能造成目标表数据不完整或者重复。当然如果源库和目标库是异构数据库比如Oracle到MySQLdblink就没那么顺畅了。这时候我会优先看能不能用Oracle的外部表External Table加DIRECTORY或者MySQL的FEDERATED引擎实在不行才退回到Python脚本用to_sql分批量写入。2.3 跨库数据流动的通用套路不管用哪种技术栈跨库清洗的流程骨架是一样的先确认源表的数据结构和数据量明确要清洗的目标列在源库上做统计算出均值、标准差、最大值、最小值最好顺带看下中位数和分位数计算3sigma上下边界并在源库上查出“会被剔除”的记录数先做个影响评估通过跨库通道把“清洗后”的数据写入目标表写完以后做行数比对、关键字段校验确认清洗逻辑没有出错。这里有个很多人容易犯的错一上来就直接CREATE TABLE tableb AS SELECT * FROM tablea先全量复制再清洗。这是低效且危险的做法。正确姿势是先算边界再过滤最后只把干净数据写过去。全量复制意味着把脏数据也复制了一遍如果中途掉电或者写入失败目标表反而多了一批污染数据。注意如果你确实需要保留历史快照建议把被剔除的极端值单独存到一张“异常数据表”里而不是让它们跟着正常数据一起入库。这样既不影响业务分析又能为后续排查异常原因留下依据。3. 实操从tablea到tableb的完整清洗流程3.1 第一步先摸清数据底细不管后续用什么方案第一步永远是摸底。假设tablea里有一列指标叫score我们需要剔除score明显异常的行。先在源库跑一个统计查询把整体分布看清楚-- 源库执行 SELECT COUNT(*) AS cnt, COUNT(score) AS score_cnt, AVG(score) AS avg_score, STDDEV(score) AS std_score, MIN(score) AS min_score, MAX(score) AS max_score, MEDIAN(score) AS median_score, PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS p25, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS p75 FROM tablea;注意Oracle里MEDIAN()是直接可用的PERCENTILE_CONT的写法也是Oracle的标准语法。执行完以后重点看两个东西均值和标准差是否在同一数量级均值和median是否接近。如果avg_score是100std_score是300说明变异系数太大数据分布很“宽”如果avg_score和median_score差了几倍说明数据偏态明显。这两种情况直接上3sigma都要慎重。作为对比如果你用的是MySQL或者PostgreSQL语法略有差异。MySQL没有MEDIAN函数可以这样模拟SELECT COUNT(*), AVG(score), STDDEV(score), MIN(score), MAX(score), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER () AS median_score FROM tablea;不过MySQL 8.0以上才支持窗口函数老版本就只能靠子查询排序取中间值了。3.2 第二步计算上下边界并筛选异常值摸底没问题以后就可以算边界了。先说一个SQL新手很容易踩的坑不能直接在WHERE子句里引用AVG(score)和STDDEV(score)这种聚合结果必须把统计数据放到子查询或者CTE里再和原表关联。用WITH语句计算3sigma边界并找出极端值WITH stats AS ( SELECT AVG(score) AS avg_score, STDDEV(score) AS std_score FROM tablea WHERE score IS NOT NULL ) SELECT a.*, s.avg_score, s.std_score FROM tablea a CROSS JOIN stats s WHERE a.score IS NOT NULL AND (a.score s.avg_score - 3 * s.std_score OR a.score s.avg_score 3 * s.std_score);执行完这个查询你会得到一个“将被剔除”的数据列表。此时一定不要急着去删除或者写入先看一眼被剔除的数量占总记录数的百分比。如果超过2%~3%说明数据分布可能不太适合3sigma或者你的业务数据里本身就有大量真实的“高值用户”这时候直接剔除会丢失重要信息。如果被剔除的比例合理接下来就要把过滤逻辑和目标表写入合并成一个SQL避免先在源库查一次、再在目标库插入一次这种两步操作带来的数据不一致。3.3 第三步把干净数据写入目标表源库和目标库建立dblink之后可以在目标库侧直接执行-- 目标库执行dblink已指向源库 INSERT INTO tableb(id, score, remark) SELECT a.id, a.score, a.remark FROM tableadblink_src a CROSS JOIN ( SELECT AVG(score) AS avg_score, STDDEV(score) AS std_score FROM tableadblink_src WHERE score IS NOT NULL ) s WHERE a.score IS NOT NULL AND a.score BETWEEN s.avg_score - 3 * s.std_score AND s.avg_score 3 * s.std_score; COMMIT;这个写法有两点值得说明。第一CROSS JOIN子查询里没有GROUP BY所以它只返回一行统计数据与原表逐行关联时不会产生数据放大这是安全的。第二WHERE条件里我用了BETWEEN而不是“大于下限且小于上限”的两个独立条件语义等价但可读性更好。实际生产里如果你想把被剔除的数据留档可以再写一个INSERT INTO tableb_excluded的语句同样带上3sigma边界字段方便以后追溯。如果你只能靠Python脚本完成跨库清洗可以这样写import pandas as pd import numpy as np from sqlalchemy import create_engine src_engine create_engine(postgresql://user:passsrc_host:5432/src_db) dst_engine create_engine(postgresql://user:passdst_host:5432/dst_db) df pd.read_sql(SELECT id, score FROM tablea, src_engine) mean df[score].mean() std df[score].std(ddof1) # 注意和数据库默认口径对齐 lower mean - 3 * std upper mean 3 * std clean_df df[(df[score] lower) (df[score] upper)] excluded_df df[(df[score] lower) | (df[score] upper)] clean_df.to_sql(tableb, dst_engine, if_existsappend, indexFalse) excluded_df.to_sql(tableb_excluded, dst_engine, if_existsappend, indexFalse)Python的好处是中间结果一目了然适合快速验证。但请注意pandas的std()默认是ddof1即样本标准差而Oracle和PostgreSQL的STDDEV()默认也是样本标准差但MySQL的STDDEV()是总体标准差等同于STDDEV_POP。不同数据库口径不一样如果不统一清洗结果会有些许出入。经验做法是双方都用样本标准差这样理论上才一致。3.4 分组3sigma的写法前面说的都是整表一个均值、一个标准差。但在很多真实业务里数据是有“组”的。比如不同门店的销售数据A门店的销售均值是10000标准差是2000B门店的销售均值是100标准差是30。如果你把所有门店数据混在一起算3sigma那么B门店的正常数据可能全被当成极端值因为它们的绝对水平远低于A门店。这种情况必须分组计算3sigmaWITH stats AS ( SELECT store_id, AVG(score) AS avg_score, STDDEV(score) AS std_score FROM tablea WHERE score IS NOT NULL GROUP BY store_id ) SELECT a.* FROM tablea a JOIN stats s ON a.store_id s.store_id WHERE a.score IS NOT NULL AND a.score BETWEEN s.avg_score - 3 * s.std_score AND s.avg_score 3 * s.std_score;分组的时候要额外注意某些组记录数太少比如只有两三条标准差会很小甚至为0。标准差为0时3sigma区间就退化成平均值本身只有恰好等于平均值的记录才能通过这明显不符合业务直觉。所以我在分组3sigma的实践中一般会加一个最小样本量门槛比如只对组内记录数大于等于30的组做清洗样本量不足的组直接整体放行标记为“需要人工复核”。4. 踩坑记录与排查技巧4.1 不同数据库SQL方言差异我在Oracle上写惯了换到其他数据库时经常被语法差异坑到。整理几个最容易踩的点STDDEV函数口径不同Oracle里STDDEV()是样本标准差MySQL里STDDEV()是总体标准差。如果不统一口径两边算出来的边界可能差一点。建议明确指定Oracle用STDDEV()MySQL用STDDEV_SAMP()PostgreSQL用STDDEV_SAMP()或STDDEV()都行PG的stddev是样本。中位数函数Oracle有MEDIAN()MySQL没有需要借助PERCENTILE_CONT窗口函数。dblink写法Oracle的dblink查询是tableadblink_srcPostgreSQL的dblink模块则要配合dblink_connect函数SQL Server走的是Linked Server加[db].[schema].[table]四段式命名。跨库方案一定要先确认目标数据库支持哪种跨库机制否则后面全白搭。4.2 NULL和0值怎么处理AVG和STDDEV在大多数数据库里都会忽略NULL但如果你在INSERT语句里用了WHERE a.score IS NOT NULL那么NULL值根本不会进入目标表。如果业务上NULL表示“缺失”而非“异常”这没问题但如果NULL有业务含义比如“尚未评分”那么只清洗score这一列时就要小心别把NULL行全部拒绝掉。0值处理起来更微妙。很多统计指标里0是合法值比如“当日订单数”为0完全正常但有些指标里0根本不合法比如“成交金额”为0说明数据采集异常。我在实际项目中见过一种情况表里有相当一部分0值这些0值把标准差撑大了导致真正的异常高值反而落在3sigma区间内“浑水摸鱼”。解决办法是清洗前先统计0值占比并和业务确认它的合法性。如果0值不合法先把它们单独剔除再对剩余非零数据做3sigma。4.3 多列联合判断 vs 单列独立处理如果tablea里不止一列数值指标比如同时有amount金额、quantity数量、price单价要分别计算各自的3sigma边界。此时有两种处理方式按行处理只要任一列超界整行都视为极端值按列处理先剔除amount超界的行再在剩余基础上剔除quantity超界的行。按行处理逻辑简单但可能把“某一行单价极高但金额正常”的数据也剔除掉。按列处理更精细但是清洗顺序影响结果。我的建议是除非业务明确要求“任一指标超界就是异常”否则优先采用按列逐轮清洗并在每一步都记录剔除数量。这样出问题时你可以很清楚地回溯到是哪一列、哪一轮导致的。还有一种更工程化的做法先对每列做z-score标准化即(score - avg) / std然后取绝对值保留所有列z-score绝对值都小于等于3的行。这个写法在SQL里就是一个多条件组合比多次子查询嵌套清爽很多。4.4 数据量大时的性能问题几万行的表怎么折腾都无所谓一旦到了几千万行3sigma清洗的性能问题就很现实了。首先计算AVG和STDDEV需要全表扫描这没法避免其次要把统计结果与原表关联又需要一遍扫描。两遍全表扫描一般来说可以接受但如果表上有多个索引写入目标表的性能可能被索引维护拖累。几个经验技巧如果源表数据量巨大建议先用采样数据算3sigma边界比如TABLESAMPLE SYSTEM(10)抽10%的数据先看边界是否合理再全量执行。清洗前把目标表的索引先DRO掉插入完成后再重建。很多团队忽视这个操作结果插入比清洗还慢。如果数据是离线批量同步建议把清洗结果先写入临时表再通过分区交换或者MERGE方式合并到正式表里。这样即使中途失败也不会污染正式数据。除非必须不要写PL/SQL游标循环逐行判断。数据库里集合操作比逐行处理快几个数量级能用一条SQL解决就别写FOR循环。4.5 PL/SQL导入Excel时容易踩的坑场景描述里提到“plsql导入excel到数据表”这里说的应该是PL/SQL Developer这个工具。用它的“Text Importer”导入Excel时我踩过几个坑列头识别问题Excel第一行往往是列名PL/SQL Developer导入时如果没勾选“Header in first line”列名会当成数据导入导致类型转换失败。日期格式问题Excel里的日期在导入时可能被转成文本比如2024-01-01变成“45292”Excel的数字日期。解决办法是导入前先把Excel里的日期列统一格式化成YYYY-MM-DD字符串目标表字段用VARCHAR2或明确格式的DATE再导入。数值列被识别为文本Excel某列如果混入几个文本整个列在导入时可能被判断为VARCHAR2。这样后续对数值列做AVG、STDDEV时会报ORA-01722。导入前把该列所有单元格设为“常规”格式并排查非数字内容。大数据量导入缓慢几万行导入可能还好几十万行在PL/SQL Developer里就很慢。建议分批导入或者先导入外部表再INSERT INTO ... SELECT。生产环境我更推荐外部表方式用DIRECTORY指向服务器目录导入速度和稳定性都远胜于GUI工具。4.6 3sigma不是银弹警惕边界效应最后说一个很多人不会注意到的细节3sigma会随着数据本身变化而变化。如果极端值很多均值会被拉偏标准差会被拉大结果就是3sigma区间变宽部分极端值反而“安全过关”。如果你第一次洗完剔除极端值以后又重新计算均值标准差再剔除一轮那等于在被污染过的数据上二次清洗边界会不断漂移极端值可能越切越多。我的习惯是3sigma边界只算一次并且把上下边界值保存下来。第一轮清洗后把剔除的数据放到异常表如果第二轮还想清洗必须用第一轮的固定边界而不是重新计算的边界。这样规则稳定才能保证每次跑批结果可复现。另外对于严重偏态的数据log变换是好帮手。先对原列取自然对数再对log值计算3sigma最后把边界通过exp还原回原值。这样既能利用3sigma的简洁性又不会误杀长尾高值。-- 对log变换后的值计算3sigma并还原边界 WITH stats AS ( SELECT AVG(LN(score)) AS avg_log, STDDEV(LN(score)) AS std_log FROM tablea WHERE score IS NOT NULL AND score 0 ) SELECT EXP(s.avg_log - 3 * s.std_log) AS lower_limit, EXP(s.avg_log 3 * s.std_log) AS upper_limit FROM stats s;注意这种方法只适用于score严格大于0的数据因为0和负数没法取对数。实际业务中金额、数量这类指标用log变换的效果普遍不错。我在实际项目里已经数不清用过多少次这个方案了。常规做法先跑一遍统计看一眼均值和分位数再决定是直接上3sigma还是先做log变换。这样做最稳妥也最能体现数据分析的功底——不是会写SQL就行而是知道自己手里的数据长什么样该用哪种“刀”去切。最后再分享一个建议不管用哪种清洗规则都务必把“被剔除的样本数、边界值、剔除规则”记录下来这既是数据治理的要求也是日后跟业务方对线时最有说服力的凭证。