Easy way to calculate median in MySQL

Question

What is the simplest (and hopefully not too slow) way to calculate the median using MySQL? I've used AVG(x) to find the mean, but I'm having trouble finding an easy way to calculate the median. Currently I return all rows to PHP, sort, and then select the middle rows, but there must be an easy way to do it in a single MySQL query.

Example data:

id | val
--------
 1 4
 2 7
 3 2
 4 2
 5 9
 6 8
 7 3

Sort by val we get 2 2 3 4 7 8 9, so the median should be 4, not SELECT AVG(val) is equal to 5.

Answer 1

I just found another answer online in the comments:

Make sure your columns are well indexed and that the index is used for filtering and sorting. Validate through execution plan.

select count(*) from table --找到行数

Calculate the "median" row number. Possible use: median_row = floor(count / 2).

Then select from the list:

select val from table order by val asc limit median_row,1

This should return a value you want.

Answer 2

In MariaDB/MySQL:

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- 在这里放置一些条件语句
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen pointed out that after the first pass, @rownum will contain the total number of rows. This can be used to determine the median, so no second pass or join is required.

Additionally, AVG(dd.val) and dd.row_number IN(...) are used to correctly calculate the median when the number of records is even. The reasoning is as follows:

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- 当total_rows为3时，平均行数为2和2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- 当total_rows为4时，平均行数为2和3

Finally, MariaDB 10.3.3 includes the MEDIAN function