SQL 计算中位数|极客教程

SQL 计算中位数，你想计算某个数值列的中位数（即按顺序排列的一组数据中居于中间位置的数）。例如，你希望知道 DEPTNO 等于 20 的部门里员工工资的中位数。就如下示例而言，中位数为 2975。

SQL 计算中位数问题描述

你想计算某个数值列的中位数（即按顺序排列的一组数据中居于中间位置的数）。例如，你希望知道 DEPTNO 等于 20 的部门里员工工资的中位数。就如下示例而言，中位数为 2975。

select sal
  from emp
 where deptno = 20
 order by sal
　
       SAL
----------
       800
      1100
      2975
      3000
      3000

SQL 计算中位数解决方案

除了 Oracle 的解决方案（Oracle 有可以计算中位数的函数），其他解决方案都基于 David Rozenshtein、Anatoly Abramovich 和 Eugene Birger 在 Optimizing Transact-SQL 一书中记载的方法。窗口函数的出现使得我们有了比传统的自连接查询更为高效的做法。
DB2
使用窗口函数 COUNT(*) OVER 和 ROW_NUMBER 查找中位数。

 1  select avg(sal)
 2    from (
 3  select sal,
 4         count(*) over() total,
 5         cast(count(*) over() as decimal)/2 mid,
 6         ceil(cast(count(*) over() as decimal)/2) next,
 7         row_number() over (order by sal) rn
 8    from emp
 9   where deptno = 20
10         ) x
11   where ( mod(total,2) = 0
12           and rn in ( mid, mid+1 )
13         )
14      or ( mod(total,2) = 1
15           and rn = next
16         )

MySQL 和 PostgreSQL
使用自连接查询查找中位数。

 1  select avg(sal)
 2    from (
 3  select e.sal
 4    from emp e, emp d
 5   where e.deptno = d.deptno
 6     and e.deptno = 20
 7   group by e.sal
 8  having sum(case when e.sal = d.sal then 1 else 0 end)
 9                            >= abs(sum(sign(e.sal - d.sal)))
10         )

Oracle
使用函数 MEDIAN（Oracle Database 10g）或者 PERCENTILE_CONT（Oracle 9i）。

1 select median(sal)
2   from emp
3  where deptno=20
　
1 select percentile_cont(0.5)
2         within group(order by sal)
3   from emp
4  where deptno=20

DB2 解决方案也适用于 Oracle 8i。对于 Oracle 8i **之前的版本，就要采用 MySQL 和 PostgreSQL 的解决方案。
**SQL Server
使用窗口函数 COUNT(*) OVER 和 ROW_NUMBER 查找中位数。

 1  select avg(sal)
 2    from (
 3  select sal,
 4         count(*) over() total,
 5         cast(count(*) over() as decimal)/2 mid,
 6         ceiling(cast(count(*)over() as decimal)/2) next,
 7         row_number() over(order by sal) rn
 8    from emp
 9   where deptno = 20
10         ) x
11   where ( total%2 = 0
12           and rn in ( mid, mid+1 )
13         )
14      or ( total%2 = 1
15           and rn = next
16         )

SQL 计算中位数扩展知识

DB2 和 SQL Server
DB2 和 SQL Server 的解决方案之间的唯一不同之处在于一个语法细节：SQL Server 的取模运算符是 %，DB2 则使用 MOD 函数。除此之外，二者并无二致。内嵌视图 X 返回 TOTAL、MID 和 NEXT 三种不同的总数以及 ROW_NUMBER 函数生成的 RN。这些额外生成的列有助于查找中位数。仔细观察内嵌视图 X 返回的结果集，并看看这些列表示什么。

select sal,
       count(*) over() total,
       cast(count(*)over() as decimal)/2 mid,
       ceil(cast(count(*)over() as decimal)/2) next,
       row_number()over(order by sal) rn
  from emp
 where deptno = 20
　
 SAL TOTAL  MID NEXT   RN
---- ----- ---- ---- ----
 800     5  2.5    3    1
1100     5  2.5    3    2
2975     5  2.5    3    3
3000     5  2.5    3    4
3000     5  2.5    3    5

为了找到中位数，SAL 列的值必须按照从小到大的顺序排列。由于 DEPTNO 等于 20 的员工的个数为奇数，因此中位数就是 RN 与 NEXT 相等的那一行的 SAL 值（NEXT 表示大于员工总数除以 2 的商的最小整数）。
如果内嵌视图 X 返回的结果集的行数为奇数，则 WHERE 子句的前半部分（第 11 ～ 13 行）不会命中。如果我们确信内嵌视图 X 返回的结果集的行数始终是奇数，不妨简化为如下的形式。

select avg(sal)
  from (
select sal,
       count(*)over() total,
       ceil(cast(count(*)over() as decimal)/2) next,
       row_number()over(order by sal) rn
  from emp
 where deptno = 20
       ) x
 where rn = next

不幸的是，如果子查询返回的结果集的行数为偶数，上述简化的做法不再适用。该解决方案使用 MID 列来处理偶数行的情况。如果 DEPTNO 等于 30，则内嵌视图 X 的返回结果会包含 6 个员工。

select sal,
       count(*)over() total,
       cast(count(*)over() as decimal)/2 mid,
       ceil(cast(count(*)over() as decimal)/2) next,
       row_number()over(order by sal) rn
  from emp
 where deptno = 30
　
 SAL TOTAL  MID NEXT   RN
---- ----- ---- ---- ----
 950     6    3    3    1
1250     6    3    3    2
1250     6    3    3    3
1500     6    3    3    4
1600     6    3    3    5
2850     6    3    3    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 列表中，以观察查询结果，并验证这一点。

select e.sal,
       sum(case when e.sal=d.sal
                then 1 else 0 end) as cnt1,
       abs(sum(sign(e.sal - d.sal))) as cnt2
  from emp e, emp d
 where e.deptno = d.deptno
   and e.deptno = 20
 group by e.sal
　
 SAL CNT1 CNT2
---- ---- ----
 800    1    4
1100    1    2
2975    1    0
3000    4    6

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 的中间）。