How to calculate the median of a set of data in MySQL

To get the median of a set of data (such as the median income of a certain region or a certain company), we generally need to subdivide this task into 3 small tasks:

1 . Sort the data and give each row of data its ranking among all data;

2. Find the median ranking number;

3. Find the corresponding middle ranking The value of ;

The following takes the monthly income of employees of a company as an example to illustrate the use of some complex MySQL statements.

Method 1

Create a test table

First create an income table, the table creation statement is:

CREATE TABLE IF NOT EXISTS `employee` (
  `id`     INT                  AUTO_INCREMENT PRIMARY KEY,
  `name`   VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT         NOT NULL DEFAULT '0'
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
INSERT INTO `employee` (`name`, `income`)
VALUES ('麻子', 20000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('李四', 12000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('张三', 10000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('王二', 16000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('土豪', 40000);

Complete Task 1

Sort the data and give each row of data its ranking among all data:

SELECT t1.name, t1.income, COUNT(*) AS rank
FROM employee AS t1,
     employee AS t2
WHERE t1.income < t2.income
   OR (t1.income = t2.income AND t1.name <= t2.name)
GROUP BY t1.name, t1.income
ORDER BY rank;

The query result is:

Complete small task 2

Find the median ranking number:

SELECT (COUNT(*) + 1) DIV 2 as rank
FROM employee;

The query result is:

Complete small task 3

SELECT income AS median
FROM (SELECT t1.name, t1.income, COUNT(*) AS rank
      FROM employee AS t1,
           employee AS t2
      WHERE t1.income < t2.income
         OR (t1.income = t2.income AND t1.name <= t2.name)
      GROUP BY t1.name, t1.income
      ORDER BY rank) t3
WHERE rank = (SELECT (COUNT(*) + 1) DIV 2 FROM employee)

The query result is:

At this point, we have found how to get the median from a set of data.

Method 2

Next, let’s introduce another method of optimizing ranking statements.

We all know how to sort a set of data. In this example, the implementation method is as follows:

SELECT name, income
FROM employee
ORDER BY income DESC

The query result is:

##Can we go one step further and add a column to the query results? The data in this column is the ranking?

We can achieve this goal through 3 custom variables:

The first variable is used to record the income of the current row of data

The second variable Used to record the income of the previous row of data

The third variable is used to record the ranking of the current row of data

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;
SELECT `name`,
       @curr_income := income                                      AS income,
       @rank := if(@prev_income != @curr_income, @rank + 1, @rank) AS rank,
       @prev_income := @curr_income                                AS dummy
FROM employee
ORDER BY income DESC

The query results are as follows:

Then find the ranking number of the median, and further find out the median income:

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;
SELECT income AS median
FROM (SELECT `name`,
             @curr_income := income                                      AS income,
             @rank := if(@prev_income != @curr_income, @rank + 1, @rank) AS rank,
             @prev_income := @curr_income                                AS dummy
      FROM employee
      ORDER BY income DESC) AS t1
WHERE t1.rank = (SELECT (COUNT(*) + 1) DIV 2 FROM employee)

The query result is:

At this point, We found two ways to solve the median problem. Sprinkle flowers.

Recommended: "

mysql tutorial"

The above is the detailed content of How to calculate the median of a set of data in MySQL. For more information, please follow other related articles on the PHP Chinese website!