head.WriteLine()

Sonntag, April 23, 2006

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.

4 Comments:

  • Hallo Jörg,
    es stimmt schon die Ranking-Funktion ist einfach prima. Aber das Statement am 2000er wäre ein Cursor nötig, mag ich widerlegen:

    select max(Quantity) as "Fast Median (2)"
    from ( SELECT TOP 50 percent Quantity
    FROM #bla
    order by Quantity) as d

    Das Problem bei dieser und Deiner Lösung ist, dass der Median bei dieser und Deiner Lösung nicht korrekt berechnet wird. Bei geraden Anzahlen ist der Median der Durchschnitt zwischen den Mittleren. Details siehe bitte
    http://www.glorf.it/blog/?id=28, hier ist so eng... ;-)

    Ich habe heute Abend keinen 2005er griffbereit und es deswegen niht ausprobiert, aber Du hast @rowcount als INT deklariert: WHERE Rank = @RowCount / 2
    Daher sollte das Ergebnis immer ganzzahlig sein. Bei 5 Datensätzen ist das Ergebnis 2. Es sollte aber doch dann 3 sein. Oder mache ich einen Denkfehler?

    By Anonymous ThomasG, at Juli 08, 2006 12:39 vorm.  

  • Hi Thomas,
    coole Lösung! Die Geschichte mit dem Mittelwert ist knifflig. Da ist man schnell wieder bei einem Cursor. Da muss ich mal drüber nachdenken. Falls Dir eine Lösung einfällt, lass es mich wissen!

    By Blogger Jörg Neumann, at Juli 12, 2006 8:31 vorm.  

  • Hallo Jörg,
    Deiner Einladung komme ich gerne nach. Wenn man Deine Lösung leicht erweitert, dann sollte es in jedem Fall den richtigen Median liefern:

    SELECT avg(Quantity) AS Median
    FROM
    (
    SELECT ROW_NUMBER() OVER
    ( ORDER BY Quantity DESC) AS Rank,
    Quantity
    FROM #bla
    ) AS sub
    WHERE (SELECT COUNT(*) FROM #bla) / 2 in (rank, rank-1)

    Hier stehen noch mehr Infos dazu:
    Median am SQL Server 2005
    Median am SQL Server 2000

    By Anonymous Thomas, at Juli 17, 2006 6:26 nachm.  

  • Perfekt!

    By Blogger Jörg Neumann, at Juli 18, 2006 7:26 vorm.  

Kommentar veröffentlichen

<< Home