有更好的方法计算中位数（不是平均数）吗？

Question

假设我有以下表定义：

CREATE TABLE x (i serial primary key, value integer not null);

我想计算value的中位数（而不是平均值）。中位数是将集合分成包含相同数量元素的两个子集的值。如果元素数为偶数，则中位数是最低段中最大值和最高段中最小值的平均值。（有关更多细节，请参见维基百科。）

以下是我计算中位数的方法，但我认为肯定有更好的方法：

SELECT AVG(values_around_median) AS median
  FROM (
    SELECT
       DISTINCT(CASE WHEN FIRST_VALUE(above) OVER w2 THEN MIN(value) OVER w3 ELSE MAX(value) OVER w2 END)
        AS values_around_median
      FROM (
        SELECT LAST_VALUE(value) OVER w AS value,
               SUM(COUNT(*)) OVER w > (SELECT count(*)/2 FROM x) AS above
          FROM x
          GROUP BY value
          WINDOW w AS (ORDER BY value)
          ORDER BY value
        ) AS find_if_values_are_above_or_below_median
      WINDOW w2 AS (PARTITION BY above ORDER BY value DESC),
             w3 AS (PARTITION BY above ORDER BY value ASC)
    ) AS find_values_around_median

有什么想法吗？

- Ghislain Leveque

---

"Mean"和"average"是同义词。你所询问的是"中位数"：http://en.wikipedia.org/wiki/Median - Sinan Ünür

平均值 http://zh.wikipedia.org/wiki/%E5%B9%B3%E5%9D%87 is指数字之和除以数量的结果。 - ChrisF

确实。有了这些信息，也许谷歌会更容易一些 =) - David Hedlund

可能是使用MySQL计算中位数的简单方法的重复问题。 - ChrisF

@ChrisF - 不是重复。使用不同的关系型数据库管理系统。PostgreSQL 可能比 MySQL 更好，因为它支持分析函数和用户定义的聚合函数 http://wiki.postgresql.org/wiki/Aggregate_Median - Martin Smith

请将主题从平均值更改为中位数。 - Scott Bailey

Answer 1

是的，使用PostgreSQL 9.4，您可以使用新引入的反向分布函数PERCENTILE_CONT()，这是一种有序集聚合函数，也被规定在SQL标准中。

WITH t(value) AS (
  SELECT 1   UNION ALL
  SELECT 2   UNION ALL
  SELECT 100 
)
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY value)
FROM
  t;

这篇文章介绍了如何通过使用反向分布函数来模拟MEDIAN()聚合函数，并且也在这里有详细的文档.

- Lukas Eder

Answer 2

确实有更简单的方法。在Postgres中，您可以定义自己的聚合函数。我之前发布了用于计算中位数、众数和范围的函数到PostgreSQL片段库。

http://wiki.postgresql.org/wiki/Aggregate_Median

- Scott Bailey

Answer 3

一个更简单的查询如下：

WITH y AS (
   SELECT value, row_number() OVER (ORDER BY value) AS rn
   FROM   x
   WHERE  value IS NOT NULL
   )
, c AS (SELECT count(*) AS ct FROM y) 
SELECT CASE WHEN c.ct%2 = 0 THEN
          round((SELECT avg(value) FROM y WHERE y.rn IN (c.ct/2, c.ct/2+1)), 3)
       ELSE
                (SELECT     value  FROM y WHERE y.rn = (c.ct+1)/2)
       END AS median
FROM   c;

主要要点

• 忽略NULL值。
• 核心特性是自版本8.4以来就存在的row_number()窗口函数。
• 最终SELECT语句得到一个奇数个数的行和两个偶数个数的行的avg()。结果为数字，四舍五入保留三位小数。

测试表明，新版本比问题中的查询速度快4倍（并且产生正确的结果）：

CREATE TEMP TABLE x (value int);
INSERT INTO x SELECT generate_series(1,10000);
INSERT INTO x VALUES (NULL),(NULL),(NULL),(3);

- Erwin Brandstetter

Answer 4

只使用本地的Postgres函数的简单SQL：

select 
    case count(*)%2
        when 1 then (array_agg(num order by num))[count(*)/2+1]
        else ((array_agg(num order by num))[count(*)/2]::double precision + (array_agg(num order by num))[count(*)/2+1])/2
    end as median
from unnest(array[5,17,83,27,28]) num;

如果你想处理空值，当然可以添加coalesce()或其他函数。

- Ghost

Answer 5

对于谷歌员工：还有http://pgxn.org/dist/quantile，安装此扩展后可以在一行代码中计算中位数。

- user1270496

Answer 6

CREATE TABLE array_table (id integer, values integer[]) ;

INSERT INTO array_table VALUES ( 1,'{1,2,3}');
INSERT INTO array_table VALUES ( 2,'{4,5,6,7}');

select id, values, cardinality(values) as array_length,
(case when cardinality(values)%2=0 and cardinality(values)>1 then (values[(cardinality(values)/2)]+ values[((cardinality(values)/2)+1)])/2::float 
 else values[(cardinality(values)+1)/2]::float end) as median  
 from array_table

或者你可以创建一个函数并在以后的查询中随意使用。

CREATE OR REPLACE FUNCTION median (a integer[]) 
RETURNS float AS    $median$ 
Declare     
    abc float; 
BEGIN    
    SELECT (case when cardinality(a)%2=0 and cardinality(a)>1 then 
           (a[(cardinality(a)/2)] + a[((cardinality(a)/2)+1)])/2::float   
           else a[(cardinality(a)+1)/2]::float end) into abc;    
    RETURN abc; 
END;    
$median$ 
LANGUAGE plpgsql;

select id,values,median(values) from array_table

- Siddharth Tayade

Answer 7

请使用以下函数查找第n个百分位数：

CREATE or REPLACE FUNCTION nth_percentil(anyarray, int)
    RETURNS 
        anyelement as 
    $$
        SELECT $1[$2/100.0 * array_upper($1,1) + 1] ;
    $$ 
LANGUAGE SQL IMMUTABLE STRICT;

在您的情况下，这是第50个百分位数。

使用以下查询获取中位数。

SELECT nth_percentil(ARRAY (SELECT Field_name FROM table_name ORDER BY 1),50)

这将给您第50百分位数，也就是中位数。

希望这对您有所帮助。

- Sowmiya Raja Radhakrishnan