Einfache Möglichkeit, den Median mit MySQL zu berechnen

Question

Was ist der einfachste (hoffentlich nicht zu langsame) Weg, den Median mit MySQL zu berechnen? Ich verwende AVG(x), um den Mittelwert zu ermitteln, habe jedoch Probleme, eine einfache Möglichkeit zur Berechnung des Medians zu finden. Im Moment gebe ich alle Zeilen an PHP zurück, sortiere sie und wähle dann die mittleren Zeilen aus, aber es muss eine einfache Möglichkeit geben, dies in einer einzigen MySQL-Abfrage zu tun.

Beispieldaten:

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

Sortierung val ergibt 2 2 3 4 7 8 9, daher sollte der Median 4 sein. Und ;SELECT AVG(val) == 5.

Answer 1

我刚刚在评论中在线找到了另一个答案：

确保您的列有良好的索引，并且索引用于过滤和排序。与解释计划进行验证。

select count(*) from table --find the number of rows

计算“中位数”行号。也许使用：median_row = Floor(count / 2)。

然后从列表中选择它：

select val from table order by val asc limit median_row,1

这应该返回一行，其中包含您想要的值。

Answer 2

在 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 将包含总行数。这可用于确定中位数，因此不需要第二次传递或连接。

此外，AVG(dd.val) 和 dd.row_number IN(...) 用于在存在偶数条记录时正确生成中位数。推理：

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 函数