Calculating the Median with Mysql

mysql statistics median
Tim picture Tim · Mar 23, 2011 · Viewed 24.9k times · Source

I'm having trouble with calculating the median of a list of values, not the average.

I found this article Simple way to calculate median with MySQL

It has a reference to the following query which I don't understand properly. 

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

If I have a time column and I want to calculate the median value, what do the x and y columns refer to?

Answer

Reggie Edwards picture Reggie Edwards · May 31, 2011

I propose a faster way.

Get the row count:

SELECT CEIL(COUNT(*)/2) FROM data;

Then take the middle value in a sorted subquery:

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

I tested this with a 5x10e6 dataset of random numbers and it will find the median in under 10 seconds.

This will find an arbitrary percentile by replacing the COUNT(*)/2 with COUNT(*)*n where n is the percentile (.5 for median, .75 for 75th percentile, etc).

Related questions

Simple way to calculate median with MySQL

What's the simplest (and hopefully not too slow) way to calculate the median with MySQL? I've used AVG(x) for finding the mean, but I'm having a hard time finding a simple way of calculating the median. For now, I'm …

Read More
Using MEDIAN along side MAX, MIN, and AVG functions in MySQL

I have the following MySQL query which is working perfectly: select count(*) as `# of Data points`, name, max((QNTY_Sell/QNTYDelivered)*1000) as `MAX Thousand Price`, min((QNTY_Sell/QNTYDelivered)*1000) as `MIN Thousand Price`, avg((QNTY_Sell/QNTYDelivered)*1000) as `MEAN Thousand …

Read More
Mysql, reshape data from long / tall to wide

I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql? Easiest to explain with an example. Suppose you have information on (country, …

Read More