中国领先的IT技术网站
|
|

如何使用简单的SQL查询在MySQL中计算中位数

数值型数组的中位数是在数据排序后位于数组中间项的值。如果数组有偶数个元素,中位数就是最中间的两个数值的平均数。中位数对于了解“我的值是否位于中间?”

作者:佚名来源:开源中国|2017-10-09 12:56

开发者大赛路演 | 12月16日,技术创新,北京不见不散


什么是中位数?

数值型数组的中位数是在数据排序后位于数组中间项的值。如果数组有偶数个元素,中位数就是最中间的两个数值的平均数。

中位数对于了解“我的值是否位于中间?”非常有用。比如,我在学校的最后一次考试中得了 D (或 80),那么我的在全班同学中的排名是否在前 50%?

如何使用简单的SQL查询在MySQL中计算中位数

举一个具体的例子,我们看看从学校的这些测试分数中找到中位数的过程:[55, 80, 95, 100, 99, 70, 60]。

  • 首先,对数组排序:[55, 80, 95, 100, 99, 70, 60] ===> [55, 60, 70, 80, 95, 99, 100].
  • 这个数组包含 7 个元素,不是偶数,所以中位数是 (7 / 2 + 1),即第 4 项的值 => 80。

在 MySQL 中计算某列数据的中位数

很不幸,MySQL 并未内置计算列数据中位数的函数。因此,我们需要自己创建查询。

假如我们想从“grades”列中查找中位数。来看看我们要创建的查询是什么算法:

  • 对“grades”排序并为排序后的每一行附加序号(行号)
  • 如果总行数是奇数,找到位于有序列表中间位置的值。
  • 如果总行数是偶数,找到位于有序列表中间两个位置的值。
  • 计算上面第 (3) 步和第 (4) 取得值的平均值。
  • 返回这个平均值,它就是中位数。

那么这样的查询该怎么写?

  1. SET @rownum := -1;   
  2. SELECT 
  3.    AVG(g.grade) 
  4. FROM 
  5.    (SELECT @rowindex:=@rowindex + 1 AS rowindex, 
  6.            grades.grade AS grade 
  7.     FROM grades 
  8.     ORDER BY grades.grade) AS g 
  9. WHERE 
  10. t.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2)); 

解释一下:

  1. 先从内部的一个子查询开始 —— 为 @rowindex 赋值的子查询会为每个成绩赋予一个自增后的序号,并对 grades 排序 [译者注:按逻辑和代码是先排序再赋予序号]。
  2. 有了排好序的成绩列表之后,外层查询会获取位中间位置的值。如果数组元素是奇数个,两个值都是取的位于正中那个值。
  3. 然后,外层查询的 SELECT 子句返回上述两个值的平均值作为中位数。

你看,这并不是简单的运行 MEDIAN(column_name),但它可行。我们期待 MySQL 会决定将这一功能添加到内置产品中,这将简化获取中位数的查询。

【编辑推荐】

  1. SQL Server正式出现在Linux平台上
  2. 7个原因告诉你为什么要选择ArangoDB “多模型” 数据库?
  3. 10道Hadoop面试真题及解题思路
  4. MySQL技能提升篇 — sqlyog高级应用
  5. 深度解读 | 阿里云新一代关系型数据库 PolarDB
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网管员必读—网络应用

本书是一本介绍当前主流计算机网络应用技术的工具图书,全面总结了当前最主流、最基础的计算机网络应用,包括局域网和互联网应用两方面。在...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊