Das Median-Problem, Teil 2

Wie ich im ersten Teil bereits schmerzlich feststellen musste, sind benutzerdefinierte Aggregatfunktionen zwar im Grund eine gute Sache, eignen sich jedoch nicht für die Ermittlung des Median. In diesem Teil versuche ich daher einen anderen Ansatz, der auf T-SQL basiert.

Doch zunächst noch einmal zu den Anforderungen:

• Die Anzahl der Zeilen wird benötigt, um den mittleren Wert zu ermitteln
• Die Zahlenmenge muss in einer sortierten Liste vorliegen

Die erste Anforderung ist relativ einfach über eine COUNT()-Abfrage zu realisieren. Wenn beispielsweise der Median für alle Positionspreise aller Bestellungen ermittelt werden soll, könnte dies so aussehen:

DECLARE @RowCount int
SELECT @RowCount = COUNT(*) FROM Sales.SalesOrderDetail

Nun müssen die Einzelpreise aller Bestellungen ermittelt und in sortierter Form bereitgestellt werden.

SELECT LineTotal
FROM Sales.SalesOrderDetail
ORDER BY LineTotal

Doch wie kann ich nun auf dieser Ergebnismenge den Wert ermitteln, der in der Mitte steht? Hierzu verwende ich eine T-SQL-Neuerung des SQL Server 2005. Über die Rankingfunktion ROW_NUMBER() kann jede Zeile der Ergebnismenge um eine Zählerspalte erweitert werden.

SELECT ROW_NUMBER() OVER
(
    ORDER BY LineTotal DESC
) AS Rank,
LineTotal
FROM Sales.SalesOrderDetail

Hierbei wird innerhalb der ROW_NUMBER()-Funktion ein Ausdruck angegeben, der die Sortierung der Ergebnismenge festlegt, auf die sich die Nummerierung beziehen soll. Das Ergebnis sieht hierbei etwas so aus:

Rank    LineTotal
1       2,3
2       2,8
3       3.2
4       5
...

Um nun den mittleren Wert zu ermitteln, könnte ich in der WHERE-Klausel die Rank-Spalte auf die Gesamtzeilenzahl durch zwei teilen.

WHERE Rank = @RowCount / 2

So einfach geht das jedoch nicht, da ROW_NUMBER() nicht innerhalb der WHERE-Klausel verwendet werden darf. Daher muss ich das Ganze in einer Unterabfrage kapseln und auf der Oberabfrage die Filterung vornehmen.

Alles in allem könnte dies zum Beispiel so aussehen:

DECLARE @RowCount int
SELECT @RowCount = COUNT(*) FROM Sales.SalesOrderDetail

SELECT Rank,
LineTotal
FROM
(
    SELECT ROW_NUMBER() OVER
    (
        ORDER BY LineTotal DESC
    ) AS Rank,
    LineTotal
    FROM Sales.SalesOrderDetail
) AS sub
WHERE Rank = @RowCount / 2

Am Ende habe ich nun meinen Median berechnet, konnte hierbei jedoch nicht den generischen Ansatz einer Aggregatfunktion verwenden. Dank der neuen Rankingfunktionen des SQL Server 2005, ist die Ermittlung jedoch wesentlich einfacher als mit den Vorgängerversionen. Hier hätte ich den Median nämlich in zeitaufwendigen Cursor- oder Schleifendurchläufen ermitteln müssen.