Есть ли лучший способ рассчитать медианную (не среднюю)

Предположим, что у меня есть следующее определение таблицы:

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

Я хочу рассчитать MEDIAN из value (а не AVG). Медиана - это значение, которое делит множество на два подмножества, содержащие одинаковое количество элементов. Если число элементов четное, медиана - это среднее значение самого большого значения в нижнем сегменте и самое низкое значение самого большого сегмента. (Подробнее см. Wikipedia.)

Вот как мне удается вычислить MEDIAN, но я думаю, что должен быть лучший способ:

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

Любые идеи?

Ответы

Ответ 1

Действительно, есть более простой способ. В Postgres вы можете определить свои собственные агрегатные функции. Я разместил функции для медианного, а также режима и диапазона до библиотеки фрагментов PostgreSQL некоторое время назад.

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

Ответ 2

Да, с 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() через PERCENTILE_CONT() также описана здесь.

Ответ 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.
  • Основной особенностью является функция row_number(), которая была там начиная с версии 8.4
  • Окончательный SELECT получает одну строку для нечетных чисел и avg() двух строк для четных чисел. Результат - числовое, округленное до 3 знаков после запятой.

Тест показывает, что новая версия в 4 раза быстрее, чем (и дает правильные результаты, в отличие от) запроса в вопросе:

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

Ответ 4

Для гуглеров: есть также http://pgxn.org/dist/quantile Медиану можно рассчитать в одной строке после установки этого расширения.

Ответ 5

Простой sql с встроенными функциями postgres:

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() или что-то еще, если вы хотите обрабатывать нули.

Ответ 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

Ответ 7

Используйте функцию "Ниже" для поиска nth percentile

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-й процентиль, который в основном является срединным.

Надеюсь, что это будет полезно.