不同数据库 Oracle, db2, sql server求中位数

问题:计算一列数字值的中间值（中间值就是一组有序元素中间成员的值）。例如，查找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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14568827/viewspace-711540/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14568827/viewspace-711540/