MySQL 使用 Standard Deviations 忽略 Outliers

2026-10-24支付阿杰

结论

先正确连接 Payments 和 Accounts，再按 Account 计算平均值和标准差，最后过滤超出指定标准差范围的记录。

如果 Payments.Account 对应 Accounts.Accountid，连接条件应该写成：

p.Account = a.Accountid

不能写成：

payments.paymentid = accounts.acountid

以下查询保留平均值上下 2 个标准差以内的数据：

WITH eligible_payments AS (
    SELECT
        p.Paymentid,
        p.Account,
        p.Value_gbp
    FROM Payments AS p
    INNER JOIN Accounts AS a
        ON a.Accountid = p.Account
    WHERE a.Entity_type = 'company'
      AND a.Settlement_model = 'payment agent'
      AND p.Value_gbp IS NOT NULL
),
account_stats AS (
    SELECT
        Account,
        AVG(Value_gbp) AS mean_gbp,
        STDDEV_POP(Value_gbp) AS std_gbp
    FROM eligible_payments
    GROUP BY Account
)
SELECT
    ep.Account,
    COUNT(*) AS `# Of Payments`,
    TRUNCATE(AVG(ep.Value_gbp), 2) AS `Avg Value`
FROM eligible_payments AS ep
INNER JOIN account_stats AS s
    ON s.Account = ep.Account
WHERE s.std_gbp = 0
   OR ep.Value_gbp BETWEEN s.mean_gbp - 2 * s.std_gbp
                       AND s.mean_gbp + 2 * s.std_gbp
GROUP BY ep.Account
ORDER BY ep.Account;

原查询为什么出错

原查询有几个彼此独立的问题。

首先，FROM payments, LEFT JOIN (...) 混用了逗号连接和显式 JOIN，语法不正确。使用 JOIN 时，表名后面不能保留逗号。

其次，外层查询的 FROM 中没有声明 accounts，但 ON 条件引用了它：

ON payments.paymentid = accounts.acountid

子查询中的 accounts 只在子查询内部有效，外层查询不能引用。

另外，Paymentid 通常是支付记录的主键，Accountid 是账户主键，两者一般不应直接连接。根据给出的字段结构，更合理的关系是：

Payments.Account = Accounts.Accountid

如果实际数据库通过 rfx_ref 关联，则应改为：

ON a.rfx_ref = p.rfx_ref

具体使用哪个连接字段，应以数据库中的真实外键关系为准。

查询如何处理数据

eligible_payments 先连接账户表并应用业务条件，只保留：

Entity_type = 'company'
Settlement_model = 'payment agent'
Value_gbp 不为 NULL

接着，account_stats 按 Account 计算：

平均值 μ = AVG(Value_gbp)
标准差 σ = STDDEV_POP(Value_gbp)

最终查询只保留符合以下条件的记录：

μ - 2σ ≤ Value_gbp ≤ μ + 2σ

查询再根据过滤后的记录计算数量和平均值。因此，最终的 COUNT 和 AVG 都不会包含被判定为异常值的支付记录。

如何选择标准差范围

示例使用 2 * std_gbp，也就是平均值上下 2 个标准差。这个范围可以按业务规则调整，例如改为 3 个标准差：

WHERE s.std_gbp = 0
   OR ep.Value_gbp BETWEEN s.mean_gbp - 3 * s.std_gbp
                       AND s.mean_gbp + 3 * s.std_gbp

这个倍数不是 MySQL 的固定要求，而是异常值判定规则的一部分。

MySQL 5.7 写法

MySQL 5.7 不支持 CTE，可以改用派生表：

SELECT
    p.Account,
    COUNT(*) AS `# Of Payments`,
    TRUNCATE(AVG(p.Value_gbp), 2) AS `Avg Value`
FROM Payments AS p
INNER JOIN Accounts AS a
    ON a.Accountid = p.Account
INNER JOIN (
    SELECT
        p2.Account,
        AVG(p2.Value_gbp) AS mean_gbp,
        STDDEV_POP(p2.Value_gbp) AS std_gbp
    FROM Payments AS p2
    INNER JOIN Accounts AS a2
        ON a2.Accountid = p2.Account
    WHERE a2.Entity_type = 'company'
      AND a2.Settlement_model = 'payment agent'
      AND p2.Value_gbp IS NOT NULL
    GROUP BY p2.Account
) AS s
    ON s.Account = p.Account
WHERE a.Entity_type = 'company'
  AND a.Settlement_model = 'payment agent'
  AND p.Value_gbp IS NOT NULL
  AND (
      s.std_gbp = 0
      OR p.Value_gbp BETWEEN s.mean_gbp - 2 * s.std_gbp
                         AND s.mean_gbp + 2 * s.std_gbp
  )
GROUP BY p.Account
ORDER BY p.Account;

注意事项

字段列表中写的是 Settlment_model，原查询中使用的则是 settlement_model。请以数据库中的真实字段名为准。虽然 MySQL 的字段名通常不区分大小写，但拼写错误仍会导致 Unknown column。

STDDEV_POP() 用于计算总体标准差，STDDEV() 在 MySQL 中是它的同义函数。如果业务数据属于总体样本，可以使用 STDDEV_POP()；如果需要计算样本标准差，则使用 STDDEV_SAMP()。

某个账户只有一条有效记录时，STDDEV_POP() 会返回 0。查询中的以下条件会保留这类账户：

s.std_gbp = 0

如果改用 STDDEV_SAMP()，单条记录可能得到 NULL，此时可以写成：

s.std_gbp IS NULL
OR s.std_gbp = 0
OR p.Value_gbp BETWEEN s.mean_gbp - 2 * s.std_gbp
                    AND s.mean_gbp + 2 * s.std_gbp

均值和标准差本身也会受到极端值影响。如果支付金额分布明显偏斜，中位数、四分位距（IQR）或业务阈值可能比标准差规则更稳定。