计算不包含最大值和最小值的均值

ehxz

问题

计算平均数，但希望排除最大和最小值，以（希望能）减少数据畸偏造成的影响。例如，计算除最高和最低工资外的所有职员的平均工资。

解决方案

MySQL和PostgreSQL

使用子查询排除最高和最低值：

1  select avg(sal)

2    from emp

3   where sal not in (

4      (select min(sal) from emp),

5      (select max(sal) from emp)

6   )

DB2、Oracle和SQL Server

使用内联视图及窗口函数MAX OVER和MIN OVER，生成一个结果集，可以很容易地从中剔除最大和最小值：

1  select avg(sal)

2    from (

3  select sal, min(sal)over() min_sal, max(sal)over() max_sal

4    from emp

5         ) x

6   where sal not in (min_sal,max_sal)

讨论

MySQL和PostgreSQL

子查询返回表中的最高工资和最低工资。针对返回的值使用NOT IN，就可以从平均值中排除最高工资和最低工资。记住，如果存在重复（多个职员都是最高或最低工资），那么他们都会被排除在平均值之外。如果只想排除一个最高和最低值，只需从SUM中减去它们，再做除法：

select (sum(sal)-min(sal)-max(sal))/(count(*)-2)

  from emp

DB2、Oracle和SQL Server

内联视图X将返回所有工资，其中包括最高工资和最低工资：

select sal, min(sal)over() min_sal, max(sal)over() max_sal

  from emp

      SAL   MIN_SAL   MAX_SAL

--------- --------- ---------

      800       800      5000

     1600       800      5000

     1250       800      5000

     2975       800      5000

     1250       800      5000

     2850       800      5000

     2450       800      5000

     3000       800      5000

     5000       800      5000

     1500       800      5000

     1100       800      5000

      950       800      5000

     3000       800      5000

     1300       800      5000

从每一行都可以访问最高工资和最低工资，因此，要找出哪些工资是最高工资的和/或最低工资的非常简单。外层查询会对内联视图X返回的行作筛选，这样，所有与MIN_SAL和MAX_SALAN相匹配的行都会从平均值中排除掉。