Ich habe heute entdeckt, dass bei head.WriteLine(): Das Median-Problem, Teil 2 diskutiert wird, wie man mit SQL den Median berechnen kann. Die folgende Formulierung reizte meinen Spieltrieb:
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.
Generell stimmt es, die neuen Ranking-Funktionen sind wirklich großartig. Ich hatte sie schon sooo lange vermisst. Aber die Geschichte mit den Cursors oder Schleifen möchte ich gerne widerlegen.
Eine simple Median-Berechnung würde mit SQL Server 2000 so aussehen:
select top 1 Quantity as "Fast Median (1)"
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity) as d
order by Quantity desc
Ein Blick auf den Query Plan zeigt, dass hier noch leichter Spielraum für Verbesserungen besteht:
select max(Quantity) as "Fast Median (2)"
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity) as d
Nun leider ist das Leben nicht immer so einfach. Obige Berechnungen klappen nur bei einer ungeraden Anzahl an Werten. Aber wie man in der Wikipedia nachlesen kann, muss man bei einer geraden Anzahl den Durchschnitt der mittleren Werte ausgeben. Das sieht dann schon komplizierter aus.
select ((select max(Quantity)
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity asc) as d)
+(select min(Quantity)
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity desc) as d)
)/2 as "richtiger Median"
Wenn jetzt zu allen Übel die Werte noch ganzzahlig sind, dann muss man vor der Division den Typ in Numeric konvertieren:
select cast(
(select max(Quantity)
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity asc) as d)
+(select min(Quantity)
from ( SELECT TOP 50 percent Quantity
FROM #bla
order by Quantity desc) as d)
as numeric(12,2))/2 as "richtiger Median bei ganzen Zahlen"
Natürlich ist diese Lösung nicht blitzschnell, aber durch einen geeigneten Index kann man das Ganze doch schön beschleunigen.
Zuletzt noch der Code, um die Beispieltabelle anzulegen und zu bestücken:
if object_id(N'tempdb..#bla') is not null drop table #bla
create table #bla (quantity numeric(12,2))
insert into #bla(quantity) values (1)
insert into #bla(quantity) values (2)
insert into #bla(quantity) values (3)
insert into #bla(quantity) values (4)
insert into #bla(quantity) values (5)
insert into #bla(quantity) values (105)