计算不包含最大值和最小值的均值
问题计算平均数，但希望排除最大和最小值，以（希望能）减少数据畸偏造成的影响。例如，计算除最高和最低工资外的所有职员的平均工资。
解决方案
MySQL和PostgreSQL
使用子查询排除最高和最低值：
1select 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，生成一个结果集，可以很容易地从中剔除最大和最小值：
1select avg(sal)
2 from (
3select 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相匹配的行都会从平均值中排除掉。
页:
[1]