如何在 MySQL 中计算中位数

Gustavo du Mortier 2023年1月30日 2020年11月24日
  1. 在 MySQL 中计算中位数的最准确方法
  2. 使用 UDF(用户自定义函数)为 MySQL 添加功能
如何在 MySQL 中计算中位数

在本教程中,我们将展示几种在 MySQL 中高效计算一系列数字的中位数的方法。

MySQL 没有一个内置的函数来计算一系列数字的统计中位数。这就是为什么,如果你需要计算中位数,你必须使用自定义代码来计算。

中位数是你要分析的一系列数字的中间元素。如果你使用大样本,并且没有有效地进行检测,检测这个元素可能是一个缓慢的过程。在某些情况下,这个过程也会很棘手。例如,一些算法可能对奇数集有效,但对偶数集无效,反之亦然。

在 MySQL 中计算中位数的最准确方法

假设你有一个表 dataset,其中有一列类型为 float 的 MyNumber,你需要得到 MyNumber 数据系列的中位数。用一种在所有情况下都有效的算法来计算中位数,最准确的方法是使用下面的脚本。

SELECT AVG(mid_vals) AS 'median' FROM (
 SELECT tab1.MyNumber AS 'mid_vals' FROM
  (
   SELECT @row:=@row+1 AS 'row', a.MyNumber
   FROM dataset AS a, (SELECT @row:=0) AS r
   ORDER BY a.MyNumber
  ) AS tab1,
  (
   SELECT COUNT(*) as 'count'
   FROM dataset x
  ) AS tab2
  WHERE tab1.row >= tab2.count/2 and tab1.row <= ((tab2.count/2) +1)) AS tab3;

如果表的行数较多,你需要在列 MyNumber 上建立一个索引,并确保该索引用于过滤和排序,以保证语句的良好执行。

使用 UDF(用户自定义函数)为 MySQL 添加功能

数学和统计函数是非常常见的,如果你需要使用中位数等函数,你不应该重新发明轮子。因此,你可以抓住一个经过良好测试的用户定义函数来完成这项工作。

这个网站上,你会发现一堆你可以免费使用的 UDF 来为 MySQL 添加统计功能。其中一个 UDF 是 median 函数。它有一个输入参数和一个输出参数,类型都是 REAL。输入参数是包含计算中位数的一组数字的列,输出参数是计算出的中位数。

下载好源码后,用这个命令添加 UDF。

CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

然后,你可以在 SELECT 语句中使用这个函数,就像这样:

SELECT MEDIAN(MyNumber) FROM dataset