關(guān)于ORACLE OLAP函數(shù)語(yǔ)法的總結(jié)
Oracle數(shù)據(jù)庫(kù)的OLAP函數(shù)用途很廣泛，本文主要從以下幾個(gè)方面來(lái)對(duì)OLAP函數(shù)語(yǔ)法進(jìn)行總結(jié)，以方便我們的理解。
OLAP函數(shù)語(yǔ)法四個(gè)部分：
1、function本身用于對(duì)窗口中的數(shù)據(jù)進(jìn)行操作；
2、partitioning clause 用于將結(jié)果集分區(qū)；
3、order by clause 用于對(duì)分區(qū)中的數(shù)據(jù)進(jìn)行排序；
4、windowing clause 用于定義function在其上操作的行的集合，即function所影響的范圍。
一、order by對(duì)窗口的影響
不含order by的：
- SQL> select deptno,sal,sum(sal) over()
- from emp;
不含order by時(shí)，默認(rèn)的窗口是從結(jié)果集的***行直到末尾。
含order by的：
- SQL> select deptno,sal,
- sum(sal) over(order by deptno) as sumsal
- from emp;
當(dāng)含有order by時(shí)，默認(rèn)的窗口是從***行直到當(dāng)前分組的***一行。
二、用于排列的函數(shù)
- 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;
三、用于合計(jì)的函數(shù)
- 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;
四、開窗語(yǔ)句
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：將當(dāng)前行和它前面的兩行劃為一個(gè)窗口，因此sum函數(shù)就作 用在這三行上面。
- 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分區(qū)后之后，rows 2 preceding(窗口)只在當(dāng)前分區(qū)內(nèi)生效，不會(huì)影響分區(qū)之外的行。
- 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之后，取當(dāng)前行的前5行+當(dāng)前行作為窗口(共6行)。
2、"range unbounded preceding"range unbounded preceding會(huì)把當(dāng)前行之前的所有行都包含進(jìn)來(lái)，但當(dāng)partition by時(shí)：
- 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是等價(jià)的：
- select deptno,ename,sal,
- sum(sal) over (order by deptno) sumsal
- from emp;
因?yàn)閛rder by的默認(rèn)窗口總是從結(jié)果集的***行開始到它分組的***一行。
而partiton by的默認(rèn)窗口總是從分區(qū)的***行開始。
3、range窗口：
"range 100 preceding"這個(gè)子句只適用于number和date，而且只能order by一列。
如果over()里asc排列，意思是[number-100,number]這樣一個(gè)閉區(qū)間是它的窗口。
如果over()里desc排列，意思是[number,number+100]這樣一個(gè)閉區(qū)間是它的窗口。
4、窗口總結(jié)：
1、unbounded preceding:從當(dāng)前分區(qū)的***行開始，到當(dāng)前行結(jié)束。
2、current row:從當(dāng)前行開始，也結(jié)束于當(dāng)前行。
3、[numeric expr
4、[numeric expr
關(guān)于Oracle數(shù)據(jù)庫(kù)OLAP函數(shù)語(yǔ)法的總結(jié)就介紹到這里，謝謝大家！
【編輯推薦】
