如何使用标准差（standard deviation）剔除销售增长率数据中的异常值

常见误区澄清 #

异常值不等同于数值高于标准差的记录，标准差仅代表数据的离散程度，行业通用的标准差剔异常逻辑为「n倍标准差原则」：

• 常规正态分布数据采用3σ原则：落在平均值 ± 3*标准差范围外的记录判定为异常
• 偏态分布数据可调整为2σ，也可结合业务特性自定义系数
• 销售增长率数据天然无负值，可以仅判断上限，无需设置下限阈值，避免误删低增长/零增长的正常客户记录

方案效率优化 #

你当前先存变量、再写临时表的方案不是最优选择，无需中间存储，可通过CTE（公共表表达式）一次性完成计算，减少表扫描次数，避免硬编码阈值带来的维护问题，参考SQL实现如下：

WITH stat_params AS (
    -- 第一步：计算整体均值和标准差
    SELECT 
        AVG(sales_growth_percentage) AS avg_growth,
        STDDEV(sales_growth_percentage) AS std_growth
    FROM 你的原始数据表名
)
SELECT
    COUNT(customer) AS valid_customer_count,
    SUM(sales_growth_percentage) AS total_valid_growth
FROM 你的原始数据表名 t
CROSS JOIN stat_params p
-- 这里2为标准差系数，可根据业务需求调整为1.5/3等数值
WHERE t.sales_growth_percentage <= p.avg_growth + 2 * p.std_growth

补充建议 #

如果你的销售增长率数据偏态非常严重（比如你的示例中大部分客户增长率低于30，仅2个客户数值超过500），标准差法的剔除效果可能不佳，可以替换为分位数法，示例如下：

WITH percentile_params AS (
    -- 取95分位数作为阈值，剔除最高5%的异常值
    SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY sales_growth_percentage) AS p95_growth
    FROM 你的原始数据表名
)
SELECT
    COUNT(customer) AS valid_customer_count,
    SUM(sales_growth_percentage) AS total_valid_growth
FROM 你的原始数据表名 t
CROSS JOIN percentile_params p
WHERE t.sales_growth_percentage <= p.p95_growth

内容的提问来源于stack exchange，提问作者SRJCoding