用MySQL计算中位数最简单(希望不会太慢)的方法是什么?我已经使用AVG(x)来寻找平均值,但我很难找到一个简单的方法来计算中位数。现在,我将所有的行返回到PHP,进行排序,然后选择中间的行,但是肯定有一些简单的方法可以在一个MySQL查询中完成它。

示例数据:

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) == 5。


当前回答

我没有将这个解决方案的性能与这里发布的其他答案进行比较,但我发现这个解决方案是最容易理解的,并且涵盖了计算中位数的全部数学公式。换句话说,这个解决方案对于偶数和奇数数据集足够健壮:

SELECT CASE 
-- odd-numbered data sets:
WHEN MOD(COUNT(*), 2) = 1 THEN (SELECT median.<value> AS median
FROM
(SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM <data>) t1,
       (SELECT COUNT(*) AS num_records FROM <data>) t2
 WHERE t1.rownum =(t2.num_records) / 2) as median)
-- even-numbered data sets:
ELSE (select (low_bound.<value> + up_bound.<value>) / 2 AS median
FROM
(SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM <data>) t1,
       (SELECT COUNT(*) AS num_records FROM <data>) t2
 WHERE t1.rownum =(t2.num_records - 1) / 2) as low_bound,
 (SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM station) t1,
       (SELECT COUNT(*) AS num_records FROM data) t2
 WHERE t1.rownum =(t2.num_records + 1) / 2) as up_bound)
END
FROM <data>

其他回答

另一个对Velcrow答案的重复,但使用了一个中间表,并利用了用于行编号的变量来获得计数,而不是执行额外的查询来计算它。还开始计数,以便第一行是第0行,以便简单地使用Floor和Ceil选择中位数行。

SELECT Avg(tmp.val) as median_val
    FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum
              FROM data as inTab,  (SELECT @rows := -1) as init
              -- Replace with better where clause or delete
              WHERE 2 > 1
              ORDER BY inTab.val) as tmp
    WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));

关心奇数的计数-给出中间两个值的平均值。

SELECT AVG(val) FROM
  ( SELECT x.id, x.val from data x, data y
      GROUP BY x.id, x.val
      HAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))
  ) sq

让我们创建一个名为numbers的示例表

这个答案是针对mysql数据库的

在postgres Sql中,它简单地使用per_cont函数

创建表数字( num INT, 频率整数 );

在数字表中插入值

插入数字 (7) 0 (1, 1), (2、3), (1) 3 (9,1), (1, 1), (2、3), (1) 3 (9,1);

——select * from numbers

作为递归num_frequency (num,frequency, i) ( 选择num,频率,1 从数字 UNION ALL 选择num,频率,i + 1 从num_frequency num_frequency的地方。I < num_frequency.frequency )

select * (max(当numbers=lower_limit时,则num else null end)/2 +max(当数字=upper_limit时,则num else null end)/2)作为中位数 从( select *, total_number % 2, 情况下 当total_number%2=0时,total_number/2 Else (total_number+1)/2 end as lower_limit, 情况下 当total_number%2=0时,total_number/2+1 其他(total_number + 1) / 2 结束为upper_limit

从( Select *,max(numbers) over() as total_number from ( Select num,row_number() over(按num排序) 作为num_frequency中的数字 b) b) b)

安装和使用本mysql统计函数:http://www.xarg.org/2012/07/statistical-functions-in-mysql/

之后,计算中值就很简单了:

SELECT median(val) FROM data;

你可以使用这里的用户定义函数。