OLAP函數語法的總結

OLAP函數語法的總結

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()
  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相反。