代码之家 › 专栏 › 技术社区 › milovanderlinden

如何得到忽略异常值的平均值?

aggregate statistics postgresql

milovanderlinden · 技术社区 · 16 年前

假设我有一个具有以下值的PostgreSQL表:

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

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

我正在寻找一种消除极端的方法,并希望做到“统计上正确”。极端的不能被修正。我不能说,如果一个值超过x,就必须消除它。

我一直在埋头于PostgreSQL聚合函数,但我无法确定什么是适合我使用的。有什么建议吗?

4 回复 | 直到 10 年前

Rodger 16 年前

我不能说,如果一个值超过x,就必须消除它。

好吧,您可以使用having和subselect来消除异常值,比如:

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

(或者,对于这一点,如果您希望能够更好地消除异常值,可以使用更复杂的版本来消除超过2或3个标准差的任何内容。)

另一种选择是考虑生成一个中值,这是一种相当合理的统计方法来计算异常值;令人欣慰的是,有三个合理的例子: one from the Postgresql Wiki ,一 built as an Oracle compatability layer 和另一个来自 PostgreSQL Journal . 请注意有关它们如何精确/准确地实现中间层的注意事项。

Peter Tillemans 16 年前

PostgreSQL还可以计算标准差。

您只能获取average()+/-2*stddev()中的数据点,该数据点大致对应于最接近平均值的90%数据点。

当然,2也可以是3(95%)或6(99.995%),但不要挂断数字,因为在有集合异常值的情况下,您不再处理正态分布。

非常小心,并验证它是否按预期工作。

David Wolever 13 年前

这是一个聚合函数,它将计算一组值的修剪平均值,不包括平均值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;
    -- RAISE NOTICE 'mean: % stddev: % lbound: % ubound: %', mean, stddev, lbound, ubound;

    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, {})'
);

要点(应相同): https://gist.github.com/4458294

Kouber Saparev 10 年前

介意使用 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;