Gibt es eine bessere Methode zur Berechnung des Medians (nicht des Durchschnitts)?

Question

Angenommen, ich habe die folgende Tabellendefinition:

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

Ich möchte den MEDIAN der folgenden Werte berechnen value (nicht die AVG). Der Median ist ein Wert, der die Menge in zwei Teilmengen mit der gleichen Anzahl von Elementen teilt. Wenn die Anzahl der Elemente gerade ist, ist der Median der Durchschnitt des größten Wertes im kleinsten Segment und des kleinsten Wertes im größten Segment. (Siehe Wikipedia für weitere Einzelheiten).

So habe ich es geschafft, den MEDIAN zu berechnen, aber ich denke, es muss einen besseren Weg geben:

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

Irgendwelche Ideen?

Answer 1

Ja, mit PostgreSQL 9.4 können Sie die neu eingeführte Funktion der inversen Verteilung verwenden PERCENTILE_CONT() ist eine Aggregatfunktion für geordnete Mengen, die ebenfalls im SQL-Standard festgelegt ist.

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;

Diese Nachahmung von MEDIAN() を経て PERCENTILE_CONT() ist auch hier dokumentiert .

Answer 2

Es gibt in der Tat einen einfacheren Weg. In Postgres können Sie Ihre eigenen Aggregatfunktionen definieren. Ich habe vor einiger Zeit Funktionen für Median, Modus und Bereich in die PostgreSQL-Snippets-Bibliothek gestellt.

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

Answer 3

Eine einfachere Abfrage dafür:

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;

Wichtige Punkte

• Ignoriert NULL-Werte.
• Hauptmerkmal ist die Fensterfunktion row_number() die es seit Version 8.4 gibt
• Der abschließende SELECT liefert eine Zeile für ungerade Zahlen und avg() von zwei Reihen für gerade Zahlen. Das Ergebnis ist numerisch und wird auf 3 Dezimalstellen gerundet.

Der Test zeigt, dass die neue Version 4x schneller ist als die Abfrage in der Frage (und im Gegensatz zu dieser korrekte Ergebnisse liefert):

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

Answer 4

Für Googler: Es gibt auch http://pgxn.org/dist/quantile Der Median kann nach Installation dieser Erweiterung in einer Zeile berechnet werden.

Answer 5

Einfaches Sql nur mit nativen Postgres-Funktionen:

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;

Sicher können Sie coalesce() oder etwas hinzufügen, wenn Sie Nullen behandeln möchten.