SQL 计算中位数,你想计算某个数值列的中位数(即按顺序排列的一组数据中居于中间位置的数)。例如,你希望知道 DEPTNO
等于 20 的部门里员工工资的中位数。就如下示例而言,中位数为 2975。
SQL 计算中位数 问题描述
你想计算某个数值列的中位数(即按顺序排列的一组数据中居于中间位置的数)。例如,你希望知道 DEPTNO
等于 20 的部门里员工工资的中位数。就如下示例而言,中位数为 2975。
SQL 计算中位数 解决方案
除了 Oracle 的解决方案(Oracle 有可以计算中位数的函数),其他解决方案都基于 David Rozenshtein、Anatoly Abramovich 和 Eugene Birger 在 Optimizing Transact-SQL 一书中记载的方法。窗口函数的出现使得我们有了比传统的自连接查询更为高效的做法。
DB2
使用窗口函数 COUNT(*) OVER
和 ROW_NUMBER
查找中位数。
MySQL 和 PostgreSQL
使用自连接查询查找中位数。
Oracle
使用函数 MEDIAN
(Oracle Database 10g)或者 PERCENTILE_CONT
(Oracle 9i)。
DB2 解决方案也适用于 Oracle 8i。对于 Oracle 8i **之前的版本,就要采用 MySQL 和 PostgreSQL 的解决方案。
**SQL Server
使用窗口函数 COUNT(*) OVER
和 ROW_NUMBER
查找中位数。
SQL 计算中位数 扩展知识
DB2 和 SQL Server
DB2 和 SQL Server 的解决方案之间的唯一不同之处在于一个语法细节:SQL Server 的取模运算符是 %
,DB2 则使用 MOD
函数。除此之外,二者并无二致。内嵌视图 X
返回 TOTAL
、MID
和 NEXT
三种不同的总数以及 ROW_NUMBER
函数生成的 RN
。这些额外生成的列有助于查找中位数。仔细观察内嵌视图 X
返回的结果集,并看看这些列表示什么。
为了找到中位数,SAL
列的值必须按照从小到大的顺序排列。由于 DEPTNO
等于 20 的员工的个数为奇数,因此中位数就是 RN
与 NEXT
相等的那一行的 SAL
值(NEXT
表示大于员工总数除以 2 的商的最小整数)。
如果内嵌视图 X
返回的结果集的行数为奇数,则 WHERE
子句的前半部分(第 11 ~ 13 行)不会命中。如果我们确信内嵌视图 X
返回的结果集的行数始终是奇数,不妨简化为如下的形式。
不幸的是,如果子查询返回的结果集的行数为偶数,上述简化的做法不再适用。该解决方案使用 MID
列来处理偶数行的情况。如果 DEPTNO
等于 30,则内嵌视图 X
的返回结果会包含 6 个员工。
由于一共有偶数行数据,因此中位数就变成了其中两行数据的平均值,相关的两行是 RN
等于 MID
的那一行以及 RN
等于 MID+1
的那一行。
MySQL 和 PostgreSQL
首先通过自连接 EMP
表来计算中位数,自连接查询会返回所有工资值的笛卡儿积(但是针对 E.SAL
做 GROUP BY
之后会去掉重复值)。HAVING
子句使用 SUM
函数来计算 E.SAL
和 D.SAL
相等的次数。如果它们相等的次数不小于 E.SAL
大于 D.SAL
的次数,则对应的行就是中位数。不妨把 SUM
函数添加到 SELECT
列表中,以观察查询结果,并验证这一点。
Oracle
对于 Oracle Database 10g 或者 Oracle 9i,可以使用 Oracle 中的函数计算中位数。对于 Oracle 8i,不妨使用 DB2 解决方案。对于更早的 Oracle 版本,只能采用 PostgreSQL 解决方案。MEDIAN
函数明显是用于计算中位数的,而 PERCENTILE_CONT
函数的用途看起来就不那么直观。传递给 PERCENTILE_CONT
函数的参数 0.5 其实是一个百分位数值。WITHIN GROUP (ORDER BY SAL)
子句会生成顺序排列的行数据,以便于 PERCENTILE_CONT
函数搜索。(注意,中位数就是在一组顺序排列的数值里居于中间位置的那个数。)最后的返回值是顺序排列的行数据里落入指定百分位的数值(本例中的百分位数值是 0.5,居于边界值 0 和 1 的中间)。