聚合中位数
注意：此页面已过时。
要在 PostgreSQL 中获取中位数，请使用percentile_cont(0.5) WITHIN GROUP (ORDER BY num_value). 查看文档了解详细信息。但是，有序集聚合不能用作窗口函数，而下面给出的替代方法可以。
统计学中的中位数是将数据样本、总体或概率分布的上半部分与下半部分分隔开来的数值。中位数仅在有序的一维数据上定义，并且与任何距离度量无关。
可以使用quantile 扩展（使用 quantile(some_column,0.5)）获得中位数的更高效实现。
median(numeric)
此代码段也是 ulib_agg 用户定义库 的一部分。
CREATE OR REPLACE FUNCTION _final_median(numeric[])
RETURNS numeric AS
$$
SELECT AVG(val)
FROM (
SELECT val
FROM unnest($1) val
ORDER BY 1
LIMIT 2 - MOD(array_upper($1, 1), 2)
OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE median(numeric) (
SFUNC=array_append,
STYPE=numeric[],
FINALFUNC=_final_median,
INITCOND='{}'
);
用法
SELECT median(num_value) AS median_value FROM t;
注意
与mode()以及range()不同，由于动态限制和偏移，此代码段仅适用于 PostgreSQL 8.4 及更高版本。
注意：此版本不考虑 NULL 值，例如，median('{1,NULL,NULL}') 为 NULL。
此实现也相当慢。对于大约 500K 个元素的表，orafce 实现的速度快约 400 倍。
median(anyelement)
这是对上述代码的改进，有两个创新之处：它通过 anyelement 接受任何数值。它还会剔除空值，这需要额外的处理，因此速度会稍慢。此外，我将其改为返回 float8，这主要是一种审美上的偏好。此代码段也是 ulib_agg 用户定义库 的一部分。
CREATE FUNCTION _final_median(anyarray) RETURNS float8 AS $$
WITH q AS
(
SELECT val
FROM unnest($1) val
WHERE VAL IS NOT NULL
ORDER BY 1
),
cnt AS
(
SELECT COUNT(*) as c FROM q
)
SELECT AVG(val)::float8
FROM
(
SELECT val FROM q
LIMIT 2 - MOD((SELECT c FROM cnt), 2)
OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
) q2;
$$ LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE median(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_median,
INITCOND='{}'
);
用法
SELECT median(value) AS median_value FROM t;
注意
如果你仍然使用 PostgreSQL 8.3 或更低版本，以下是对上述中位数聚合的修改将起作用（注意，首先必须定义 unnest 并创建 PL/pgsql 语言（CREATE LANGUAGE plpgsql;）如果你还没有。另请注意，在未排序的数组上可能存在更高效的中位数函数，例如，使用 选择算法 的 O(N) 与此先进行比较排序的数组的 O(N lg N)。
CREATE OR REPLACE FUNCTION final_median(anyarray) RETURNS float8 AS
$$
DECLARE
cnt INTEGER;
BEGIN
cnt := (SELECT count(*) FROM unnest($1) val WHERE val IS NOT NULL);
RETURN (SELECT avg(tmp.val)::float8
FROM (SELECT val FROM unnest($1) val
WHERE val IS NOT NULL
ORDER BY 1
LIMIT 2 - MOD(cnt, 2)
OFFSET CEIL(cnt/ 2.0) - 1
) AS tmp
);
END
$$ LANGUAGE plpgsql;
CREATE AGGREGATE median(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=final_median,
INITCOND='{}'
);
外部实现
对于“外部”中位数计算，有很多编译版本
- 用于 libpq 的 C 代码
- PL/R（R 语言也是一个统计软件包）
- orafce 具有非常快的中位数实现。它的软件包也以 postgresql-x.x-orafce 的形式在 Debian/Ubuntu 存储库中可用。
- ...
