mysql 中间值_SQL语句-计算中间值

问题:计算一列数字值的中间值(中间值就是一组有序元素中间成员的值)。例如，查找DEPTNO 20中工资的中间数。如下列工资：

select sal

from emp

where deptno = 20

order by sal

SAL

----------

800

1100

2975

3000

3000

中间数为2975。

解决方案

除了Oracle解决方案(用函数计算中间数)之外，其他所有解决方案都是以Rozenshtein、Abramovich和Birger在 Optimizing Transact-SQL: Advanced Programming Techniques (SQL Forum Press, 1997)中描述的方法为基础的。与传统的自联接相比，窗口函数的引入，使解决方案更为有效。

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(Oracle9i Database)：

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

对于Oracle8i Database，使用DB2解决方案。对于Oracle8i Database之前的版本，可以采用PostgreSQL/MySQL解决方案。

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          )

讨论

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(即大于职员总数除以2的最小整数)。

如果结果集返回奇数行，WHERE子句的第一部分(第11～13行)条件不满足。如果能够确定结果集是奇数行，则可以简化为：

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和MID + 1两行的平均数。

MySQL和PostgreSQL

根据第一个自联接表EMP计算中间数，而该表返回了所有工资的笛卡儿积(GROUP BY    E.SAL会去掉重复值)。HAVING子句使用函数SUM计算E.SAL等于D.SAL的次数；如果这个值大于等于E.SAL且大于D.SAL次数，那 么该行就是中间数。在SELECT列表中加入SUM就可以观察到这种情况：

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或Oracle9i Database中，可以使用Oracle提供的函数计算中间数；对于Oracle8i Database，可以采用DB2解决方案；其他版本必须采用PostgreSQL解决方案。显然可以用MEDIAN函数计算中间值，用 PERCENTILE_CONT函数也可以计算中间值就不那么显而易见了。传递给PERCENTILE_CONT的值0.5是一个百分比值。子句 WITHIN GROUP (ORDER BY SAL)确定PERCENTILE_CONT要搜索哪些有序行(记住，中间值就是一组已排序值的中间值)。返回的值就是搜索的有序行中符合给定百分比(在 这个例子中是0.5，因为其两个边界值分别为0和1)的值。