Ausgehend von dem Posting letzte Woche möchte eine Lösung für den Median am SQL Server 2005 vorstellen, die wegen der neuen TSQL-Features um einiges performanter ist, als die "alte" Lösung:
Zum Vergleich hier noch mal die "beste" Lösung am SQL Server 2000:
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"
Diese Lösung am SQL Server 2005 ist etwa um Faktor 4 schneller. Sie basiert auf dem Ansatz von Itzik Ben-Gan, den ich jetzt entdeckt habe: "Calculating the Median Gets Simpler in SQL Server 2005" by Itzik Ben-Gan. (Ein wenig bin ich stolz, dass er für den 2000er die gleiche Lösung vorschlägt wie ich.. 😉 )
In einer Common-Table-Expression wird die komplette Liste durchnumeriert und gezählt. Auf dem Ergebnis werden dann alle bis auf der bzw. die beiden mittleren Datensätze rausgefiltert (WHERE). Dann muss nur noch der Durchschnitt auf dem Rest berechnet werden:
WITH blaRN AS
(
SELECT Quantity,
ROW_NUMBER() OVER(ORDER BY Quantity) AS RowNum,
(select COUNT(*) from #bla) AS Cnt
FROM #bla
)
SELECT avg(Quantity) as Median
FROM blaRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
Die nächste Lösung für den SQL 2005 basiert auf der Losung von Jörg Neumann, liefert aber in allen Fällen ein korrektes Ergebnis.
Hier passiert im Prinzip das gleiche wie oben. Allerdings wird hier in einer Derived-Table durchgezählt. In der äußeren Query werden dann alle bis auf den bzw. die beiden mittleren Datensätze gefiltert. Dann muss nur noch deren Durchschnittswert berechnet werden:
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)
Der Zugriffplan der unteren Lösung ist leicht komplizierter, aber das macht bei meinen paar Datensätzen keinen Unterschied aus…
Einige sehr interessante Ansätze stehen übrigens auch bei Dan Sullivan.
Anbei noch der Code, um die Beispieltabelle zu füllen:
if object_id(N'tempdb..#bla') is not null drop table #bla
create table #bla (quantity numeric(12,2))
create clustered index i1 on #bla(quantity)
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)