分析函數(shù)用于計(jì)算一組行的聚合值，由分析函數(shù)語(yǔ)法定義的行集叫做窗口，窗口的大小由物理行數(shù)或邏輯間隔決定。每一行上的滑動(dòng)窗口都是已定義的，窗口決定了對(duì)某一行而言進(jìn)行計(jì)算的行的范圍。從查詢(xún)結(jié)果和查詢(xún)語(yǔ)法來(lái)看，分析函數(shù)與聚合函數(shù)主要有 3 點(diǎn)不同：

  • 1、聚合函數(shù)每組只返回一條記錄，而分析函數(shù)每組可返回多條記錄。

  • 2、在單個(gè)查詢(xún)中，多個(gè)聚合函數(shù)只能作用于同一個(gè)分組，而不同分析函數(shù)可作用于不同分組。

  • 3、在分組查詢(xún)中，查詢(xún)列表只能出現(xiàn)聚合函數(shù)或 GROUP BY 子句中出現(xiàn)過(guò)的字段或常量或表達(dá)式，而分析函數(shù)沒(méi)有這些限制。

1、函數(shù)語(yǔ)法

我理解的分析函數(shù)無(wú)非也就是由一或多個(gè)普通的函數(shù)或子句按照一定的規(guī)則構(gòu)成的復(fù)雜數(shù)據(jù)分析函數(shù)。換言之，分析函數(shù)內(nèi)部有些函數(shù)或子句的語(yǔ)法規(guī)則之前已經(jīng)講述過(guò)了，這里將不再贅述，本節(jié)將著重講述分析函數(shù)所特有的一些函數(shù)或子句的語(yǔ)法。

1.1、語(yǔ)法概述

有很多網(wǎng)站都把分析函數(shù)稱(chēng)之為窗口函數(shù)，又稱(chēng) OVER 為開(kāi)窗函數(shù)，還有些似是而非的概念我本人也不甚了解。我特地查閱過(guò) 《Oracle Database SQL Reference 10g Release 2》Analytic Functions，語(yǔ)法如下：

analytic_function([arguments]) OVER([analytic_clause])

參數(shù)說(shuō)明：

  • analytic_function：分析函數(shù)的名稱(chēng)。Oracle 10g R2 中內(nèi)置了 30 個(gè)分析函數(shù)。

  • arguments：分析函數(shù)的參數(shù)。內(nèi)置的分析函數(shù)一般帶 0~3 個(gè)參數(shù)，參數(shù)可以是任何數(shù)字類(lèi)型或是可以隱式轉(zhuǎn)換為數(shù)字類(lèi)型的數(shù)據(jù)類(lèi)型。

  • OVER：用來(lái)標(biāo)識(shí)函數(shù)是個(gè)分析函數(shù)。對(duì)于即可作為聚合函數(shù)又可作為分析函數(shù)的函數(shù)，Oracle 無(wú)法識(shí)別，必須用 OVER 來(lái)標(biāo)識(shí)此函數(shù)為分析函數(shù)。但并不是說(shuō)只可作為分析函數(shù)的函數(shù)就無(wú)需標(biāo)識(shí)，是分析函數(shù)就必須用 OVER 關(guān)鍵字來(lái)標(biāo)識(shí)。另外 OVER 后面的一對(duì)小括號(hào)也是必須的，即便括號(hào)中什么都不包含。

  • analytic_clause：用來(lái)確定分析規(guī)則。語(yǔ)法是：[query_partition_clause][order_by_clause[windowing_clause]]，語(yǔ)法中的 3 個(gè)子句都是可選的，但 windowing_clause 必須依賴(lài)于 order_by_clause 而存在。

    • query_partition_clause：分組子句，用于確定窗口，與 GROUP BY 語(yǔ)句的語(yǔ)法類(lèi)似。

    • order_by_clause：排序子句，用于確定窗口規(guī)則，與 ORDER BY 語(yǔ)句的語(yǔ)法類(lèi)似。

    • windowing_clause：窗口范圍子句，用于確定分組中當(dāng)前的計(jì)算范圍。

1.2、窗口詳解

在 《Oracle Database SQL Reference 10g Release 2》 中給出 windowing_clause 語(yǔ)法示意圖如下：

  • ROWS：用于指定窗口由物理行構(gòu)成，即符合指定的數(shù)據(jù)行的范圍。

  • RANGE：用于指定窗口由邏輯偏移量構(gòu)成，即符合指定的邏輯條件的范圍。

  • BETWEEN...AND：用于指定窗口的起始點(diǎn)和終結(jié)點(diǎn)。

  • UNBOUNDED PRECEDING：用于指明窗口開(kāi)始于分組的第一行。

  • CURRENT ROW：作為起始點(diǎn)，指明窗口開(kāi)始于當(dāng)前行或當(dāng)前行的值；作為終結(jié)點(diǎn)，指明窗口結(jié)束于當(dāng)前行或當(dāng)前行的值。

  • UNBOUNDED FOLLOWING：用于指明窗口結(jié)束于分組的最后一行。

  • value_expr：物理或邏輯偏移量的表達(dá)式。

無(wú)論是窗口大小是由物理行數(shù)(ROWS)確定，還是由邏輯間隔(RANGE)確定，在分組中窗口總是從上往下滑動(dòng)。窗口范圍可以由 BETWEEN...AND 限定，也可以不用 BETWEEN...AND，不用則表示窗口到當(dāng)前行或值結(jié)束。

1.2.1、ROWS 窗口

ROWS 窗口是由分組排序后分組中若干連續(xù)的行所構(gòu)成的窗口。在 ROWS 窗口中 value_expr 是物理偏移量，它必須是常量或值為非負(fù)數(shù)的表達(dá)式。合法的 ROWS 窗口范圍定義共有 16 種，列舉如下：

1、窗口開(kāi)始于分組第一行，結(jié)束于分組最后一行。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

2、窗口開(kāi)始于分組第一行，結(jié)束于當(dāng)前行。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS UNBOUNDED PRECEDING

3、窗口開(kāi)始于分組第一行，結(jié)束于當(dāng)前行的前 value_expr 行。

ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING

4、窗口開(kāi)始于分組第一行，結(jié)束于當(dāng)前行的后 value_expr 行。

ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING

5、窗口開(kāi)始于當(dāng)前行，結(jié)束于分組最后一行。

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

6、窗口開(kāi)始于當(dāng)前行，結(jié)束于當(dāng)前行。

ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS CURRENT ROW

7、窗口開(kāi)始于當(dāng)前行，結(jié)束于當(dāng)前行的前 value_expr 行。

ROWS BETWEEN CURRENT ROW AND value_expr PRECEDING

8、窗口開(kāi)始于當(dāng)前行，結(jié)束于當(dāng)前行的后 value_expr 行。

ROWS BETWEEN CURRENT ROW AND value_expr FOLLOWING

9、窗口開(kāi)始于當(dāng)前行的前 value_expr 行，結(jié)束于當(dāng)前行。

ROWS BETWEEN value_expr PRECEDING AND CURRENT ROW
ROWS value_expr PRECEDING

10、窗口開(kāi)始于當(dāng)前行的前 value_expr1 行，結(jié)束于當(dāng)前行的前 value_expr2 行。前提是要滿(mǎn)足 value_expr1 >= value_expr2。

ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING

11、窗口開(kāi)始于當(dāng)前行的前 value_expr1 行，結(jié)束于當(dāng)前行的后 value_expr2 行。

ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING

12、窗口開(kāi)始于當(dāng)前行的后 value_expr 行，結(jié)束于分組最后一行。

ROWS BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING

13、窗口開(kāi)始于當(dāng)前行的后 value_expr1 行，結(jié)束于當(dāng)前行的后 value_expr2 行。前提是要滿(mǎn)足 value_expr1 <= value_expr2。

ROWS BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING

1.2.2、RANGE 窗口

RANGE 窗口是由分組排序后分組中滿(mǎn)足指定邏輯條件的行所構(gòu)成的窗口。在 RANGE 窗口中 value_expr 為邏輯偏移量，它必須是常量或值為非負(fù)數(shù)的表達(dá)式或間隔值。當(dāng) value_expr 值是一個(gè)數(shù)字時(shí)，排序字段必須是數(shù)字或日期類(lèi)型；當(dāng) value_expr 值是一個(gè)間隔值時(shí)，排序字段必須是一個(gè)日期類(lèi)型。合法的 RANGE 窗口范圍定義也有 16 種，列舉如下：

1、升序排序時(shí)，表達(dá)式介于第一行的值和最后一行的值之間；降序排序時(shí)，表達(dá)式介于最后一行的值和第一行的值之間。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

2、升序排序時(shí)，表達(dá)式介于第一行的值和當(dāng)前行的值之間；降序排序時(shí)，表達(dá)式介于當(dāng)前行的值和第一行的值之間。若不指定窗口，則默認(rèn)為該窗口。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWRANGE UNBOUNDED PRECEDING

3、升序排序時(shí)，表達(dá)式介于第一行的值和當(dāng)前行的值 -value_expr 之間；降序排序時(shí)，表達(dá)式介于當(dāng)前行的值 -value_expr 和第一行的值之間。

RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING

4、升序排序時(shí)，表達(dá)式介于第一行的值和當(dāng)前行的值 +value_expr 之間；降序排序時(shí)，表達(dá)式介于當(dāng)前行的值 +value_expr 和第一行的值之間。

RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING

5、升序排序時(shí)，表達(dá)式介于當(dāng)前行的值和最后一行的值之間；降序排序時(shí)，表達(dá)式介于最后一行的值和當(dāng)前行的值之間。

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

6、表達(dá)式等于當(dāng)前行的值。

RANGE BETWEEN CURRENT ROW AND CURRENT ROWRANGE CURRENT ROW

7、表達(dá)式介于當(dāng)前行的值和當(dāng)前行的值 +value_expr 之間。

RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING

8、表達(dá)式介于當(dāng)前行的值 -value_expr 和最后一行的值之間。

RANGE BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWING

9、表達(dá)式介于當(dāng)前行的值 -value_expr 和當(dāng)前行的值之間。

RANGE BETWEEN value_expr PRECEDING AND CURRENT ROWRANGE value_expr PRECEDING

10、表達(dá)式介于當(dāng)前行的值 -value_expr1 和當(dāng)前行的值 -value_expr2 之間。前提是要滿(mǎn)足 value_expr1 >= value_expr2。

RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING

11、表達(dá)式介于當(dāng)前行的值 -value_expr1 和當(dāng)前行的值 +value_expr2 之間。

RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING

12、升序排序時(shí)，表達(dá)式介于當(dāng)前行的值 +value_expr 和最后一行的值之間；降序排序時(shí)，表達(dá)式介于最后一行的值和當(dāng)前行的值 +value_expr 之間。

RANGE BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING

13、表達(dá)式介于當(dāng)前行的值 +value_expr1 和當(dāng)前行的值 +value_expr2 之間。前提是要滿(mǎn)足 value_expr1 <= value_expr2。

RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING

2、函數(shù)用法

Oracle 10g R2 中內(nèi)置的分析函數(shù)有 38 個(gè)，本節(jié)按函數(shù)用途將其分為 4 類(lèi)，分別是：普通統(tǒng)計(jì)函數(shù)、數(shù)據(jù)排序函數(shù)、數(shù)據(jù)分布函數(shù)及統(tǒng)計(jì)分析函數(shù)，并逐一給出函數(shù)釋義和用法示例。

2.1、普通統(tǒng)計(jì)類(lèi)函數(shù)

  • MAX：對(duì)組內(nèi)的數(shù)據(jù)窗口中的字段或表達(dá)式求最大值。

  • MIN：對(duì)組內(nèi)的數(shù)據(jù)窗口中的字段或表達(dá)式求最小值。

  • AVG：對(duì)組內(nèi)的數(shù)據(jù)窗口中的字段或表達(dá)式求平均值。

  • SUM：對(duì)組內(nèi)的數(shù)據(jù)窗口中的字段或表達(dá)式求合計(jì)值。

  • COUNT：對(duì)組內(nèi)的數(shù)據(jù)窗口中的數(shù)據(jù)行進(jìn)行累計(jì)計(jì)數(shù)。

示例 1：

SELECT t.staff_name,t.dept_code,t.base_salary,t.post_salary,  MAX(t.post_salary) OVER(PARTITION BY t.dept_code) max_salary,  -- 部門(mén)最高崗位工資
  MIN(t.post_salary) OVER(PARTITION BY t.dept_code) min_salary,  -- 部門(mén)最低崗位工資
  AVG(t.post_salary) OVER(PARTITION BY t.dept_code) avg_salary,  -- 部門(mén)平均崗位工資
  SUM(t.post_salary) OVER(PARTITION BY t.dept_code) sum_salary,  -- 部門(mén)崗位工資之和
  COUNT(t.post_salary) OVER(PARTITION BY t.dept_code) cnt_salary -- 部門(mén)工資份數(shù)FROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 2：

SELECT t.staff_name,t.dept_code,t.base_salary,t.post_salary,  -- 部門(mén)內(nèi)截至當(dāng)前行的最高崗位工資
  MAX(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) max_salary,  -- 部門(mén)內(nèi)當(dāng)前行至最后一行的最低崗位工資
  MIN(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS CURRENT ROW) min_salary,  -- 部門(mén)內(nèi)第一行至前一行的平均崗位工資
  AVG(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) avg_salary,  -- 部門(mén)內(nèi)第一行至后一行的崗位工資之和
  SUM(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) sum_salary,  -- 部門(mén)內(nèi)截至當(dāng)前行的工資份數(shù)
  COUNT(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) cnt_salaryFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

2.2、數(shù)據(jù)排序類(lèi)函數(shù)

  • RANK()：根據(jù) ORDER BY 的排序結(jié)果，計(jì)算組間相對(duì)位置，可能跳號(hào)。組內(nèi)數(shù)據(jù)按 ORDER BY 子句排序，每一行都會(huì)得到一個(gè)序號(hào)，從而形成一個(gè)序列，該序列從 1 開(kāi)始，往后 ORDER BY 表達(dá)式的值每發(fā)生一次變化，該序列也隨之加 1。值相同的行將得到相同的序號(hào)（NULL 被認(rèn)為是相等的），后面行的序號(hào)將發(fā)生跳躍。如前兩行的序號(hào)為 1，則沒(méi)有序號(hào) 2，第 3 行將得到序號(hào) 3。

  • DENSE_RANK()：根據(jù) ORDER BY 的排序結(jié)果，計(jì)算組間相對(duì)位置，不會(huì)跳號(hào)。DENSE_RANK 與 RANK 類(lèi)似，也會(huì)得到一個(gè)首項(xiàng)為 1、公差為 1 的等差序列，值相同的行序號(hào)也相同（也認(rèn)為 NULL 是相等的），但序號(hào)不會(huì)跳躍。如前兩行的序號(hào)為 1，第 3 行將得到序號(hào) 2。

  • FIRST_VALUE(expression)：返回組內(nèi)數(shù)據(jù)窗口的第一個(gè)值。

  • LAST_VALUE(expression)：返回組內(nèi)數(shù)據(jù)窗口的最后一個(gè)值。

  • LAG(expression [, offset [, default] ])：可以訪問(wèn)結(jié)果集中當(dāng)前行之前的行而不用進(jìn)行自連接，這樣就可以從組中與當(dāng)前行一起選取當(dāng)前行之前的行。offset 是一個(gè)正整數(shù)，默認(rèn)值為 1，若不指定 offset，或 offset 的值超出窗口范圍，就啟用默認(rèn)值。

  • LEAD(expression [, offset [, default] ])：與 LAG 相反，LEAD 可以訪問(wèn)結(jié)果集中當(dāng)前行之后的行而不用進(jìn)行自連接，這樣就可以從組中與當(dāng)前行一起選取當(dāng)前行之后的行。offset 是一個(gè)正整數(shù)，默認(rèn)值為 1，若不指定 offset，或 offset 的值超出窗口范圍，就啟用默認(rèn)值。

  • ROW_NUMBER：返回有序組中一行的偏移量，按特定的排序分配行號(hào)。

  • FIRST：從緊湊排序后的結(jié)果集中篩選出排在最前面的一個(gè)值的行（可能是多行，因?yàn)橹悼赡芟嗟龋?/p>

  • LAST：從緊湊排序后的結(jié)果集中篩選出排在最后面的一個(gè)值的行（可能是多行，因?yàn)橹悼赡芟嗟龋?/p>

示例 1：

SELECT t.dept_code,t.staff_name,t.post_salary,  RANK() OVER(ORDER BY t.dept_code,t.post_salary) rank,  DENSE_RANK() OVER(ORDER BY t.dept_code,t.post_salary) dense_rank,  FIRST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code,t.post_salary) fist_value,  LAST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code,t.post_salary) last_value,
  LAG(t.post_salary,1,0) OVER(ORDER BY t.dept_code,t.post_salary) lag_value,  LEAD(t.post_salary,1,0) OVER(ORDER BY t.dept_code,t.post_salary) lead_value,
  ROW_NUMBER() OVER(ORDER BY t.dept_code,t.post_salary) row_numberFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 2：

SELECT t.dept_code,t.staff_name,t.birthday,t.post_salary,  RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) rank,  DENSE_RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) dense_rank,  FIRST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) fist_value,  LAST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) last_value,
  LAG(t.post_salary,1,0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) lag_value,  LEAD(t.post_salary,1,0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) lead_value,
  ROW_NUMBER() OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) row_numberFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 3：

SELECT t.staff_name,t.gender,t.dept_code,t.post_salary,  MIN(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value1,  MAX(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value2,  MIN(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value1,  MAX(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value2FROM demo.t_staff t ORDER BY t.gender,t.dept_code;

2.3、數(shù)據(jù)分布類(lèi)函數(shù)

  • RATIO_TO_REPORT(expr)：計(jì)算當(dāng)前行的值與組中所有行的值之和的比率。

  • NTILE(expr)：將一組有序的數(shù)據(jù)集分為若干桶，并為每一行分配相應(yīng)的桶編號(hào)。expr 必須是能被解析成正整數(shù)的值或表達(dá)式，如果帶小數(shù)，小數(shù)部分將被截取。桶編號(hào)從 1 開(kāi)始到 TRUNC(expr)，每桶的數(shù)據(jù)行數(shù)最多相差 1。如果行數(shù)不能被桶數(shù)整除，那么靠前的桶將優(yōu)先被填充，靠后的桶則會(huì)少一行數(shù)據(jù)。例如 expr=3，行數(shù)=16，則桶編號(hào)為 1 的有 6 行，桶編號(hào)為 2 或 3 的有 5 行。

  • CUME_DIST()：計(jì)算一組值的累計(jì)分布，并返回大于 0、小于或等于 1 的數(shù)，該數(shù)表示該行在組中的相對(duì)位置，值相等的相鄰行會(huì)得到相同的累計(jì)分布值。

  • PERCENT_RANK()：與 CUME_DIST 函數(shù)類(lèi)似，每行的值等于該行的行號(hào)先減去 1，再除以組中總行數(shù)減去 1，因此返回值總是大于或等于 0、小于或等于 1。

  • PERCENTILE_DISC(expr)：返回一個(gè)與分布

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-Over.html
版權(quán)聲明：本文為博客園博主 韓宗澤 原創(chuàng)，作者保留署名權(quán)！歡迎通過(guò)轉(zhuǎn)載、演繹或其它傳播方式來(lái)使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫(xiě)博客，水平有限，若有不當(dāng)之處，敬請(qǐng)批評(píng)指正，謝謝！

http://www.cnblogs.com/hanzongze/p/Oracle-Over.html