如何获得忽略异常值的平均值？

Question

假设我有一个PostgreSQL表，其中包含以下值：

id | value
----------
1  | 4
2  | 8
3  | 100
4  | 5
5  | 7

如果我使用postgresql计算平均值，它会给出24.8的平均值，因为100的高值对计算有很大影响。而事实上，我想找到一个平均值约为6左右的值，并消除极端值。

我正在寻找一种“统计正确”的方法来消除极端值。这些极端值无法修复。我不能说；如果一个值超过X，则必须被排除。

我一直在研究postgresql聚合函数，但无法确定应该使用什么。有什么建议吗？

- milovanderlinden

---

你正在寻找一个截尾均值。 - Mike T

Answer 1

PostgreSQL也可以计算标准差。

你可以选择处于average() +/- 2*stddev()之间的数据点，这大致相当于90%最靠近平均值的数据点。

当然2也可以是3（95%）或6（99.995%），但不要过于纠结于数字，因为如果有集合中的离群值存在时，您就不再处理正态分布了。

非常小心并验证其按预期工作。

- Peter Tillemans

---

听起来不错！我不知道stddev会导致集合的百分比，虽然这听起来完全可以理解。我知道如果将您的答案与Rodger的答案结合起来，我必须走在正确的轨道上！ - milovanderlinden

看起来你假设这是一个正态分布（从问题中的示例很难说，实际上，从这样的5个数据点来看，它似乎不是）。如果是这样，你的百分比也不太正确。 - Bruno

Answer 2

我不能说；如果一个值超过X，它必须被消除。

好的，你可以使用having和子查询来消除异常值，类似于：

HAVING value < (
 SELECT 2 * avg(value)
 FROM   mytable
 GROUP BY ...
)

（或者，更复杂的版本可以使用，以消除任何超过2或3个标准偏差的内容，如果你想要更好地消除异常值。）

另一个选择是查看生成中位数值，这是一种相当统计学上可靠的处理异常值的方法；有三个合理的例子： Postgresql Wiki 上的一个，一个 作为Oracle兼容性层构建的, 还有一个来自 PostgreSQL Journal 的例子。请注意它们如何精确/准确地实现中位数的警告。

- Rodger

---

非常好的答案，特别是关于聚合中位数的维基页面！不过，正如Peter Tillemans建议的那样，我将结合stddev。但由于您的答案包含了最多的提示，所以我会将其评为正确答案。 - milovanderlinden

Answer 3

这是一个聚合函数，它可以计算一组值的修剪平均值，排除均值外N个标准差之外的值。

示例：

DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo (x FLOAT);
INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);
INSERT INTO foo VALUES (3);
INSERT INTO foo VALUES (4);
INSERT INTO foo VALUES (100);

SELECT avg(x), tmean(x, 2), tmean(x, 1.5) FROM foo;

--  avg | tmean | tmean 
-- -----+-------+-------
--   22 |    22 |   2.5

代码：

DROP TYPE IF EXISTS tmean_stype CASCADE;
CREATE TYPE tmean_stype AS (
  deviations FLOAT,
    count INT,
    acc FLOAT,
    acc2 FLOAT,
    vals FLOAT[]
);
CREATE OR REPLACE FUNCTION tmean_sfunc(tmean_stype, float, float)
RETURNS tmean_stype AS $$
    SELECT $3, $1.count + 1, $1.acc + $2, $1.acc2 + ($2 * $2), array_append($1.vals, $2);
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION tmean_finalfunc(tmean_stype)
RETURNS float AS $$
DECLARE
    fcount INT;
    facc FLOAT;
    mean FLOAT;
    stddev FLOAT;
    lbound FLOAT;
    ubound FLOAT;
    val FLOAT;
BEGIN
    mean := $1.acc / $1.count;
    stddev := sqrt(($1.acc2 / $1.count) - (mean * mean));
    lbound := mean - stddev * $1.deviations;
    ubound := mean + stddev * $1.deviations;
fcount := 0;
    facc := 0;
    FOR i IN array_lower($1.vals, 1) .. array_upper($1.vals, 1) LOOP
        val := $1.vals[i];
        IF val >= lbound AND val <= ubound THEN
            fcount := fcount + 1;
            facc := facc + val;
        END IF; 
    END LOOP;
IF fcount = 0 THEN
        return NULL;
    END IF;
    RETURN facc / fcount;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE tmean(float, float)
(
    SFUNC = tmean_sfunc,
    STYPE = tmean_stype,
    FINALFUNC = tmean_finalfunc,
    INITCOND = '(-1, 0, 0, 0, {})'
);

Gist（应该是相同的）：https://gist.github.com/4458294

- David Wolever

Answer 4

您可以使用IQR过滤异常值。PL/pgSQL代码：

select percentile_cont(0.25) WITHIN GROUP (ORDER BY value)
  into q1
  from table;
select percentile_cont(0.75) WITHIN GROUP (ORDER BY value)
  into q3
  from table;

iqr := q3 - q1;
min := q1 - 1.5 * iqr;
max := q3 + 1.5 * iqr;

select value
  into result
  from table
  where value >= min and value <= max;
return result;

- cdalxndr

Answer 5

记得使用ntile窗口函数。它能让你轻松地从结果集中分离出极端值。

假设你想要从结果集的两侧各削减10%。那么将值10传递给ntile，并查找2到9之间的值，就能得到你想要的结果。还要注意，如果记录少于10条，可能会意外地削减超过20%，所以一定要检查总记录数。

WITH yyy AS (
  SELECT
    id,
    value,
    NTILE(10) OVER (ORDER BY value) AS ntiled,
    COUNT(*) OVER () AS counted
  FROM
    xxx)
SELECT
  *
FROM
  yyy
WHERE
  counted < 10 OR ntiled BETWEEN 2 AND 9;

- Kouber Saparev