使用MySQL剔除异常值并计算平均交付周期的技术问询

问题点 #

• 聚合与行级字段混用错误：子查询里同时使用AVG()/STDDEV()（全局聚合函数）和DATEDIFF(shipped_date, order_date)（行级字段），无GROUP BY时多数SQL引擎会报错；即便允许执行，actual_ave_lead_time返回的是全表交付周期平均值，并非剔除异常值后的结果。
• Z分数公式错误：正确Z分数应为(单个交付周期 - 全局平均值) / 全局标准差，原写法遗漏分子括号，逻辑变成单个交付周期 - (全局平均值/全局标准差)，完全偏离计算逻辑。
• WHERE条件语法错误：BETWEEN的正确用法是BETWEEN 最小值 AND 最大值，原写法BETWEEN zscore<1.96 AND >-.96语法混乱，且阈值不对称（合理阈值应为±1.96）、符号书写有误。

修正方案 #

分两步实现：先计算全表交付周期的平均值和标准差，再筛选Z分数在合理范围的行，最终计算这些行的平均交付周期。

修正后的SQL示例（以MySQL为例） #

-- 方式1：使用CTE预计算全局统计值
WITH global_stats AS (
    SELECT
        AVG(DATEDIFF(shipped_date, order_date)) AS avg_lead_time,
        STDDEV(DATEDIFF(shipped_date, order_date)) AS std_lead_time
    FROM orders
)
SELECT
    ROUND(AVG(DATEDIFF(o.shipped_date, o.order_date)), 2) AS actual_ave_lead_time
FROM orders o
CROSS JOIN global_stats gs
WHERE 
    (DATEDIFF(o.shipped_date, o.order_date) - gs.avg_lead_time) / gs.std_lead_time 
    BETWEEN -1.96 AND 1.96;

替代写法（子查询版） #

SELECT
    ROUND(AVG(DATEDIFF(shipped_date, order_date)), 2) AS actual_ave_lead_time
FROM orders,
(
    SELECT
        AVG(DATEDIFF(shipped_date, order_date)) AS avg_lead_time,
        STDDEV(DATEDIFF(shipped_date, order_date)) AS std_lead_time
    FROM orders
) AS stats
WHERE 
    (DATEDIFF(shipped_date, order_date) - stats.avg_lead_time) / stats.std_lead_time 
    BETWEEN -1.96 AND 1.96;

说明 #

1. 先通过CTE或子查询计算全表的交付周期平均值和标准差，避免重复计算；
2. 通过关联操作将全局统计值匹配到每一行，计算单个订单的Z分数；
3. 筛选Z分数在±1.96区间内的正常数据，再计算这些数据的平均交付周期，得到剔除异常值后的结果。

内容的提问来源于stack exchange，提问作者Joseph Badana Rivera