{"id":28,"date":"2006-07-08T00:31:54","date_gmt":"2006-07-07T22:31:54","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/2006\/07\/08\/sql-talk\/headwriteline-das-median-problem-teil-2\/"},"modified":"2006-07-09T00:39:31","modified_gmt":"2006-07-08T22:39:31","slug":"headwriteline-das-median-problem-teil-2","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2006\/07\/08\/sql-talk\/headwriteline-das-median-problem-teil-2","title":{"rendered":"Ein Median mit SQL Server 2000"},"content":{"rendered":"

Ich habe heute entdeckt, dass bei head.WriteLine(): Das Median-Problem, Teil 2<\/a> diskutiert wird, wie man mit SQL den Median berechnen kann. Die folgende Formulierung reizte meinen Spieltrieb:<\/p>\n

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.<\/p><\/blockquote>\n

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.
\nEine simple Median-Berechnung würde mit SQL Server 2000 so aussehen:<\/p>\n

select top 1 Quantity as \"Fast Median (1)\"\r\n     from (    SELECT TOP 50 percent Quantity\r\n                      FROM #bla\r\n                      order by Quantity) as d\r\n     order by Quantity desc<\/pre>\n
Ein Blick auf den Query Plan zeigt, dass hier noch leichter Spielraum für Verbesserungen besteht:<\/p>\n
select max(Quantity) as \"Fast Median (2)\"\r\n     from (    SELECT TOP 50 percent Quantity\r\n                      FROM #bla\r\n                      order by Quantity) as d<\/pre>\n
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<\/a> nachlesen kann, muss man bei einer geraden Anzahl den Durchschnitt der mittleren Werte ausgeben. Das sieht dann schon komplizierter aus.<\/p>\n
select ((select max(Quantity)\r\n                   from (    SELECT TOP 50 percent Quantity\r\n                                    FROM #bla\r\n                                    order by Quantity asc) as d)\r\n          +(select min(Quantity)\r\n                   from (    SELECT TOP 50 percent Quantity\r\n                                    FROM #bla\r\n                                    order by Quantity desc) as d)\r\n          )\/2 as \"richtiger Median\"<\/pre>\n
Wenn jetzt zu allen Übel die Werte noch ganzzahlig sind, dann muss man vor der Division den Typ in Numeric konvertieren:<\/p>\n
\r\nselect cast(\r\n          (select max(Quantity)\r\n                from (    SELECT TOP 50 percent Quantity\r\n                                   FROM #bla\r\n                            order by Quantity asc) as d)\r\n        +(select min(Quantity)\r\n                from (    SELECT TOP 50 percent Quantity\r\n                                   FROM #bla\r\n                            order by Quantity desc) as d)\r\n        as numeric(12,2))\/2 as \"richtiger Median bei ganzen Zahlen\"<\/pre>\n
Natürlich ist diese Lösung nicht blitzschnell, aber durch einen geeigneten Index kann man das Ganze doch schön beschleunigen.<\/p>\n
Zuletzt noch der Code, um die Beispieltabelle anzulegen und zu bestücken:<\/p>\n
if object_id(N'tempdb..#bla') is not null drop table #bla\r\ncreate table #bla (quantity numeric(12,2))\r\n\r\ninsert into #bla(quantity) values (1)\r\ninsert into #bla(quantity) values (2)\r\ninsert into #bla(quantity) values (3)\r\ninsert into #bla(quantity) values (4)\r\ninsert into #bla(quantity) values (5)\r\ninsert into #bla(quantity) values (105)<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/28"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=28"}],"version-history":[{"count":0,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/28\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=28"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=28"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=28"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}