tag:blogger.com,1999:blog-15319370.post6236830412123376923..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Calculating the Financial Median in MySQLrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-15319370.post-88826148807333949552012-03-07T03:13:29.584+01:002012-03-07T03:13:29.584+01:00Thanks for the code. It was a lifesaver. Dealing w...Thanks for the code. It was a lifesaver. Dealing with large result sets, I found a function flavor to be much less unwieldy and simpler to performance tune.<br /><br />-------------------------------<br /><br />DELIMITER $$<br />CREATE FUNCTION `median`( commaDelimitedData varchar(255)) RETURNS decimal(17,3) DETERMINISTIC<br />BEGIN<br /> declare eleCount int;<br /> <br /> set eleCount = (length(commaDelimitedData) - length(replace(commaDelimitedData,',','')))+1;<br /> if ((eleCount=1) and length(trim(commaDelimitedData))=0) then <br /> set eleCount=0;<br /> end if;<br /> <br /> return (substring_index( -- left median: max value in lower half:<br /> substring_index(<br /> commaDelimitedData<br /> , ','<br /> , ceiling(eleCount/2) -- left half of the list <br /> )<br /> , ','<br /> , -1 -- keep only the last value in list<br /> )<br /> + substring_index( -- right median: min value in upper half:<br /> substring_index(<br /> commaDelimitedData<br /> , ','<br /> , -ceiling(eleCount/2) -- right half of the list <br /> )<br /> , ','<br /> , 1 -- keep only the first value in list<br /> )<br /> ) / 2; -- average of left and right medians<br />ENDAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-18681247780207665492010-02-16T11:22:25.195+01:002010-02-16T11:22:25.195+01:00Your a monster!!!! Superb!!!!
TRILLION THANKS!!!!Your a monster!!!! Superb!!!!<br />TRILLION THANKS!!!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-59816873234062620652010-02-08T10:57:18.503+01:002010-02-08T10:57:18.503+01:00Hi Josh!
Unfortunately, MySQL does not let you b...Hi Josh! <br /><br />Unfortunately, MySQL does not let you build aggregrate functions with the stored routine language. So I don't think it's possible to come up with a good solution for this :(rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-16102914091576953362010-02-07T17:11:23.116+01:002010-02-07T17:11:23.116+01:00Roland:
I have been puzzling over how to bring a ...Roland: <br />I have been puzzling over how to bring a median calculation into some standard queries I have. Is there a way of using your methods or pure sql methods in a stored function or proceedure and bring the results into my other existing queries. <br /><br />I have been using the udf median function for a while, but I am trying to move my database to a webserver that won't let me add user defined functions<br /><br />Thanks.Unknownhttps://www.blogger.com/profile/11398613093730059793noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-73363875064189558552008-01-11T21:50:00.000+01:002008-01-11T21:50:00.000+01:00Another Standard SQL trick: SELECT AVG(x) FROM (SE...Another Standard SQL trick: <BR/><BR/>SELECT AVG(x)<BR/> FROM (SELECT x,<BR/> ROW_NUMBER()OVER(ORDER BY x ASC) AS up, <BR/> ROW_NUMBER()OVER(ORDER BY x DESC) AS dn <BR/>FROM Foobar) AS X(x, up, dn)<BR/>WHERE up IN (dn, dn+1, dn-1);<BR/><BR/>This is easier to see with a number line:<BR/>1 2 3 4 5 6 8 9 = up<BR/>9 8 6 5 4 3 2 1 = dn<BR/><BR/>the middle subset is {4,5} and the average is 4.5 or the median.--CELKO--https://www.blogger.com/profile/11908190660799274693noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-6140470374047075662008-01-02T22:13:00.000+01:002008-01-02T22:13:00.000+01:00Roland,Thanks for that, and your detailed explanat...Roland,<BR/>Thanks for that, and your detailed explanation. I am an SQL Newbie and you have helped me very much.<BR/><BR/>Elegant coding.<BR/><BR/>SteveAnonymousnoreply@blogger.com