用 MySQL 计算中位数的简单方法

问题描述

使用 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模板网！