Oracle分析函数-JAVA组培训.doc

Oracle分析函数 Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。Oracle从8.1.6开始提供分析函数。一、基本语法 oracle分析函数的语法： function_name(arg1,arg2,.) over ( ) 说明： 1.partition-clause 数据记录集分组 2.order-by-clause 数据记录集排序 3.windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式: range、row、specifying。二、常用分析函数1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t; DEPTNO EMPNO SAL AVG_SAL- - - - 10 7782 2450 2916.66667 7839 5000 2916.66667 7934 1300 2916.66667 20 7566 2975 2175 7902 3000 2175 7876 1100 2175 7369 800 2175 7788 3000 2175 30 7521 1250 1566.66667 7844 1500 1566.66667 7499 1600 1566.66667 7900 950 1566.66667 7698 2850 1566.66667 7654 1250 1566.666672.count() 对组内数据进行计数 3rank() 和 dense_rank() rank()根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。rank() 和 dense_rank （）函数功能类似，但是有重复值时序号是跳号的。 select deptno,ename,sal,rank() over(partition by deptno order by sal) rn, dense_rank() over(partition by deptno order by sal) dense_rnfrom t; DEPTNO ENAME SAL RN DENSE_RN- - - - - 10 MILLER 1300 1 1 CLARK 2450 2 2 KING 5000 3 3 20 SMITH 800 1 1 ADAMS 1100 2 2 JONES 2975 3 3 SCOTT 3000 4 4 FORD 3000 4 4 30 JAMES 950 1 1 MARTIN 1250 2 2 WARD 1250 2 2 TURNER 1500 4 3 ALLEN 1600 5 4 BLAKE 2850 6 54first_value(显示的表达式),last_value(显示的表达式) 返回组内第一个值，最后一个值。查询每个部门工资高和最低的人 ，一般查询sql：select max(sal),min(sal),deptno from emp groupby deptno；但是无法查询对应人员名称，通过分析函数可以变通实现 select distinct deptno, first_value(ename| : |sal) over ( partitionby deptno order by sal) asfirst, first_value(ename| : |sal) over ( partitionby deptno order by sal desc) aslast from emp; DEPTNO ASFIRST ASLAST- - 30 JAMES : 950 BLAKE : 2850 20 SMITH : 800 FORD : 3000 10 MILLER : 1300 KING : 5000 要说明的last_value()并不类似于max函数，从分析函数语法解析知道，默认窗口下的last_value分析的是当前组的当前的以前数据行以及当前行，因此 first_value(ename| : |sal) over ( partitionby deptno orderby sal) 并不等同于 last_value(ename| : |sal) over (partitionby deptno orderby sal desc) 5min(expression),max(expression) 返回组内最小，最大值 select distinct deptno,min(sal) over (partition by deptno) min_sal， max(sal) over (partition by deptno) max_sal from t; DEPTNO MIN_SAL MAX_SAL- - - 10 1300 5000 30 950 2850 20 800 30006row_number() 返回有序组中的一行的偏移量，也就是对应的序号select deptno,empno,ename,row_number() over (partition by deptno order by sal) rn from t; DEPTNO EMPNO ENAME RN- - - - 10 7934 MILLER 1 7782 CLARK 2 7839 KING 3 20 7369 SMITH 1 7876 ADAMS 2 7566 JONES 3 7788 SCOTT 4 7902 FORD 5 30 7900 JAMES 1 7654 MARTIN 2 7521 WARD 3 7844 TURNER 4 7499 ALLEN 5 7698 BLAKE 67. sum(expression) 计算组中表达式的累计和select distinct deptno,sum(sal) over (partition by deptno) sum_salfrom t; DEPTNO SUM_SAL- - 10 8750 20 10875 30 94008LAG(expression,)和(expression,) 取得前一行和后一行的值可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEADselect empno,ename,sal,sal-beforesal as bdiff,sal-aftersal as adif from(select empno,ename,sal,comm,lag(sal) over (order by empno) beforesal, lead(sal) over (order by empno) aftersal from t); EMPNO ENAME SAL bdiff adif- - - - 7369 SMITH 800 -800 7499 ALLEN 1600 800 350 7521 WARD 1250 -350 -1725 7566 JONES 2975 1725 1725 7654 MARTIN 1250 -1725 -1600 7698 BLAKE 2850 1600 400 7782 CLARK 2450 -400 -550 7788 SCOTT 3000 550 -2000 7839 KING 5000 2000 3500 7844 TURNER 1500 -3500 400 7876 ADAMS 1100 -400 150 7900 JAMES 950 -150 -2050 7902 FORD 3000 2050 1700 7934 MILLER 1300 -1700三、开窗函数介绍range窗口只对日期和数字类型数据生效,只能按照一个字段排序,在对应的字段数据范围内进行搜索.说明范围和排序的升降有关 a、升序，查找本行字段数据值-range值,本行数据值数据集合 b、降序, 查找本行数据值,本行字段数据值+range值,数据集合 select ename,sal,greater_num,lower_num from (select ename,sal,count( ename) over ( order by sal desc range 100 preceding)-1 as greater_num , (count(ename) over ( order by sal asc range 100 preceding)-1) as lower_num from t) a order by sal asc;ENAME SAL GREATER_NUM LOWER_NUM- - - -SMITH 800 0 0JAMES 950 0 0ADAMS 1100 0 0MARTIN 1250 2 1WARD 1250 2 1MILLER 1300 0 2TURNER 1500 1 0ALLEN 1600 0 1CLARK 2450 0 0BLAKE 2850 0 0JONES 2975 2 0SCOTT 3000 1 2FORD 3000 1 2KING 5000 0 02. row 窗口row 窗口是设定分析函数的数据行数，使用该窗口基本没有限制 rows n preceding 即为该窗口数据包括本行前的 n 行以及本行共 (n+1) 行数据 select deptno,ename,sal, min(sal) over (order by deptno rows 5 preceding) sumsal from t;DEPTNO ENAME SAL SUMSAL- - - - 10 CLARK 2450 2450 KING 5000 2450 MILLER 1300 1300 20 JONES 2975 1300 FORD 3000 1300 ADAMS 1100 1100 SMITH 800 800 SCOTT 3000 800 30 WARD 1250 800 TURNER 1500 800 ALLEN 1600 800 JAMES 950 800 BLAKE 2850 950 MARTIN 1250 950三、其它典型示例1行列转换 select deptno, min(decode(seq_num,1,ename,null) as highest, min(decode(seq_num,2,ename,null) as sec_highest, min(decode(seq_num,3,ename,null) as third_highest from (select ename,deptno,seq_num from (select ename,deptno,dense_rank() over (partition by deptno order by sal desc ) as seq_num from t) a where seq_num=3) b group by b.deptno; DEPTNO HIGHEST SEC_HIGHES THIRD_HIGH- - - - 10 KING CLARK MILLER 20 FORD JONES ADAMS 30 BLAKE ALLEN TURNER2.计算运行的累计总数select ename,deptno,sal,sum(sal) over (order by deptno,ename) running_total,sum(sal) over (partition by deptno order by ename) department_total,row_number() over (partition by deptno order by ename) seqfrom t;ENAME DEPTNO SAL RUNNING_TOTAL DEPARTMENT_TOTAL SEQ- - - - - -CLARK 10 2450 2450 2450 1KING 5000 7450 7450 2MILLER 1300 8750 8750 3ADAMS 20 1100 9850 1100 1FORD 3000 12850 4100 2JONES 2975 15825 7075 3SCOTT 3000 18825 10075 4SMITH 800 19625 10875 5ALLEN 30 1600 21225 1600 1BLAKE 2850 24075 4450 2JAMES 950 25025 5400 3MARTIN 1250 26275 6650 4TURNER 150