calculating median values via mysql
Here is a quick mysql sql snippet that I used to calculate median values grouped by year…
select a.year, avg(a.rownum), avg(a.value) from ( select if(@current_year=year, @rownum:=@rownum+1, @rownum:=1) as rownum, @current_year:=year as year, amount as value from (your_data_or_select) as amounts, (select @rownum:=0) as r, (select @current_year:=0) as y order by year, amount) as a, (select year, 0.5+count(*)/2 as median from (your_data_or_select) as amounts group by year) as b where a.rownum between (b.median - 0.5) and (b.median +0.5) and a.year = b.year group by a.year;The interesting bit here is that you don’t need a stored proc and works well with activerecord and the mysql2 gem.
results = ActiveRecord::Base.connection.execute(sql)
