How Can I Efficiently Calculate the Median in MySQL?

Simplified median calculation in MySQL

Calculating the median (the middle value in a set of numerical data) can be challenging in MySQL. While calculating the mean using AVG(x) is simple, finding the median requires a more complex approach.

MySQL implementation

To simplify median calculation, MariaDB/MySQL provides the following query:

<code class="language-sql">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
  -- 在此处添加任何必要的 WHERE 子句
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );</code>

Explanation

This query uses the following techniques:

• Initialize row counter @rownum and total row count @total_rows to track the position of each row in the sorted data.
• Calculates the median (for even data sets) by averaging the values ​​corresponding to FLOOR((@total_rows 1) / 2) and FLOOR((@total_rows 2) / 2).
• In MariaDB 10.3.3, you have the option to calculate the median directly using the MEDIAN function.

The above is the detailed content of How Can I Efficiently Calculate the Median in MySQL?. For more information, please follow other related articles on the PHP Chinese website!