Oracle數(shù)據(jù)庫的OLAP函數(shù)用途很廣泛，本文主要從以下幾個方面來對OLAP函數(shù)語法進行總結，以方便我們的理解。

OLAP函數(shù)語法四個部分：

1、function本身用于對窗口中的數(shù)據(jù)進行操作；

2、partitioning clause 用于將結果集分區(qū)；

3、order by clause 用于對分區(qū)中的數(shù)據(jù)進行排序；

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時，默認的窗口是從***行直到當前分組的***一行。

二、用于排列的函數(shù)

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; 

三、用于合計的函數(shù)

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函數(shù)就作 用在這三行上面。

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分區(qū)后之后，rows 2 preceding(窗口)只在當前分區(qū)內(nèi)生效，不會影響分區(qū)之外的行。

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的默認窗口總是從分區(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來說從小于數(shù)值表達式的值開始，到當前行結束。

4、[numeric expression] following:與[numeric expression] preceding相反。 

關于Oracle數(shù)據(jù)庫OLAP函數(shù)語法的總結就介紹到這里，謝謝大家！

【編輯推薦】

1. oracle job之存儲過程處理的奧秘
2. 使用Oracle job定時執(zhí)行存儲過程
3. ASP通過Oracle Object for OLE對Oracle查詢
4. 在存儲過程中連接遠程Oracle數(shù)據(jù)庫并進行操作
5. 淺談修復被優(yōu)化大師Kiss掉的Oracle監(jiān)聽器的兩種方法