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

Oracle分析函數的學習有以下幾個關鍵點：

1.對一組數據進行計算，返回多行。

2.不需要進行多表聯合，提高性能。

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

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

分析函數語法:

FUNCTION_NAME(,...)

OVER

()

例:

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

sum就是函數名

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

over是一個關鍵字，用于標識分析函數，否則查詢分析器不能區別sum()聚集函數和sum()分析函數。

partition by deptno 是可選的分區子句,如果不存在任何分區子句,則全部的結果集可看作一個單一的大區。

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

1)FUNCTION子句

ORACLE提供了26個分析函數,按功能分5類

分析函數分類

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

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

例:

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)函數:與開窗函數同名,作用于一個分區或一組上的所有列

例:

sum(t.sal) over () running_total2,

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

制表函數與開窗函數的關鍵不同之處在于OVER語句上缺少一個ORDER BY子句!

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

VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數:計算任何未排序分區的統計值

2)PARTITION子句

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

3)ORDER BY子句

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

4)WINDOWING子句

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

Windowing子句給出了一個定義變化或固定的數據窗口的方法,分析函數將對這些數據進行操作

默認的窗口是一個固定的窗口，僅僅在一組的第一行開始，一直繼續到當前行,要使用窗口，必須使用ORDER BY子句。

根據2個標準可以建立窗口:數據值的范圍(RANGES)或與當前行的行偏移量。

5)Rang窗口

Range 5 preceding:將產生一個滑動窗口,他在組中擁有當前行以前5行的集合

ANGE窗口僅對NUMBERS和DATES起作用，因為不可能從VARCHAR2中增加或減去N個單元。

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

例:

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

6)Row窗口

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

7)Specifying窗口

UNBOUNDED PRECEDING:這個窗口從當前分區的每一行開始，并結束于正在處理的當前行。

CURRENT ROW:該窗口從當前行開始(并結束)

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

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

range between 100 preceding and 100 following:當前行100前,當前后100后

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

Oracle分析函數實例(在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

針對當前所有記錄的前一條、后一條記錄，也就是表中的所有記錄

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

--preceding：在...之前

--following：在...之后

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

運行結果：

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