oracle中去除最大最小值后求方差用什么函数

oracle中去除最大最小值后求方差用什么函数

（1）oracle使用keep分析函数取最值记录
　　-- 取工资sal最大的雇员姓名及其工资，以及工资sal最少的雇员姓名及其工资select
　　deptno,
　　empno,
　　ename,
　　sal,
　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_salfrom emp
　　where deptno=10
　　结果如下：
　　技术分享
　　从语句中可以看到，ename和sal都是用的max()，这样做的目的是为了去除由于keep()函数得到的有重复值的数据结果集。这样用有一个弊端，加入部门20有两个相同的最大SAL的人，部门30有两个相同的最小SAL的人，如果按照这种方法取出来的数据，就不一定准确了，重复的人会被去除掉。
　　我们用下面的语句来修改一下：
　　select
　　deptno,
　　empno,
　　ename,
　　sal,
　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal,wmsys.wm_concat(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as 工资最高的人,wmsys.wm_concat(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as 工资最低的人from emp
　　where deptno=20
　　order by 1, 2 ;
　　我们新增了两个列：工资最高的人，工资最低的人。执行看一下结果：
　　技术分享
　　可以看到，deptno=20时，SCOTT和FORD两个人的工资SAL都是3000，如果用MAX()就只能取出其中一个人的姓名，显然是不对的。
　　然后，我们再来看一下deptno=30时的情况：
　　select
　　deptno,
　　empno,
　　ename,
　　sal,
　　max(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal_man,max(sal) keep(dense_rank FIRST order by sal) over (partition by deptno) as min_sal,max(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal_man,max(sal) keep(dense_rank LAST order by sal) over (partition by deptno) as max_sal,wmsys.wm_concat(ename) keep(dense_rank LAST order by sal) over (partition by deptno) as 工资最高的人,wmsys.wm_concat(ename) keep(dense_rank FIRST order by sal) over (partition by deptno) as 工资最低的人from emp
　　where deptno=30
　　order by 1, 2 ;
　　deptno=30时的结果如下：
　　技术分享
　　可以看到，deptno=30时，WARD和MARTIN两人的工资最小且均为1250，如果用MAX()的方式，就只能取出其中一个人的名称。
　　这就是因为keep()取出来的数据集是包含多个数据结果的，所以，在语句中使用了wmsys.wm_concat()函数，该函数的作用是以逗号分隔连接列的值。
　　注：wm_concat()的功能有点儿类似分析函数listagg() within group() 。
　　（2）使用SQL子查询和聚合函数，查询出最大值和最小值-- 使用子查询查询出最大值和最小值
　　select * from
　　(
　　select
　　deptno,
　　listagg(ename,‘,‘) within group (order by deptno) as dept_max_ename,max(sal) as dept_max_sal
　　from emp
　　where (deptno,sal) in (select deptno, max(sal) as max_sal from emp group by deptno)group by deptno
　　) A
　　inner join
　　(
　　select
　　deptno,
　　listagg(ename,‘,‘) within group (order by deptno) as dept_min_ename,min(sal) as dept_min_sal
　　from emp
　　where (deptno,sal) in (select deptno, min(sal) as min_sal from emp group by deptno)group by deptno
　　) B
　　on A.deptno = B.deptno
　　结果如下：
　　技术分享
　　在这个方案里面，还使用了listagg()分析函数将最值有重复姓名的人合并在一起，用wm_concat()函数替代listagg()也可以wm_concat(ename) as dept_max_ename,
　　wm_concat(ename) as dept_min_ename,

我。。知。。道 加。。我。。私。。聊