How to Efficiently Calculate the Median in MySQL?

Calculate MySQL median easily

In statistical analysis and data interpretation, determining the median of a data set is crucial. While MySQL provides the convenient AVG() function to calculate the average, finding the median requires a different approach. This article provides a simplified solution for calculating the median using a single MySQL query.

Efficient median calculation

MySQL provides a more efficient way than sorting and selecting rows in PHP:

<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>

Code interpretation:

• The subquery calculates the row number and total number of rows for each value.
• The AVG() function calculates the average of the median.
• The IN clause filters the results and includes the middle row if the number of rows is an even number.

Performance enhancement

An optimized version of this query uses row count operations to avoid a second pass or join:

<code class="language-sql">SELECT AVG(dd.val) as median_val
FROM (
  SELECT
    d.val,
    (@rownum:=@rownum+1) as `row_number`  -- 性能优化
  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((@rownum)/2), FLOOR((@rownum)/2)+1 )  -- 或 FLOOR((@rownum)/2), FLOOR((@rownum)/2)+1 )</code>

MariaDB enhancement

MariaDB version 10.3.3 includes built-in MEDIAN function:

<code class="language-sql">SELECT MEDIAN(val) AS median_val
FROM data</code>

Together, these methods provide a straightforward and efficient solution for calculating the median in MySQL, allowing developers to easily extract meaningful insights from the data.

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