Oracle分析函数-OLAP函数总结

ORACLE OLAP 函数
　　 最近这个东东用得特别多，总结了一下 。 
　　express

语法: FUNCTION_NAME(,,...) 
　　  OVER()函数

　　OLAP函数语法四个部分:
　　一、function自己 用于对窗口中的数据进行操做；
　　二、partitioning clause 用于将结果集分区；
　　三、order by clause 用于对分区中的数据进行排序；
　　四、windowing clause 用于定义function在其上操做的行的集合，即function所影响的范围。
　　spa

1、order by对窗口的影响
　　不含order by的：
　　SQL> select deptno,sal,sum(sal) over() from emp;
　　不含order by时，默认的窗口是从结果集的第一行直到末尾。
　　blog

     含order by的：
　　SQL> select deptno,sal, sum(sal) over(order by deptno) as sumsal  from emp;
　　当含有order by时，默认的窗口是从第一行直到当前分组的最后一行。
　　排序

2、用于排列的函数
　　SQL> select empno, deptno, sal, it

          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;
　　io

3、用于合计的函数
　　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;
　　ast

4、开窗语句
　function

　一、rows窗口: "rows 5 preceding"  适用于任何类型并且能够order by多列。date

　　 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行)。
　　

       二、"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的默认窗口老是从分区的第一行开始。

　　三、range窗口: "range 100 preceding"

　　这个子句只适用于number和date，并且只能order by一列。
　　若是over()里asc排列，意思是[number-100,number]这样一个闭区间是它的窗口。
　　若是over()里desc排列，意思是[number,number+100]这样一个闭区间是它的窗口。

　　四、窗口总结
　　一、unbounded preceding:从当前分区的第一行开始，到当前行结束。
　　二、current row:从当前行开始，也结束于当前行。
　　三、[numeric expression] preceding:对于rows来讲从当前行以前的第[numeric expression]行开始，到当前行结束。对range来讲从小于数值表达式的值开始，到当前行结束。
　　四、[numeric expression] following:与[numeric expression] preceding相反。