ORACLE OLAP 函数

上周代码走查时发现由于分页功能需要提供总条数 ，项目组内的普遍做法是进行两次sql查询，一次用count(*)获得总条数，一次获取真正的展现数据。其实oracle提供了olap函数对此进行优化，可通过伪列：count(*) over()获得当前sql的总条数。

    比如：select t.*,count(*) over() from dual 会返回总条数为1。
 
    olap函数主要用于统计分析，熟练掌握能很好的提高sql执行效率。
        count(*) over() 具体功能描述如下：
       对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
       例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
sql如下：
 SELECT last_name, salary, COUNT(*) OVER () AS cnt1,
           COUNT(*) OVER (ORDER BY salary) AS cnt2,
           COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3 FROM employees;

结果如下 ：

LAST_NAME  SALARY  CNT2   CNT2  CNT3
Olson          2100 107 1 3
Markle 2200 107 3 2
Philtanker 2200 107 3 2
Landry 2400 107 5 8
Gee 2400 107 5 8
Colmenares 2500 107 11 10
Patel 2500 107 10 10
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/aatewu/archive/2009/08/03/4404680.aspx
-----------------------------------------------------------------------------------------------------
语法: 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()
  2  from emp;
不含order by时，默认的窗口是从结果集的第一行直到末尾。
含order by的：
SQL> select deptno,sal,
  2         sum(sal) over(order by deptno) as sumsal
  3  from emp;
当含有order by时，默认的窗口是从第一行直到当前分组的最后一行。二、用于排列的函数
SQL> select empno, deptno, sal,
  2         rank() over
  3         (partition by deptno order by sal desc nulls last) as rank,
  4         dense_rank() over
  5         (partition by deptno order by sal desc nulls last) as dense_rank,
  6         row_number() over
  7         (partition by deptno order by sal desc nulls last) as row_number
  8   from  emp; 三、用于合计的函数
SQL> select deptno,sal,
  2         sum(sal) over (partition by deptno) as sumsal,
  3         avg(sal) over (partition by deptno) as avgsal,
  4         count(*) over (partition by deptno) as count,
  5         max(sal) over (partition by deptno) as maxsal
  6    from emp; 四、开窗语句 1、rows窗口: "rows 5 preceding"     适用于任何类型而且可以order by多列。    SQL> select deptno,ename,sal,
          2         sum(sal) over (order by deptno rows 2 preceding) sumsal
          3    from emp; rows 2 preceding:将当前行和它前面的两行划为一个窗口，因此sum函数就作  用在这三行上面
SQL> select deptno,ename,sal,
  2         sum(sal) over
  3         (partition by deptno order by ename rows 2 preceding) sumsal
  4    from emp
  5  order by deptno,ename;
加了partiton by分区后之后，rows 2 preceding(窗口)只在当前分区内生效，不会影响分区之外的行。
SQL> select ename,sal,hiredate,
  2         first_value(ename)over

  3         (order by hiredate asc rows 5 preceding) first_ename,
  4         first_value(hiredate) over
  5         (order by hiredate asc rows 5 preceding) first_hiredate
  6    from emp
  7  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,
  2         sum(sal) over
  3         (partition by deptno order by deptno range unbounded preceding) sumsal
  4    from emp;
SQL> select deptno,ename,sal,
  2         sum(sal) over
  3         (order by deptno range unbounded preceding) sumsal
  4   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相反。

转载于:https://www.cnblogs.com/bensoncn/archive/2009/09/14/1566279.html