mysql - 如何获得每组的平均值并找出 SQL 中的异常值

Question

这是我的数据的样子:

id | value | group
------------------
1  | 4     | abc
2  | 8     | def
3  | 100   | abc
4  | 8     | ghi
5  | 7     | abc
6  | 10    | ghi

我需要计算排除异常值(例如 id = 3 for group = abc)的每个组的平均值。然后在平均值旁边显示异常值。对于上述数据，我期待这样的结果:

group = 'abc' 
average = '5.5'
outlier = '100'

Answer 1

一种方法创建一个子查询，其中包含每个组的统计信息(均值和标准差)，然后将其连接回原始表以确定哪些记录是异常值，对于哪个组。

SELECT t1.id,
       t1.group AS `group`,
       t2.valAvg AS average,
       t1.value AS outlier
FROM yourTable t1
INNER JOIN
(
    SELECT `group`, AVG(value) AS valAvg, STDDEV(value) AS valStd
    FROM yourTable
    GROUP BY `group`
) t2
    ON t1.group = t2.group
WHERE ABS(t1.value - t2.valAvg) > t2.valStd   -- any record whose value is MORE
                                              -- than one standard deviation from
                                              -- the mean is an outlier

更新:

由于某种原因，您的 value 列似乎是实际的 varchar 而不是数字类型。这意味着您将无法对其进行任何数学运算。所以首先，通过以下方式将该列转换为整数:

ALTER TABLE yourTable MODIFY value INTEGER;

如果您只想要比平均值更大的离群值，则使用以下 WHERE 子句:

WHERE t1.value - t2.valAvg > t2.valStd