分析函數，最早是從ORACLE8.1.6開始出現的，它的設計目的是為了解決諸如“累計計算”，“找出分組内百分比”，“前-N條查詢”，“移動平均數 計算”"等問題。其實大部分的問題都可以用PL/SQL解決，但是它的性能并不能達到你所期望的效果。分析函數是SQL言語的一種擴充，它并不是僅僅試代 碼變得更簡單而已，它的速度比純粹的SQL或者PL/SQL更快。現在這些擴充已經被納入了美國國家标準化組織SQL委員會的SQL規範說明書中。
分析函數是在一個記錄行分組的基礎上計算它們的總值。與集合函數不同，他們傳回各分組的多行記錄。行的分組被稱視窗，并通過分析語句定義。對于每記錄行， 定義了一個“滑動”視窗。該視窗确定“目前行”計算的範圍。視窗的大小可由各行的實際編号或由時間等邏輯間隔确定。
除了ORDER BY（按…排序）語句外，分析函數是一條查詢被執行的操作。所有合并、WHERE、GROUP BY、HAVING語句都是分析函數處理之前完成的。是以，分析函數隻出現在選擇目錄或ORDER BY（按…排序）語句中。
前期資料準備：
create table EMP
(
ENAME VARCHAR2(10),
DEPTNO VARCHAR2(2),
SAL NUMBER(10)
)
insert into EMP (ENAME, DEPTNO, SAL)
values ('CLARK', '10', 2450);
insert into EMP (ENAME, DEPTNO, SAL)
values ('MILLER', '10', 1300);
insert into EMP (ENAME, DEPTNO, SAL)
values ('KING', '10', 5000);
insert into EMP (ENAME, DEPTNO, SAL)
values ('FORD', '20', 3000);
insert into EMP (ENAME, DEPTNO, SAL)
values ('ADAMS', '20', 1100);
insert into EMP (ENAME, DEPTNO, SAL)
values ('JONES', '20', 2975);
insert into EMP (ENAME, DEPTNO, SAL)
values ('SCOTT ', '20', 3000);
insert into EMP (ENAME, DEPTNO, SAL)
values ('SMITH', '20', 800);
insert into EMP (ENAME, DEPTNO, SAL)
values ('ALLEN', '30', 1600);
insert into EMP (ENAME, DEPTNO, SAL)
values ('BLAKE', '30', 2850);
insert into EMP (ENAME, DEPTNO, SAL)
values ('JAMES', '30', 950);
insert into EMP (ENAME, DEPTNO, SAL)
values ('TURNER', '30', 1500);
insert into EMP (ENAME, DEPTNO, SAL)
values ('MARTIN ', '30', 1250);
insert into EMP (ENAME, DEPTNO, SAL)
values ('WARD', '30', 1250);
commit;
The Syntax句法：
OVER (
<Query-Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
根據劃分表達式設定的規則，PARTITION BY（按…劃分）将一個結果邏輯分成N個分組劃分表達式。在此“劃分”和“分組”用作同義詞。分析函數獨立應用于各個分組，并在應用時重置（按…排序）語 句規定了每個分組（劃分）的資料如何排序。這必然影響分析函數的結果。
視窗生成語句用以定義滑動或固定資料視窗，分析函數在分組内進行分析。該語句能夠對分組中任意定義的滑動或固定視窗進行計算。
Example: Calculate a running Total例：累計計算：本例中對某部門的工資進行逐行計算，每行包括之前所有行中工資的合計。
SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
/
Ename Deptno Sal Running Total Dept Total Seq
------ ------ ------ ------------- ---------- ----
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 3000 12850 4100 2
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5
ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6
.本例指出了如何計算整條查詢的“累計”。即使用排序後的整個結果集合，通過SUM(sal) OVER (ORDER BY deptno, ename)函數得到。可以進一步計算各個部門的累計值，該值在開始下一個部門計算時将被重置。由SUM(sal)中的PARTITION BY deptno實作。該條查詢中指定劃分語句将資料進行分組。根據排序規則（增加了“Seq”列以顯示該狀态)，ROW_NUMBER()函數将每組傳回的 記錄行進行順序編号，執行計劃顯示，整條查詢僅需3條一緻get函數就可以很好的執行。這一點是标準SQL甚至PL/SQL不能都實作的。
Top-N Queries前N條查詢：如何通過部分字段得到前N條記錄？在未使用這些分析函數之前，很難對此類問題做出回答。人們關于前N條查詢的說法存在問題。在 設計報告時，應留意這一點。我需要知道部門工資為前3名銷售代表的誰。這句話的問題在于含混不清。因為存在重複的值，如果有四個人領着同樣的工資，該怎麼 處理？
Example 1例1：從多到少排列銷售人員的工資，取前三行。如果該部門少于三人，則傳回的記錄少于三個。
SELECT * FROM (
SELECT deptno, ename, sal, ROW_NUMBER()
OVER (
PARTITION BY deptno ORDER BY sal DESC
) Top3 FROM emp
)
WHERE Top3 <= 3
/
DEPTNO ENAME SAL TOP3
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1
FORD 3000 2
JONES 2975 3
30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
9 rows selected.
該查詢根據工資列以降序排列各個劃分（或分組，屬于該deptno），并在處理過程中為每行配置設定一個順序号。然後使用WHERE語句得到各劃分的 前三行。
Example 2例2：我需要工資為前三位的銷售人員名字——即查找工資金額、排序、取最高的三項金額、給我領取這些工資的人員的名字。
SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/
DEPTNO ENAME SAL TOPN
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1 <--- !
FORD 3000 1 <--- !
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
ALLEN 1600 2
30 TURNER 1500 3
10 rows selected.
其中，使用DENSE_RANK函數得出最高的三個工資金額。然後指定Dense rank至工資列，并将其按降序排列。 DENSE_RANK函數計算排序後分組中各行的序數。序數為從1開始的連續整數。最大的序數就是查詢所所傳回唯一值的個數。如果出現并列，序數不跳計。 具有相同值的列的序數相同。 DENSE_RANK函數不跳計序數，并為相同值的列賦予相同的序數。結果集合在目前視窗建立後，通過部門編号選擇Dense rank為3 或3之前的行，就可以知道工資在該部門前三位的名字。
Windows視窗：
視窗語句用以定義滑動或固定資料視窗。在其上面運作組的分析函數。預設視窗為固定視窗，從第一行開始到目前行。可根據兩種規則對視窗進行設定：數 據值的範圍或目前行指定區距的行。分析函數中的ORDER BY會預設添加一條視窗語句：RANGE UNBOUNDED PRECEDING。即按照ORDER BY語句取得劃分中的目前之前的所有行。以下例子為一個分組中的滑動視窗，計算該組中目前行與其前兩行的SAL列的和。如我們需要計算目前員工的工資和其 之前的兩人工資的和，如下例所示。
SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename
ROWS 2 PRECEDING) "Sliding Total"
FROM emp
ORDER BY deptno, ename
/
Deptno Ename Sal Sliding Total
------ ------ ------ -------------
10 CLARK 2450 2450
KING 5000 7450
MILLER 1300 8750
20 ADAMS 1100 1100
FORD 3000 4100
JONES 2975 7075 ^
SCOTT 3000 8975 |
SMITH 800 6775 /-- Sliding Window
30 ALLEN 1600 1600
BLAKE 2850 4450
JAMES 950 5400
MARTIN 1250 5050
TURNER 1500 3700
WARD 1250 4000
劃分語句使SUM (sal)在各部門内進行，并獨立于其他組。當部門改變時，SUM (sal) 也被“重置”。ORDER BY ENAME語句通過ENAME排列各部門的資料。這使得視窗語句：ROWS 2 PRECEDING擷取該分組中目前行之前兩行的資料以計算合計工資。
例如，SMITH的SLIDING TOTAL（滑動合計）6 7 7 5是800、3000以及2975的和。即視窗中SMITH行及其之前兩行工資的簡單相加。
Range Windows範圍視窗：
範圍視窗根據WHERE語句對行進行收集。例如“之前5”将會生成一個滑動視窗，包括該分組中目前行之前的5個機關所有行。這些機關可以是數值或日期，使 用數字或日期以外的其他資料類型表示的範圍無效。
Example例：計算目前雇傭日期之前100天内雇傭的員工的數量。範圍視窗傳回目前行雇傭日期100天之前并在這個範圍内計算行數。計算使用 以下視窗規格：
COUNT(*) OVER (ORDER BY hiredate ASC RANGE 100 PRECEDING)
SELECT ename, hiredate, hiredate-100 hiredate_pre,
COUNT(*)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) cnt
FROM emp
ORDER BY hiredate ASC
/
Name Hired Hired-100 Cnt
-------- ---------- ---------- ---
SMITH 17-DEC-80 08-SEP-80 1
ALLEN 20-FEB-81 12-NOV-80 2
WARD 22-FEB-81 14-NOV-80 3
JONES 02-APR-81 23-DEC-80 3
LAKE 01-MAY-81 21-JAN-81 4
CLARK 09-JUN-81 01-MAR-81 3
TURNER 08-SEP-81 31-MAY-81 2
MARTIN 28-SEP-81 20-JUN-81 2
KING 17-NOV-81 09-AUG-81 3
JAMES 03-DEC-81 25-AUG-81 5
FORD 03-DEC-81 25-AUG-81 5
MILLER 23-JAN-82 15-OCT-81 4
SCOTT 09-DEC-82 31-AUG-82 1
ADAMS 12-JAN-83 04-OCT-82 2
根據雇傭日期ASC對每個劃分進行排序。例中CLARK行可看到其雇傭日期為1981年6月9日，100天之前是1981年3月1日，看看在這期間雇傭的 員工，會發現JONES（雇傭日期：1981年4月2日）、BLAKE（雇傭日期：1981年5月1日），共3行，包括目前行，在CLARK行“Cnt” 列中。
Compute average salary for defined range計算定義範圍的平均工資：
，計算每個員工雇傭之前100天内雇傭員工的平均工資。查詢如下：
SELECT ename, hiredate, sal,
AVG(sal)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) avg_sal
FROM emp
ORDER BY hiredate ASC
/
Name Hired SAL Avg-100
-------- ---------- ---------- -------
SMITH 17-DEC-80 800 800
ALLEN 20-FEB-81 1600 1200
WARD 22-FEB-81 1250 1217
JONES 02-APR-81 2975 1942
BLAKE 01-MAY-81 2850 2169
CLARK 09-JUN-81 2450 2758
TURNER 08-SEP-81 1500 1975
MARTIN 28-SEP-81 1250 1375
KING 17-NOV-81 5000 2583
JAMES 03-DEC-81 950 2340
FORD 03-DEC-81 3000 2340
MILLER 23-JAN-82 1300 2563
SCOTT 09-DEC-82 3000 3000
ADAMS 12-JAN-83 1100 2050
再看看CLARK，我們已知道他在本組中的範圍視窗，可以看到平均工資2758由（2975+2850+2450）/3得來，是CLARK行和其 之前的JONES和BLAKE行工資的平均數。資料必須按由小到大順序排列。
Row Windows行視窗：
行視窗為實際機關，是包括在視窗中實際行數。例如可以計算一給定記錄的平均工資，該記錄包括其之前或之後雇傭的員工（至多5名），具體如下：
SELECT ename, hiredate, sal,
AVG(sal)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING) AvgAsc,
COUNT(*)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING) CntAsc,
AVG(sal)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) AvgDes,
COUNT(*)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) CntDes
FROM emp
ORDER BY hiredate
/
ENAME HIREDATE SAL AVGASC CNTASC AVGDES CNTDES
---------- --------- ----- ------ ------ ------ ------
SMITH 17-DEC-80 800 800 1 1988 6
ALLEN 20-FEB-81 1600 1200 2 2104 6
WARD 22-FEB-81 1250 1217 3 2046 6
JONES 02-APR-81 2975 1656 4 2671 6
BLAKE 01-MAY-81 2850 1895 5 2675 6
CLARK 09-JUN-81 2450 1988 6 2358 6
TURNER 08-SEP-81 1500 2104 6 2167 6
MARTIN 28-SEP-81 1250 2046 6 2417 6
KING 17-NOV-81 5000 2671 6 2392 6
JAMES 03-DEC-81 950 2333 6 1588 4
FORD 03-DEC-81 3000 2358 6 1870 5
MILLER 23-JAN-82 1300 2167 6 1800 3
SCOTT 09-DEC-82 3000 2417 6 2050 2
ADAMS 12-JAN-83 1100 2392 6 1100 1
.該視窗中包括6行，現有行及此行“之前”的5行，其中“之前”由ORDER BY語句定義。對于ROW（行）的劃分，不受RANGE（範圍）劃分的限制——資料可以是任何類型，order by可包括許多列。注意，也要選擇COUNT（*），可以說明是多少行的平均值。從ALLEN記錄可以清楚看到，他之前雇傭員工平均工資的計算使用了2個 記錄，他之後雇傭員工平均工資的計算使用了6個記錄。
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) last_hire,
hiredate - LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) days_last,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) next_hire,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) - hiredate days_next
FROM emp
ORDER BY deptno, hiredate
/
Dep Ename Hired LastHired DaysLast NextHire NextDays
--- ------ --------- --------- -------- --------- --------
10 CLARK 09-JUN-81 17-NOV-81 161
KING 17-NOV-81 09-JUN-81 161 23-JAN-82 67
MILLER 23-JAN-82 17-NOV-81 67
20 SMITH 17-DEC-80 02-APR-81 106
JONES 02-APR-81 17-DEC-80 106 03-DEC-81 245
FORD 03-DEC-81 02-APR-81 245 09-DEC-82 371
SCOTT 09-DEC-82 03-DEC-81 371 12-JAN-83 34
ADAMS 12-JAN-83 09-DEC-82 34
30 ALLEN 20-FEB-81 22-FEB-81 2
WARD 22-FEB-81 20-FEB-81 2 01-MAY-81 68
BLAKE 01-MAY-81 22-FEB-81 68 08-SEP-81 130
TURNER 08-SEP-81 01-MAY-81 130 28-SEP-81 20
MARTIN 28-SEP-81 08-SEP-81 20 03-DEC-81 66
JAMES 03-DEC-81 28-SEP-81 66
LEAD和LAG例程可看作對分組進行索引。使用這些函數可以通路任何一行。上面例子表明KING記錄包括之前一行（上一雇傭）和下一行（下一） 中的資料（加粗紅色字型）。可以很容易的通路排序後的分組中目前記錄之前或之後記錄中的字段。
LAG
LAG ( value_expr [, offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
. LAG無須自合并就可以擷取同一個表格中的多行記錄。知道來自查詢中的多行以及光标的位置，LEAD就可以進入位置指定區距之前的行。
如果不指定區距，預設值為1。如果區距超出視窗範圍，則傳回可指定預設值。如不指定預設值，則預設值為null。
EMP表中的每個人之前雇傭員工的：
SELECT ename,hiredate,sal,
LAG(sal, 1, 0)
OVER (ORDER BY hiredate) AS PrevSal
FROM emp
WHERE job = 'CLERK';
Ename Hired SAL PREVSAL
------ --------- ----- -------
SMITH 17-DEC-80 800 0
JAMES 03-DEC-81 950 800
MILLER 23-JAN-82 1300 950
ADAMS 12-JAN-83 1100 1300
LEAD
LEAD ( value_expr [, offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
LEAD不用自合并就可同時進入一個表格中的多行。知道來自查詢中的多行以及光标的位置，LEAD就可以進入位置指定區距之後的行。
如不指定區距，則預設值為1。如區距超出視窗範圍則傳回任意預設值。如不指定預設值，預設值為0。
例，EMP表中的每個員工，他們之後雇傭員工的雇傭日期：
SELECT ename, hiredate,
LEAD(hiredate, 1)
OVER (ORDER BY hiredate) AS NextHired
FROM emp WHERE deptno = 30;
Ename Hired NEXTHIRED
------ --------- ---------
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 01-MAY-81
BLAKE 01-MAY-81 08-SEP-81
TURNER 08-SEP-81 28-SEP-81
MARTIN 28-SEP-81 03-DEC-81
JAMES 03-DEC-81
Determine the First Value / Last Value of a Group确定組中的第一個值/最後一個值：
FIRST_VALUE與LAST_VALUE函數可以選擇一組中的第一行和最後一行。這些行很有用，因為它們經常用作計算的基線。
Example例：例為每個部門的每名員工、最高工資員工的姓名。
SELECT deptno, ename, sal,
FIRST_VALUE(ename)
OVER (PARTITION BY deptno
ORDER BY sal ASC) AS MIN_SAL_HAS
FROM emp
ORDER BY deptno, ename;
DEPTNO ENAME SAL MIN_SAL_HAS
---------- ---------- ---------- -----------
10 CLARK 2450 MILLER
KING 5000 MILLER
MILLER 1300 MILLER
20 ADAMS 1100 SMITH
FORD 3000 SMITH
JONES 2975 SMITH
SCOTT 3000 SMITH
SMITH 800 SMITH
30 ALLEN 1600 JAMES
BLAKE 2850 JAMES
JAMES 950 JAMES
MARTIN 1250 JAMES
TURNER 1500 JAMES
WARD 1250 JAMES
下例中為每個部門的每名員工、最高工資員工的姓名。
SELECT deptno, ename, sal,
FIRST_VALUE(ename)
OVER (PARTITION BY deptno
ORDER BY sal DESC) AS MAX_SAL_HAS
FROM emp
ORDER BY deptno, ename;
DEPTNO ENAME SAL MAX_SAL_HAS
---------- ---------- ---------- -----_-----
10 CLARK 2450 KING
KING 5000 KING
MILLER 1300 KING
20 ADAMS 1100 FORD
FORD 3000 FORD
JONES 2975 FORD
SCOTT 3000 FORD
SMITH 800 FORD
30 ALLEN 1600 BLAKE
BLAKE 2850 BLAKE
JAMES 950 BLAKE
MARTIN 1250 BLAKE
TURNER 1500 BLAKE
WARD 1250 BLAKE
下例為第30個部門中的每名員工、工資最低員工的姓名，使用内聯視圖。
SELECT deptno, ename, sal,
FIRST_VALUE(ename)
OVER (ORDER BY sal ASC) AS MIN_SAL_HAS
FROM (SELECT * FROM emp WHERE deptno = 30)
DEPTNO ENAME SAL MIN_SAL_HAS
---------- ---------- ---------- -----------
30 JAMES 950 JAMES
MARTIN 1250 JAMES
WARD 1250 JAMES
TURNER 1500 JAMES
ALLEN 1600 JAMES
BLAKE 2850 JAMES
交叉表查詢，或者pivot查詢，用稍微不同的方法将這些資料分組。交叉表查詢可根據三行（一行代表一個項目），每行有三列（第一列列出項目，然 後一列代表一年）得出結果——如下：
Example例：
如果需要以列的形式顯示每個部門中工資最多的3個人，查詢為每個部門傳回一行，一行有4列。DEPTNO，該部門中工資最高的人，工資第二高人的 名字，依次類推。使用分析函數很容易做到，不采用有分析函數這将是不可能的。
SELECT deptno,
MAX(DECODE(seq,1,ename,null)) first,
MAX(DECODE(seq,2,ename,null)) second,
MAX(DECODE(seq,3,ename,null)) third
FROM (SELECT deptno, ename,
row_number()
OVER (PARTITION BY deptno
ORDER BY sal desc NULLS LAST) seq
FROM emp)
WHERE seq <= 3
GROUP BY deptno
/
DEPTNO FIRST SECOND THIRD
---------- ---------- ---------- ----------
10 KING CLARK MILLER
20 SCOTT FORD JONES
30 BLAKE ALLEN TURNER
注意内查詢，它按照工資高低根據部門為每名員工賦予一個序列（RowNr）。
SELECT deptno, ename, sal,
row_number()
OVER (PARTITION BY deptno
ORDER BY sal desc NULLS LAST) RowNr
FROM emp;
DEPTNO ENAME SAL ROWNR
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 2
20 JONES 2975 3
20 ADAMS 1100 4
20 SMITH 800 5
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
30 WARD 1250 4
30 MARTIN 1250 5
30 JAMES 950 6
外查詢中DECODE隻保留含有序列1、2或3的記錄行，并将它們分派到正确的“列”。GROUP BY去掉多餘行，隻剩下壓縮結果。如果沒有根據deptno分組的合計函數MAX，結果會更容易了解。
SELECT deptno,
DECODE(seq,1,ename,null) first,
DECODE(seq,2,ename,null) second,
DECODE(seq,3,ename,null) third
FROM (SELECT deptno, ename,
row_number()
OVER (PARTITION BY deptno
ORDER BY sal desc NULLS LAST) seq
FROM emp)
WHERE seq <= 3
/
DEPTNO FIRST SECOND THIRD
---------- ---------- ---------- ----------
10 KING
10 CLARK
10 MILLER
20 SCOTT
20 FORD
20 JONES
30 BLAKE
30 ALLEN
30 TURNER
通過GROUP BY列DEPTNO（按DEPTNO分組列）應用MAX合計函數。在上面任意給出的DEPTNO中，隻有一行将對FIRST有非null值，其餘行将永遠 是NULL。MAX函數将選出非NULL值并儲存。是以，group by和MAX将會壓縮結果集，去掉NULL值并給出我們想要的值。
Conclusion結論：
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)