Oracle開發專題之：OLAP 函式 （rows 2 preceding / unbounded preceding）

ORACLE OLAP 函式

最近這個東東用得特別多，總結了一下 。
　　
語法:

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 sumsal  from 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,
　　   sum(sal) over (partition by deptno) as sumsal,
　　   avg(sal) over (partition by deptno) as avgsal,
　　   count(*) over (partition by deptno) as count,
　　   max(sal) over (partition by deptno) as maxsal
　　   from emp;

四、開窗語句

1、rows視窗: “rows 5 preceding” 適用於任何型別而且可以order by多列。

SQL> select deptno,ename,sal,
　　          sum(sal) over (order by deptno rows 2 preceding) sumsal
　　          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 emp  order 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 preceding"

range 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來說從當前行之前的第[numeric expression]行開始，到當前行結束。對range來說從小於數值表示式的值開始，到當前行結束。
4、[numeric expression] following:與[numeric expression] preceding相反。