Oracle高级分析函数与统计函数结合使用

备注:测试数据库版本为Oracle 11g R2

这个blog我们来聊聊Oracle高级分析函数与统计统计函数结合使用

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

测试数据:

DROP TABLE testa;CREATE  TABLE testa (area   VARCHAR2 (20), month VARCHAR2   (20),amount NUMBER);insert into testa values (\'上海\', \'1\', 199);insert into testa values (\'上海\', \'2\', 199);insert into testa values (\'上海\', \'3\', 155);insert into testa values (\'上海\', \'3\', 155);insert into testa values (\'上海\', \'4\', 125);insert into testa values (\'广州\', \'1\', 75);insert into testa values (\'广州\', \'2\', 67);insert into testa values (\'北京\', \'1\', 235);insert into testa values (\'北京\', \'2\', 330);Commit;

#一.keep函数
keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

keep语法：

min | max(col1) keep (dense_rank first | lastorder by col2) over (partion by col3);

最前是聚合函数，可以是min、max、avg、sum…
col1为要计算的列；
dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；

解释：返回按照col3分组后，按照col2排序的结果集中第一个或最后一个最小值或最大值col1。
col1和col2列可重复

需求:求员工表每个员工信息及部门最高薪资、最低薪资

--传统sql写法,需要嵌套一层临时表with tmp1 as(select e.deptno,max(e.sal) max_sal,min(e.sal) min_salfrom emp egroup by e.deptno)select e2.deptno,e2.ename,e2.sal,max_sal,min_salfrom emp e2left join tmp1on e2.deptno = tmp1.deptnoORDER BY e2.deptno, e2.sal, e2.ename;--排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求--通过keep函数,无需嵌套子查询,代码逻辑更为简单SELECT Deptno,Ename,Sal,MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_salFROM EmpORDER BY deptno, sal, ename;

SQL> --传统sql写法,需要嵌套一层临时表SQL> with tmp1 as2  (3  select e.deptno,max(e.sal) max_sal,min(e.sal) min_sal4  from emp e5  group by e.deptno6  )7  select e2.deptno,8         e2.ename,9         e2.sal,10         max_sal,11         min_sal12  from emp e213  left join tmp114  on e2.deptno = tmp1.deptno15  ORDER BY e2.deptno, e2.sal, e2.ename;DEPTNO ENAME            SAL    MAX_SAL    MIN_SAL------ ---------- --------- ---------- ----------10 MILLER       1300.00       5000       130010 CLARK        2450.00       5000       130010 KING         5000.00       5000       130020 SMITH         800.00       3000        80020 ADAMS        1100.00       3000        80020 JONES        2975.00       3000        80020 FORD         3000.00       3000        80020 SCOTT        3000.00       3000        80030 JAMES         950.00       2850        95030 MARTIN       1250.00       2850        95030 WARD         1250.00       2850        95030 TURNER       1500.00       2850        95030 ALLEN        1600.00       2850        95030 BLAKE        2850.00       2850        95014 rows selectedSQL> --排名函数只能进行排名,通过排名可以看出最大和最小薪资,同样需要嵌套临时表来完成此类需求SQL> --通过keep函数,无需嵌套子查询,代码逻辑更为简单SQL> SELECT Deptno,2         Ename,3         Sal,4         MIN(Sal) KEEP(DENSE_RANK FIRST ORDER BY sal) OVER(PARTITION BY deptno) max_sal,5         MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) OVER(PARTITION BY deptno) min_sal6    FROM Emp7   ORDER BY deptno, sal, ename;DEPTNO ENAME            SAL    MAX_SAL    MIN_SAL------ ---------- --------- ---------- ----------10 MILLER       1300.00       1300       500010 CLARK        2450.00       1300       500010 KING         5000.00       1300       500020 SMITH         800.00        800       300020 ADAMS        1100.00        800       300020 JONES        2975.00        800       300020 FORD         3000.00        800       300020 SCOTT        3000.00        800       300030 JAMES         950.00        950       285030 MARTIN       1250.00        950       285030 WARD         1250.00        950       285030 TURNER       1500.00        950       285030 ALLEN        1600.00        950       285030 BLAKE        2850.00        950       285014 rows selected

二.求累积销售额

需求:求每个区域每个月的销售额以及累积销售额

--传统写法,通过表连接 t1.month >= t2.month 及group语句解决select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amountfrom testa t1left join testa t2on t1.area = t2.areaand t1.month >= t2.monthgroup by t1.area,t1.month,t1.amountorder by t1.area,t1.month;--通过sum聚合函数与分析函数配合使用,代码更简洁易懂select t1.area,t1.month,t1.amount,sum(t1.amount) over(partition  by t1.area order by month) cum_amountfrom testa t1order by t1.area,t1.month;

SQL> --传统写法,通过表连接 t1.month >= t2.month 及group语句解决SQL> select t1.area,t1.month,t1.amount,sum(t2.amount) cum_amount2  from testa t13  left join testa t24  on t1.area = t2.area5  and t1.month >= t2.month6  group by t1.area,t1.month,t1.amount7  order by t1.area,t1.month;AREA                 MONTH                    AMOUNT CUM_AMOUNT-------------------- -------------------- ---------- ----------北京                 1                           235        235北京                 2                           330        565广州                 1                            75         75广州                 2                            67        142上海                 1                           199        199上海                 2                           199        398上海                 3                           155       1416上海                 4                           125        8338 rows selectedSQL> --通过sum聚合函数与分析函数配合使用,代码更简洁易懂SQL> select t1.area,2         t1.month,3         t1.amount,4         sum(t1.amount) over(partition  by t1.area order by month) cum_amount5  from testa t16  order by t1.area,t1.month;AREA                 MONTH                    AMOUNT CUM_AMOUNT-------------------- -------------------- ---------- ----------北京                 1                           235        235北京                 2                           330        565广州                 1                            75         75广州                 2                            67        142上海                 1                           199        199上海                 2                           199        398上海                 3                           155        708上海                 3                           155        708上海                 4                           125        8339 rows selected