問題描述

使用 MySQL 計算中位數的最簡單(希望不會太慢)方法是什么?我已經使用 AVG(x) 來找到平均值，但我很難找到一種計算中位數的簡單方法.現在，我將所有行返回給 PHP，進行排序，然后選擇中間的行，但肯定有一些簡單的方法可以在單個 MySQL 查詢中執行此操作.

What's the simplest (and hopefully not too slow) way to calculate the median with MySQL? I've used AVG(x) for finding the mean, but I'm having a hard time finding a simple way of calculating the median. For now, I'm returning all the rows to PHP, doing a sort, and then picking the middle row, but surely there must be some simple way of doing it in a single MySQL query.

示例數據:

id | val
--------
 1    4
 2    7
 3    2
 4    2
 5    9
 6    8
 7    3

對 val 排序給出 2 2 3 4 7 8 9，所以中位數應該是 4，而 SELECT AVG(val) which == 5.

Sorting on val gives 2 2 3 4 7 8 9, so the median should be 4, versus SELECT AVG(val) which == 5.

推薦答案

在 MariaDB/MySQL 中:

In MariaDB / MySQL:

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
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen 指出，在第一遍之后，@rownum 將包含總行數.這可用于確定中位數，因此不需要第二遍或連接.

Steve Cohen points out, that after the first pass, @rownum will contain the total number of rows. This can be used to determine the median, so no second pass or join is needed.

還有 AVG(dd.val) 和 dd.row_number IN(...) 用于在記錄數為偶數時正確生成中位數.推理:

Also AVG(dd.val) and dd.row_number IN(...) is used to correctly produce a median when there are an even number of records. Reasoning:

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

最后，MariaDB 10.3.3+ 包含一個 MEDIAN 函數

這篇關于用 MySQL 計算中位數的簡單方法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！