您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 計算中位數的三種高效方法詳解

編輯：MySQL 綜合教學

MySQL 計算中位數的三種高效方法詳解

在資料分析中，中位數能更真實反映資料分布，避免極端值干擾。MySQL 本身未提供內建中位數函數，但可透過 SQL 技巧實現。本文介紹三種計算中位數的方法，並附帶效能優化建議。

方法一：自連接排名法（經典通用）

核心邏輯：對每筆資料計算其在排序中的排名，再取中間排名對應的值。

-- 建立測試表
CREATE TABLE `income` (
  `name` VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO income (name, income) VALUES
('麻子', 20000), ('李四', 12000), ('張三', 10000),
('王二', 16000), ('土豪', 40000);

-- 步驟 1：計算每筆收入的排名
SELECT a1.name, a1.income, COUNT(*) AS rank
FROM income AS a1, income AS a2
WHERE a1.income < a2.income
   OR (a1.income = a2.income AND a1.name <= a2.name)
GROUP BY a1.name, a1.income
ORDER BY rank;

-- 步驟 2：取得中位數排名
SELECT (COUNT(*) + 1) DIV 2 FROM income;

-- 步驟 3：查詢中位數收入
SELECT income AS median FROM (
  SELECT a1.income, COUNT(*) AS rank
  FROM income AS a1, income AS a2
  WHERE a1.income < a2.income
     OR (a1.income = a2.income AND a1.name <= a2.name)
  GROUP BY a1.income
  ORDER BY rank
) AS a3
WHERE rank = (SELECT (COUNT(*) + 1) DIV 2 FROM income);

方法二：用戶變量排名法（效能較佳）

利用會話變量模擬 ROW_NUMBER()，避免自連接的笛卡兒積，適合大數據量。

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 income
  ORDER BY income DESC
) AS a1
WHERE a1.rank = (SELECT (COUNT(*) + 1) DIV 2 FROM income);

方法三：MySQL 8.0+ 窗口函數（最簡潔高效）

若使用 MySQL 8.0 或以上版本，可直接使用 PERCENTILE_CONT 或 ROW_NUMBER 窗口函數。

-- 使用 ROW_NUMBER
WITH ranked AS (
  SELECT income,
         ROW_NUMBER() OVER (ORDER BY income) AS rn,
         COUNT(*) OVER () AS total
  FROM income
)
SELECT income AS median
FROM ranked
WHERE rn = (total + 1) DIV 2;

-- 或使用 PERCENTILE_CONT (近似中位數)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY income) AS median
FROM income;