語法:OVER(PARTITION BY)及開窗函式

n-lauren發表於2014-06-30
函式
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,
　　 2 sum(sal) over(order by deptno) as sumsal
　　 3 from emp;
　　當含有order by時，預設的視窗是從第一行直到當前分組的最後一行。

　　二、用於排列的函式
　　SQL> select empno, deptno, sal,
　　 2 rank() over (partition by deptno order by sal desc nulls last) as rank,
　　 3 dense_rank() over  (partition by deptno order by sal desc nulls last) as dense_rank,
　　 4 row_number() over (partition by deptno order by sal desc nulls last) as row_number
　　 5 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 (partition by deptno order by ename rows 2 preceding) sumsal
　　 3 from emp
　　 4 order by deptno,ename;

　　加了partiton by分割槽後之後，rows 2 preceding(視窗)只在當前分割槽內生效，不會影響分割槽之外的行。
　　SQL> select ename,sal,hiredate,
　　 2 first_value(ename) over (order by hiredate asc rows 5 preceding) first_ename,
　　 3 first_value(hiredate) over (order by hiredate asc rows 5 preceding) first_hiredate
　　 4 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,
　　 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相反。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-1199906/，如需轉載，請註明出處，否則將追究法律責任。

相關文章