关于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 expression] preceding:对于rows来说从当前行之前的第[numeric expression]行开始，到当前行结束。对range来说从小于数值表达式的值开始，到当前行结束。

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

关于Oracle数据库OLAP函数语法的总结就介绍到这里，谢谢大家！

【编辑推荐】

1. oracle job之存储过程处理的奥秘
2. 使用Oracle job定时执行存储过程
3. ASP通过Oracle Object for OLE对Oracle查询
4. 在存储过程中连接远程Oracle数据库并进行操作
5. 浅谈修复被优化大师Kiss掉的Oracle监听器的两种方法