ORACLE OLAP 函數(shù)
　　 最近這個(gè)東東用得特別多，總結(jié)了一下 。?
　　

語(yǔ)法: FUNCTION_NAME(,,...)?
　　? OVER()

　　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;
　　

四、開(kāi)窗語(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é)果集的第一行開(kāi)始到它分組的最后一行。
　　而partiton by的默認(rèn)窗口總是從分區(qū)的第一行開(kāi)始。

　　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ū)的第一行開(kāi)始，到當(dāng)前行結(jié)束。
　　2、current row:從當(dāng)前行開(kāi)始，也結(jié)束于當(dāng)前行。
　　3、[numeric expression] preceding:對(duì)于rows來(lái)說(shuō)從當(dāng)前行之前的第[numeric expression]行開(kāi)始，到當(dāng)前行結(jié)束。對(duì)range來(lái)說(shuō)從小于數(shù)值表達(dá)式的值開(kāi)始，到當(dāng)前行結(jié)束。
　　4、[numeric expression] following:與[numeric expression] preceding相反。

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/sooner/p/7735154.html

總結(jié)

以上是生活随笔為你收集整理的Oracle分析函数-OLAP函数总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。