Oracle开发专题之：OLAP 函数（rows 2 preceding / unbounded preceding）

冷不防 2021-06-26 16:06 381阅读 0赞

## 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`相反。