在 MySQL 中，如何計算一組資料的中位數？

jxlwqq發表於2018-10-17
MySql

要得到一組資料的中位數（例如某個地區或某家公司的收入中位數），我們一般要將這一任務細分為 3 個小任務：

  1. 將資料排序，並給每一行資料給出其在所有資料中的排名；
  2. 找出中位數的排名數字；
  3. 找出中間排名對應的值；

下面以某公司員工月收入為例，示例 MySQL 的一些複雜語句的使用。

方法一

建立測試表

首先建立一個收入表，建表語句為：

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

完成任務1

將資料排序，並給每一行資料給出其在所有資料中的排名：

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;

查詢結果為：

name income rank
土豪 40000 1
麻子 20000 2
王二 16000 3
李四 12000 4
張三 10000 5

完成小任務2

找出中位數的排名數字：

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

查詢結果為：

rank
3

完成小任務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)

查詢結果為：

median
16000

至此，我們就找到了如何從一組資料中獲得中位數的方法。

方法二

下面，來介紹另外一種最佳化排名語句的方法。

我們都知道如何給一組資料做排序操作，在本例中，實現方法如下：

SELECT name, income
FROM employee
ORDER BY income DESC

查詢結果為：

name income
土豪 40000
麻子 20000
王二 16000
李四 12000
張三 10000

那我們可不可以更進一步，對查詢出的結果加一列，這一列的資料為排名呢？

我們可以透過3個自定義變數的方法來實現這一目標：

第一個變數用來記錄當前行資料的收入
第二個變數用來記錄上一行資料的收入
第三個變數用來記錄當前行資料的排名

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

查詢結果如下：

name income rank dummy
土豪 40000 1 40000
麻子 20000 2 20000
王二 16000 3 16000
李四 12000 4 12000
張三 10000 5 10000

然後再找出中位數的排名數字，進一步找出收入的中位數：

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)

查詢結果為：

median
16000

至此，我們找了兩種方法來解決中位數的問題。撒花。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章