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

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

Sie können aber auch eine Funktion erstellen und diese an beliebiger Stelle in Ihren weiteren Abfragen verwenden.

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

Answer 2

Verwenden Sie die folgende Funktion zur Ermittlung des n-ten Perzentils

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;

In Ihrem Fall ist es das 50. Perzentil.

Verwenden Sie die nachstehende Abfrage, um den Median zu ermitteln

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

Damit erhalten Sie das 50. Perzentil, das im Grunde den Median darstellt.

Ich hoffe, dies ist hilfreich.