1. 引言
最近心血來潮去參加了一個PL/SQL工程師的面試，期間被問到了Oracle分析函數，PL/SQL開發并非我的老本行，在之前的工作中，也很少使用分析函數，原因之一是對資料庫移植問題的考慮；其二是很少遇到非用分析函數不可的情況；其三是分析函數的文法相對複雜，令人缺乏興趣。這幾天看了一些入門内容，發現它們還是很強大的，唯一的遺憾是目前身邊沒有真實的應用場景，是以這裡舉的例子看起來難免有點紙上談兵的感覺。
2. 分析函數（Analytic function）與聚合函數（Aggregate function）
我們先從 “ORA-00979: not a GROUP BY expression”說起，相信大家在開始使用SQL的過程中都遇到過這個錯誤，比如你寫了下面這樣的SQL：
SELECT title, corp, COUNT(*) cnt
FROM film
GROUP BY corp
ORDER BY corp;
ORA-00979錯誤表明，SELECT子句中出現的字段，要麼包含于GROUP BY子句，要麼作為聚合函數（上面的COUNT）的輸入，除此之外不能包含其它字段。我們可以修改上面的SQL使它可以正常運作：
SELECT corp, COUNT(*) af
FROM film
GROUP BY corp
ORDER BY corp;
這就引出了聚合函數的一個主要特征，聚合之後，同組隻保留下一條資料，由上圖可知表中由“20th Century Fox”公司出品的影片共有7部，最終記錄是一條。
這符合某些統計需求，然而有時候，我們并不希望聚合函數中的這種“合并”操作，尤其是我們常常希望在SELECT子句出現未參與統計的字段，此時我們便可以使用分析函數。對于表中的每一行記錄，分析函數都能傳回一個統計值，下面我們來看一個具體的執行個體：
SELECT title,year,corp,
COUNT(*) OVER (PARTITION BY corp) af
FROM film;
注：PARTITION BY不僅導緻分區（類似于GROUP BY），而且分區之間是排序好的，也算是它的一個“副作用”。
3. 基本文法
function_name(arg1,arg2,...)OVER (<partition-clause> <order-by-clause > <window clause>)
其中<order-by-clause >子句會在下面的例子中穿插提到，<window clause>子句則會在最後一節進行解釋。
另外，還需要提到的一點是，在有分析函數參與的SQL語句中，執行流程依次是：
1) JOIN, WHERE,GROUP BY, HAVING
2) 建立分區（通常通過PARTITION BY），而後分析函數将作用于分區中的每一行
3) 主語句中ORDER BY（這個我們以前就知道，主語句的ORDER BY總是最後執行）。
4. AVG, SUM, MAX,MIN, COUNT
這些大家熟知的聚合函數，同樣可作為分析函數使用，當然要符合第3節中給出的分析函數的文法，下面我們來看幾個執行個體：
SELECT title,corp,year,box_office,
ROUND(AVG(box_office) OVER (PARTITION BY corp)) af
FROM film;
讓我們看看在OVER内應用ORDER BY之後的情形：
SELECT title,year,corp,
COUNT(*) OVER (PARTITION BY corp ORDER BY year) af
FROM film;
這個結果容易讓人非常困惑，實際上OVER内的ORDER BY子句導緻了分區（PARTITION）内的資料進行了逐漸累加。通常，這種“累加”始于排序後該分區的第一條記錄，結束于目前記錄。當排序列出現相同值（比如上面的兩個1997、兩個2009），累加則結束于相同記錄的最後一條。
讓我們再來看一個例子：
SELECT title,corp,year,box_office,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year) af
FROM film;
5. RANK,DENSE_RANK, ROW_NUMBER
代碼
SELECT title, corp, year,
RANK() OVER (PARTITION BY corp ORDER BY year) r,
DENSE_RANK() OVER (PARTITION BY corp ORDER BY year) dr,
ROW_NUMBER() OVER (PARTITION BY corp ORDER BY year) rn
FROM film;
RANK, DENSE_RANK,ROW_NUMBER具有類似的行為，隻有當排序列包含重複值時，它們的差別才能展現出來。注意上圖中紅色辨別部分，對于相同的年份1997，RANK, DENSE_RANK都傳回相同的值，不同的時，DENSE_RANK采用密集編号，兩個1之後接着的編号是2。對于ROW_NUMBER，則總是産生連續的編号。
利用這幾個函數的特性，可以相對簡單地實作TOP N的查詢，例如查詢表中各電影公司年份最早的電影：
SELECT * FROM (
SELECT title,corp,year,
RANK() OVER (PARTITION BY corp ORDER BY year) r
FROM film
) t
WHERE t.r=1;
6. LEAD, LAG
基本文法：
LEAD(<sql_expr>,<offset>, <default>) OVER (<analytic_clause>)
LAG(<sql_expr>,<offset>, <default>) OVER (<analytic_clause>)
<sql_expr> ：通常是字段名。
<offset> ：表示相對于目前行的偏移幅度（對LEAD來說是向後偏移，對LAG來說則是向前），正整數，預設為1。
<default> ：當偏移幅度超出該分區（PARTITION）的範圍時傳回的值。
SELECT title,corp,year,box_office,
LEAD(box_office,1,-9999) OVER (PARTITION BY corp ORDER BY box_office) af1,
LAG(box_office,1,-9999) OVER (PARTITION BY corp ORDER BY box_office) af2
FROM film;
我們先來分析LEAD函數的結果，即上圖中的AF1字段，對于同一行分區内，AF1字段第N行的值 = 原BOX_OFFICE第N+1行的值，參見紅色辨別部分。為什麼是N+1呢？實際這取決到我們在SQL語句中指定的offset，我們上面指定的是1。
如果偏移之後超過了分區的範圍，則傳回函數中指定的default值，這裡我們指定的是-9999。
LAG與LEAD類似，隻不過它的偏移是向前的，這點與LEAD相反。
7. FIRST_VALUE,LAST_VALUE
FIRST_VALUE傳回各分區内指定排序後的第一條記錄的值，LAST_VALUE則傳回最後一條記錄的值。
SELECT title,corp,year,box_office,
box_office-(FIRST_VALUE(box_office) OVER (PARTITION BY corp ORDER BY box_office)) af
FROM film;
8. Window子句
我們在第3節提到了分析函數中還有一個window clause，該子句為分析函數指定統計“視窗”。在前面的例子中，大多數的統計“視窗”都是整個分區（PARTITION），也就是說每個統計結果值都是基于相應分區内的所有資料計算而得。使用Window子句則可以将統計“視窗”進一步縮小，我們看一個例子：
SELECT title,corp,year,box_office,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) af
FROM film;
由于分析函數中給定的視窗是ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING，基本上我們可以按字面意思了解“2 Preceding”跟“1 Following”，這表示“視窗”始于目前行之前兩行，終于目前行之後一行，“視窗”大小共4行，是以我們看最後一列中紅色辨別的1840000000是由第1行到第4行中求得的MAX值；而最後一列中藍色辨別的920000000則是由第2行到第5行中求得的MAX值。
回過來看Window子句的具體文法：
ROWS BETWEEN<start_expr> AND <end_expr>
其中<start_expr>與<end_expr> 可能是以下形式：
(1) 1, 2, ..., NPRECEDING|FOLLOWING
(2) UNBOUNDEDPRECEDING|FOLLOWING
(3) CURRENT ROW
還存在一種更簡單的文法：
ROWS 1, 2, ..., NPRECEDING 或ROWS UNBOUNDEDPRECEDING
此時，統計“視窗”預設結束于目前行。
再來看一個綜合例子：
代碼
SELECT title,corp,year,box_office,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) af1,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN 2 PRECEDING AND UNBOUNDEDFOLLOWING) af2,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) af3,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS 2 PRECEDING) af4,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) af5,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) af6
FROM film;
注：可以看到查詢結果中的AF3這一列是NULL值，這是因為我們在指定“視窗”的時候，<start_expr>所指向的記錄一定要位于<end_expr>之前，而産生AF3統計結果的“視窗”是ROWS BETWEEN 1 PRECEDINGAND 2 PRECEDING，與要求不符，導緻傳回NULL。
除了上面用到的視窗子句——我們稱之為Row Type Window，還有另外一種視窗子句，叫作Range Type Window，下面我們來看一個例子：
SELECT title,corp,year,box_office,
MAX(box_office) OVER (PARTITION BY corp ORDER BY year RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING) af
FROM film;
由于我們是按字段“year”進行排序的，那麼上面的視窗就表示從目前行的年份往前2年，到目前行的年份往後1年，共4年的範圍。
比如對于上圖中紅色辨別的行中，該行的視窗即[2003, 2006]，由于這個時間段内隻有它自己，則傳回對應的BOX_OFFICE；再比如藍色辨別部分，該行的視窗即[2007, 2010]，傳回此範圍内的最大值2560000000。
--
到這裡，文章寫完了，文章中介紹了幾個相對比較常見的分析函數，作為入門之用，其它衆多的分析函數在使用上大同小異，有興趣的同仁可以深入研究。祝閱讀愉快。