sql中基于σ和标准差的异常值的选择

Question

样本数据是这样的。

​

​

我要为每个班从4西格玛中选择离群点。我试过了

  select  value,class,AVG(value) as mean, STDEV(value)as st, size  from Data
 having value<mean-2*st OR value>mean+2*st group by calss

似乎不起作用。我应该在这里使用“有”或“在哪里”的条款吗？

我想要的结果是整个第三排和第八排。

Answer 1

当您查看的条件是该行的属性时，请使用where，即where class = 1 (类为1的所有行)或where size > 2 (大小>2的所有行)。当条件是一组行的属性时，可以使用group by ... having，例如group by class having avg(value) > 2 (平均值>2的所有类)。

在这种情况下，您需要where，但是有一个复杂的问题。仅在每一行中都没有足够的信息来编写必要的where子句，因此必须通过子查询获得它。

最终，您需要类似于SELECT value, class, size FROM Data WHERE value < mean - 2 *st OR value > mean + 2*st;的东西，但是您需要一个子查询来获得mean和st。

这样做的一种方法是：

SELECT value, Data.class, size, mean, st FROM Data, 
INNER JOIN (
  SELECT class, AVG(value) AS mean, STDEV(value) AS st 
  FROM Data GROUP BY class
) AS stats ON stats.class = Data.class
WHERE value < mean - 2 * st OR value > mean + 2 * st;

这将创建一个子查询，该查询获取每个类的均值和标准差，将这些数字与匹配类连接到行，然后应用异常值检查。