ORACLEOLAP函数语法的总结.doc

ORACLE OLAP函数语法的总结ORACLE OLAP函数语法的总结2006-08-12 14:35语法: FUNCTION_NAME(,.) OVER ()OLAP函数语法四个部分:1、function本身 用于对窗口中的数据进行操作；2、partitioning clause 用于将结果集分区；3、order by clause 用于对分区中的数据进行排序；4、windowing clause 用于定义function在其上操作的行的集合，即function所影响的范围。一、order by对窗口的影响不含order by的：SQL select deptno,sal,sum(sal) over() from emp;不含order by时，默认的窗口是从结果集的第一行直到末尾。含order by的：SQL select deptno,sal,sum(sal) over(order by deptno) as sumsalfrom emp;当含有order by时，默认的窗口是从第一行直到当前分组的最后一行。二、用于排列的函数SQL select empno, deptno, sal, rank() over (partition by deptno order by sal desc nulls last) as rank, dense_rank() over (partition by deptno order by sal desc nulls last) as dense_rank, row_number() over(partition by deptno order by sal desc nulls last) as row_number from emp;三、用于合计的函数SQL select deptno,sal, 2 sum(sal) over (partition by deptno) as sumsal, 3 avg(sal) over (partition by deptno) as avgsal, 4 count(*) over (partition by deptno) as count, 5 max(sal) over (partition by deptno) as maxsal 6 from emp;四、开窗语句 1、rows窗口: rows 5 preceding 适用于任何类型而且可以order by多列。 SQL select deptno,ename,sal, 2 sum(sal) over (order by deptno rows 2 preceding) sumsal 3 from emp;rows 2 preceding:将当前行和它前面的两行划为一个窗口，因此sum函数就作用在这三行上面SQL select deptno,ename,sal, sum(sal) over (partition by deptno order by ename rows 2 preceding) sumsal from emporder by deptno,ename;加了partiton by分区后之后，rows 2 preceding(窗口)只在当前分区内生效，不会影响分区之外的行。SQL select ename,sal,hiredate, first_value(ename)over (order by hiredate asc rows 5 preceding) first_ename, first_value(hiredate) over (order by hiredate asc rows 5 preceding) first_hiredate from emp order by hiredate asc;order by hiredate asc rows 5 preceding: order by之后，取当前行的前5行+当前行作为窗口(共6行)。2、range unbounded precedingrange unbounded preceding会把当前行之前的所有行都包含进来，但当partition by时:SQL select deptno,ename,sal, sum(sal) over (partition by deptno order by deptno range unbounded preceding) sumsal from emp;SQL select deptno,ename,sal, sum(sal) over (order by deptno range unbounded preceding) sumsal from emp;这SQL句子和下面这SQL是等价的:select deptno,ename,sal, sum(sal) over (order by deptno) sumsal from emp;因为order by的默认窗口总是从结果集的第一行开始到它分组的最后一行。而partiton by的默认窗口总是从分区的第一行开始。3、range窗口: range 100 preceding这个子句只适用于number和date，而且只能order by一列。如果over()里asc排列，意思是number-100,number这样一个闭区间是它的窗口。如果over()里desc排列，意思是number,number+100这样一个闭区间是它的窗口。4、窗口总结1、unbounded preceding:从当前分区的第一行开始，到当前行结束。2、current row:从当前行开始，也结束于当前行。3、numeric expression preceding:对于rows来说从