Median einer Spalte berechnen
Median bezeichnet eine Grenze zwischen zwei Hälften. Ein Wert m ist Median, wenn höchstens die Hälfte der Beobachtungen in der Stichprobe einen Wert < m und höchstens die Hälfte einen Wert > m hat.
Sortiert man die Beobachtungswerte der Größe nach, so ist der Median bei einer ungeraden Anzahl von Beobachtungen der Wert der in der Mitte dieser Folge der Beobachtung. Bei einer geraden Anzahl von Beobachtungen gibt es kein einziges mittleres Element, sondern zwei. Hier sind die Werte der beiden mittleren Beobachtungen sowie alle Werte dazwischen (obwohl diese bei keiner Beobachtung aufgetreten sind) ein Median der Stichprobe, da für alle diese Werte obige Bedingung zutrifft. In solchen Fällen wird meist das arithmetische Mittel der beiden mittleren Beobachtungswerte als Median genommen.
Das Beispiel illustriert die Berechnung des Medians in folgenden Schritten:
- Der erste sub-select nummeriert die Zeilen in aufsteigender Reihenfolge mit Hilfe der analytischen Funktion ROW_NUMBER(). Die Zeilennummer wird in der Spalte R gespeichert. Zuzätzlich wird die Gesamtanzahl an Zeilen mit Hilfe der analytischen Funktion COUNT ohne analytische Klausel berechnet.
- Die WHERE-Klausel lässt eine oder zwei mittlere Zeilen durch.
-
CREATE OR REPLACE TABLE tab (col INTEGER); -- fill the table with an odd number of values INSERT INTO tab VALUES 1, 5, 2, 8, 7; WITH numbered_list AS ( SELECT col, -- number the rows subsequently ROW_NUMBER() OVER(ORDER BY col) r, -- calculate the overall count of rows COUNT(*) OVER () COUNT FROM tab ) SELECT AVG(col) median FROM numbered_list WHERE r IN ( CEIL(COUNT/2), FLOOR(COUNT/2) + 1 ) ;
-
MEDIAN ----------------- 5 1 rows in resultset.
Lassen Sie uns eine zusätzliche Zeile in die Tabelle einfügen und den Median neu berechnen:
-
INSERT INTO tab VALUES 10;
-
MEDIAN ----------------- 6 1 rows in resultset.
Analytische Funktion MEDIAN
In der Version 3.3 haben wir eine analytische Funktion MEDIAN eingeführt, so dass die Berechnung nun auf einer sehr einfache Art und Weise erfolgen kann:
-
- SELECT median(col) FROM tab
