MySQL 选择使用 AVG 和 STD 条件排除异常值的结果

Question

我正在尝试编写一个查询，以排除超出结果集平均值 6 个标准差的值。我希望这可以通过子查询优雅地完成，但我一无所获，在我读过的每个类似案例中，目标似乎只是一点不同。我的结果集似乎仅限于一行，我猜是由于调用了聚合函数。从概念上讲，这就是我所追求的:

SELECT t.Result FROM
  (SELECT Result, AVG(Result) avgr, STD(Result) stdr
   FROM myTable WHERE myField=myCondition limit=75) as t
WHERE t.Result BETWEEN (t.avgr-6*t.stdr) AND (t.avgr+6*t.stdr)

我可以通过用它自己的选择语句替换每次使用 STD 或 AVG 值(即 t.avgr)来让它工作:

(SELECT AVG(Result) FROM myTable WHERE myField=myCondition limit=75) 

然而，这似乎比我预期的要困惑得多(我有一些条件)。起初我认为指定一个 HAVING 子句是必要的，但随着我了解更多，它似乎并不是我所追求的。我很接近吗？是否有一些时髦的方法来访问用于条件的聚合函数的值(无需返回聚合值)？

Answer 1

是的，您的子查询是一个聚合查询，没有 GROUP BY 子句，因此它的结果是单行。当您从中选择时，您不能获得超过一行。此外，它是一个 MySQL 扩展，您可以在子查询的选择列表中包含 Result 字段，因为它既不是分组列也不是组的聚合函数(所以它甚至意味着什么在这种情况下，除非可能所有相关列的值都相同？)。

您应该能够像这样一起计算平均值和标准偏差，而不是按结果计算一次:

SELECT t.Result FROM
  myTable AS t
  CROSS JOIN (
    SELECT AVG(Result) avgr, STD(Result) stdr
    FROM myTable
    WHERE myField = myCondition
  ) AS stats
WHERE 
  t.myField = myCondition
  AND t.Result BETWEEN (stats.avgr-6*stats.stdr) AND (stats.avgr+6*stats.stdr)
LIMIT 75

请注意，您需要注意统计信息是在您从中选择的同一组行上计算的，因此重复了 myField = myCondition 谓词，但也删除了LIMIT 子句仅适用于外部查询。

您可以向聚合子查询添加更多统计信息，前提是它们都是在同一组行上计算的，或者您可以通过单独的子查询连接在不同行上计算的其他统计信息。请确保您所有的统计子查询都恰好返回一行，否则您将得到重复的(或没有)结果。