分析函數(shù)是Oracle專門用于解決復(fù)雜報(bào)表統(tǒng)計(jì)需求的功能強(qiáng)大的函數(shù)，它可以在數(shù)據(jù)中進(jìn)行分組然后計(jì)算基于組的某種統(tǒng)計(jì)值，并且每一組的每一行都可以返回一個(gè)統(tǒng)計(jì)值。分析函數(shù)帶有一個(gè)開窗函數(shù)over()，包含三個(gè)分析子句:分組(partition by), 排序(order by), 窗口(rows) ，他們的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

Oracle分析函數(shù)的學(xué)習(xí)有以下幾個(gè)關(guān)鍵點(diǎn)：

1.對(duì)一組數(shù)據(jù)進(jìn)行計(jì)算，返回多行。

2.不需要進(jìn)行多表聯(lián)合，提高性能。

3.在所有表連接和所有WHERE, GROUP BY和HAVING字句之后處理，在ORDER BY子句之前處理。

4.只能位于SELECT或者ORDER BY子句。

分析函數(shù)語法:

FUNCTION_NAME(,...)

OVER

()

例:

sum(sal) over (partition by deptno order by ename) new_alias

sum就是函數(shù)名

(sal)是分析函數(shù)的參數(shù),每個(gè)函數(shù)有0~3個(gè)參數(shù),參數(shù)可以是表達(dá)式,例如:sum(sal+comm)

over是一個(gè)關(guān)鍵字，用于標(biāo)識(shí)分析函數(shù)，否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)。

partition by deptno 是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結(jié)果集可看作一個(gè)單一的大區(qū)。

order by ename 是可選的order by 子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問結(jié)果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時(shí),該子句是強(qiáng)制性的,它指定了在計(jì)算分析函數(shù)時(shí)一組內(nèi)的數(shù)據(jù)是如何排序的。

1)FUNCTION子句

ORACLE提供了26個(gè)分析函數(shù),按功能分5類

分析函數(shù)分類

等級(jí)(ranking)函數(shù):用于尋找前N種查詢

開窗(windowing)函數(shù):用于計(jì)算不同的累計(jì),如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個(gè)窗口上。

例:

sum(t.sal) over (order by t.deptno,t.ename) running_total,

sum(t.sal) over (partition by t.deptno order by t.ename) department_total

制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個(gè)分區(qū)或一組上的所有列

例:

sum(t.sal) over () running_total2,

sum(t.sal) over (partition by t.deptno ) department_total2

制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語句上缺少一個(gè)ORDER BY子句!

LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值，為了避免數(shù)據(jù)的自連接，它們是非常用用的。

VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計(jì)算任何未排序分區(qū)的統(tǒng)計(jì)值

2)PARTITION子句

按照表達(dá)式分區(qū)(就是分組)，如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個(gè)單一的組。

3)ORDER BY子句

分析函數(shù)中ORDER BY的存在將添加一個(gè)默認(rèn)的開窗子句,這意味著計(jì)算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDER BY時(shí)，默認(rèn)的窗口是全部的分區(qū)。在Order by子句后可以添加nulls last，如:order by comm desc nulls last   表示排序時(shí)忽略comm列為空的行。  

4)WINDOWING子句

用于定義分析函數(shù)將在其上操作的行的集合

Windowing子句給出了一個(gè)定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對(duì)這些數(shù)據(jù)進(jìn)行操作

默認(rèn)的窗口是一個(gè)固定的窗口，僅僅在一組的第一行開始，一直繼續(xù)到當(dāng)前行,要使用窗口，必須使用ORDER BY子句。

根據(jù)2個(gè)標(biāo)準(zhǔn)可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當(dāng)前行的行偏移量。

5)Rang窗口

Range 5 preceding:將產(chǎn)生一個(gè)滑動(dòng)窗口,他在組中擁有當(dāng)前行以前5行的集合

ANGE窗口僅對(duì)NUMBERS和DATES起作用，因?yàn)椴豢赡軓腣ARCHAR2中增加或減去N個(gè)單元。

另外的限制是ORDER BY中只能有一列，因而范圍實(shí)際上是一維的，不能在N維空間中。

例:

avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統(tǒng)計(jì)前100天平均工資

6)Row窗口

利用ROW分區(qū)，就沒有RANGE分區(qū)那樣的限制了，數(shù)據(jù)可以是任何類型，且ORDER BY 可以包括很多列。

7)Specifying窗口

UNBOUNDED PRECEDING:這個(gè)窗口從當(dāng)前分區(qū)的每一行開始，并結(jié)束于正在處理的當(dāng)前行。

CURRENT ROW:該窗口從當(dāng)前行開始(并結(jié)束)

Numeric Expression PRECEDING:對(duì)該窗口從當(dāng)前行之前的數(shù)字表達(dá)式(Numeric Expression)的行開始，對(duì)RANGE來說，從從行序值小于數(shù)字表達(dá)式的當(dāng)前行的值開始。

Numeric Expression FOLLOWING:該窗口在當(dāng)前行Numeric Expression行之后的行終止(或開始),且從行序值大于當(dāng)前行Numeric Expression行的范圍開始(或終止)

range between 100 preceding and 100 following:當(dāng)前行100前,當(dāng)前后100后

注意:分析函數(shù)允許你對(duì)一個(gè)數(shù)據(jù)集進(jìn)排序和篩選，這是SQL從來不能實(shí)現(xiàn)的。除了最后的Order by子句之外，分析函數(shù)是在查詢中執(zhí)行的最后的操作集，這樣的話，就不能直接在謂詞中使用分析函數(shù)，即不能在上面使用where或having子句。

Oracle分析函數(shù)實(shí)例(在scott用戶下模擬)：

示例目的：顯示各部門員工的工資，并附帶顯示該部分的最高工資。

--顯示各部門員工的工資，并附帶顯示該部分的最高工資。

SELECT E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
LAST_VALUE(E.SAL) 
OVER(PARTITION BY E.DEPTNO 
ORDER BY E.SAL ROWS 
--unbounded preceding and unbouned following

針對(duì)當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄

--unbounded：不受控制的，無限的

--preceding：在...之前

--following：在...之后

BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
FROM EMP E;

運(yùn)行結(jié)果：

結(jié)合上述的Oracle分析函數(shù)的應(yīng)用實(shí)例，我們不難看出Oracle分析函數(shù)的作用，在提高了運(yùn)算效率的同時(shí)也提高了Oracle數(shù)據(jù)庫的查詢效率。在本站的Oracle教程中，對(duì)Oracle數(shù)據(jù)庫中的其他函數(shù)也有詳細(xì)的講解，想要深入學(xué)習(xí)的小伙伴可以結(jié)合教程學(xué)習(xí)，使我們更好更方便地使用Oracle數(shù)據(jù)庫。