Oracle OLAP 函數 最近這個東東用得非凡多，總結了一下 。 語法: FUNCTION_NAME(,,...) OVER () OLAP函數語法四個部分: 1、function本身 用于對窗口中的數據進行操作； 2、partitioning clause 用于將結果集分區(qū)； 3、order by clause 用于對分區(qū)中的數據進行排序； 4、windowing clause 用于定義function在其上操作的行的集合，即function所影響的范圍。一、order by對窗口的影響 不含order by的： SQL> select deptno,sal,sum(sal) over() 2 from emp; 不含order by時，默認的窗口是從結果集的第一行直到末尾。 含order by的： SQL> select deptno,sal, 2 sum(sal) over(order by deptno) as sumsal 3 from emp; 當含有order by時，默認的窗口是從第一行直到當前分組的最后一行。二、用于排列的函數 SQL> select empno, deptno, sal, 2 rank() over 3 (partition by deptno order by sal desc nulls last) as rank, 4 dense_rank() over 5 (partition by deptno order by sal desc nulls last) as dense_rank, 6 row_number() over 7 (partition by deptno order by sal desc nulls last) as row_number 8 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, 2 sum(sal) over 3 (partition by deptno order by ename rows 2 preceding) sumsal 4 from emp 5 order by deptno,ename; 加了partiton by分區(qū)后之后，rows 2 preceding(窗口)只在當前分區(qū)內生效，不會影響分區(qū)之外的行。 SQL> select ename,sal,hiredate, 2 first_value(ename)over
3 (order by hiredate asc rows 5 preceding) first_ename, 4 first_value(hiredate) over 5 (order by hiredate asc rows 5 preceding) first_hiredate 6 from emp 7 order by hiredate asc; order by hiredate asc rows 5 preceding: order by之后，取當前行的前5行+當前行作為窗口(共6行)。 2、"range unbounded preceding"range unbounded preceding會把當前行之前的所有行都包含進來，但當partition by時:SQL> select deptno,ename,sal, 2 sum(sal) over 3 (partition by deptno order by deptno range unbounded preceding) sumsal 4 from emp; SQL> select deptno,ename,sal, 2 sum(sal) over 3 (order by deptno range unbounded preceding) sumsal 4 from emp; 這SQL句子和下面這SQL是等價的:select deptno,ename,sal, sum(sal) over (order by deptno) sumsal from emp;因為order by的默認窗口總是從結果集的第一行開始到它分組的最后一行。 而partiton by的默認窗口總是從分區(qū)的第一行開始。 3、range窗口: "range 100 preceding"這個子句只適用于number和date，而且只能order by一列。 假如over()里asc排列，意思是[number-100,number]這樣一個閉區(qū)間是它的窗口。 假如over()里desc排列，意思是[number,number+100]這樣一個閉區(qū)間是它的窗口。 4、窗口總結1、unbounded preceding:從當前分區(qū)的第一行開始，到當前行結束。 2、current row:從當前行開始，也結束于當前行。 3、[numeric eXPression] preceding:對于rows來說從當前行之前的第[numeric expression]行開始，到當前行結束。對range來說從小于數值 表達式的值開始，到當前行結束。 4、[numeric expression] following:與[numeric expression] preceding相反。
