CODEWITHSUNDEEP
mysql
user13851309
user13851309

Reputation: 79

Efficient way to get median in a unindexed table

I am trying to find median salary in this table.

Is there an aggregate function like AVG() to find a median in an unindexed table efficiently? Thanks.

id | Salary 
-----------
1    300
2    250
7    180
8    150
9    150
10   160
11   180
15   140

Upvotes: 1

Views: 60

Answers (2)

Meghshyam Sonar
Meghshyam Sonar

Reputation: 301

select avg(Salary)
from
(
select *,
         ROW_NUMBER() over (order by Salary desc) as desc_salary,
         ROW_NUMBER() over (order by Salary asc) as asc_salary
from Table_Name 
) as a
where asc_salary in (desc_salary,desc_salary+1, desc_salary-1)

Upvotes: 1

Dostrelith
Dostrelith

Reputation: 962

There is no MEDIAN() function in MySQL but there is a somewhat simple way to calculate it as demonstrated on this website:

https://www.eversql.com/how-to-calculate-median-value-in-mysql-using-a-simple-sql-query/

Assuming your table is named salaries:

SET @rowindex := -1;
 
SELECT
   AVG(salary)
FROM
   (SELECT @rowindex:=@rowindex + 1 AS rowindex,
           salaries.salary AS salary
    FROM salaries
    ORDER BY salaries.salary) AS s
WHERE
s.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));

Explanation:

  1. Let's start with the internal subquery - the subselect assigns @rowindex as an incremental index for each salary that is selected, and sorts the salaries.
  2. Once we have the sorted list of salaries, the outer query will fetch the middle items in the array. If the array contains an odd number of items, both values will be the single middle value.
  3. Then, the SELECT clause of the outer query returns the average of those two values as the median value.

Upvotes: 1

Related Questions