關于ORACLE OLAP函數語法的總結
Oracle數據庫的OLAP函數用途很廣泛，本文主要從以下幾個方面來對OLAP函數語法進行總結，以方便我們的理解。
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 expr
4、[numeric expr
關于Oracle數據庫OLAP函數語法的總結就介紹到這里，謝謝大家！
【編輯推薦】
