Oracle聚合函數/分析函數

原文位址：http://blog.csdn.net/nshk/archive/2008/03/21/2202088.aspx

參考文獻： 《expert one-on-one》、《Oracle 9i reference》

oracle函數分兩類：單行函數、多行函數 。多行函數也成為聚合函數、組合函數，參數為數組，資料大小為記錄數，這種數組不是普通進階語言的數組，是一種虛拟數組，當記錄數大時，會将資料寫入硬碟，記憶體中放的隻是影像。

oracle從8.1.6開始提供分析函數，用于計算基于組的某種聚合值。它和聚合函數的不同之處在于每個組傳回多行，聚合函數每個組隻傳回一行。

開窗函數：指定了分析函數工作的資料視窗大小，這個資料大小會随資料行數變化而變化，示例如下：

over（order by salary） 按照salary排序進行累計，order by是個預設的開窗函數

over（partition by deptno）按照部門分區

over（order by salary range between 50 preceding and 150 following）每行對應的資料視窗是之前行幅度值不超過50，之後行幅度值不超過150

over（order by salary rows between 50 preceding and 150 following）每行對應的資料視窗是之前50行，之後150行

over（order by salary rows between unbounded preceding and unbounded following）每行對應的資料視窗是從第一行到最後一行，等效：over（order by salary range between unbounded preceding and unbounded following）

AVG功能描述，用于計算一個組和資料視窗内表達式的平均值。

 sample:select avg(salary) over(partition by manager_id order by hire_date rows between 1 preceding and 1 following) as avg_salary from employee.

CORR，傳回一對表達式的相關系數。縮寫如下：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))，從統計上講，相關性是變量之間關聯的強度，變量之間的關聯意味着一定程度上一個變量的值可以由其他變量值進行預測，傳回一個-1~1的數，相關系數給出了關聯的強度，0表示不相關。

COVAR_POP，傳回一對表達式的總體協方差。

COVAR_SAMP，傳回一對表達式的樣本協方差。

COUNT，對組内發生的事情進行累計。如果指定*或一些非空常數，count将對所有行計數，如果指定一個表達式，count傳回表達式非空指派的計數，當有相同值出現時，這些相等的值都會被納入被計算的值；可以使用DISTINCT來記錄去掉一組中完全相同的資料後出現的行數。

CUME_DIST，計算一行在組内的相對位置。CUME_DIST總是傳回大于0、小于或等于1的數，該數表示該行在N行中的位置。

DENSE_RANK，根據ORDER BY子句中表達式的值，從查詢傳回的每一行，計算它們與其它行的相對位置。組内的資料按ORDER BY子句排序，然後給每一行賦一個号，進而形成一個序列，該序列從1開始，往後累加。每次ORDER BY表達式的值發生變化時，該序列也随之增加。有同樣值的行得到同樣的數字序号（認為null時相等的）。密集的序列傳回的時沒有間隔的數。

FIRST，從DENSE_RANK傳回的集合中取出排在最前面的一個值的行（可能多行，因為值可能相等），是以完整的文法需要在開始處加上一個集合函數以從中取出記錄。

FIRST_VALUE，傳回組中資料視窗的第一個值。

LAG，可以通路結果集中的其它行而不用進行自連接配接。它允許去處理遊标，就好像遊标是一個數組一樣。在給定組中可參考目前行之前的 行，這樣就可以從組中與目前行一起選擇以前的行。Offset是一個正整數，其預設值為1，若索引超出視窗的範圍，就傳回預設值（預設傳回的是組中第一 行），其相反的函數是LEAD

LAST，從DENSE_RANK傳回的集合中取出排在最後面的一個值的行（可能多行，因為值可能相等），是以完整的文法需要在開始處加上一個集合函數以從中取出記錄。

LAST_VALUE，傳回組中資料視窗的最後一個值。

LEAD，LEAD與LAG相反，LEAD可以通路組中目前行之後的行。Offset是一個正整數，其預設值為1，若索引超出視窗的範圍，就傳回預設值（預設傳回的是組中第一行）。

MAX，在一個組中的資料視窗中查找表達式的最大值。

MIN，在一個組中的資料視窗中查找表達式的最小值。

NTILE，将一個組分為"表達式"的散清單示，例如，如果表達式=4，則給組中的每一行配置設定一個數（從1到4），如果組中有20行， 則給前5行配置設定1，給下5行配置設定2等等。如果組的基數不能由表達式值平均分開，則對這些行進行配置設定時，組中就沒有任何percentile的行數比其它 percentile的行數超過一行，最低的percentile是那些擁有額外行的percentile。例如，若表達式=4，行數=21，則 percentile=1的有5行，percentile=2的有5行等等。

PERCENT_RANK，和CUME_DIST（累積配置設定）函數類似，對于一個組中給定的行來說，在計算那行的序号時，先減1，然後除以n-1（n為組中所有的行數）。該函數總是傳回0～1（包括1）之間的數。

PERCENTILE_RANK，傳回一個與輸入的分布百分比值相對應的資料值，分布百分比的計算方法見函數PERCENT_RANK，如果沒有正好對應的資料值，就通過下面算法來得到值：

RN = 1+ (P*(N-1)) 其中P是輸入的分布百分比值，N是組内的行數

CRN = CEIL(RN) FRN = FLOOR(RN)

if (CRN = FRN = RN) then

(value of expression from row at RN)

else

(CRN - RN) * (value of expression for row at FRN) +

(RN - FRN) * (value of expression for row at CRN)

注意：本函數與PERCENTILE_DISC的差別在找不到對應的分布值時傳回的替代值的計算方法不同。

PERCENTILE_DISC，傳回一個與輸入的分布百分比值相對應的資料值，分布百分比的計算方法見函數CUME_DIST，如果沒有正好對應的資料值，就取大于該分布值的下一個值。

注意：本函數與PERCENTILE_CONT的差別在找不到對應的分布值時傳回的替代值的計算方法不同。

RANK，根據ORDER BY子句中表達式的值，從查詢傳回的每一行，計算它們與其它行的相對位置。組内的資料按ORDER BY子句排序，然後給每一行賦一個号，進而形成一個序列，該序列從1開始，往後累加。每次ORDER BY表達式的值發生變化時，該序列也随之增加。有同樣值的行得到同樣的數字序号（認為null時相等的）。然而，如果兩行的确得到同樣的排序，則序數将随 後跳躍。若兩行序數為1，則沒有序數2，序列将給組中的下一行配置設定值3，DENSE_RANK則沒有任何跳躍。

RATIO_TO_REPORT，該函數計算expression/(sum(expression))的值，它給出相對于總數的百分比，即目前行對sum(expression)的貢獻。

REGR_ (Linear Regression) Functions

功能描述：這些線性回歸函數适合最小二乘法回歸線，有9個不同的回歸函數可使用。

REGR_SLOPE：傳回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)

REGR_INTERCEPT：傳回回歸線的y截距，等于

AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)

REGR_COUNT：傳回用于填充回歸線的非空數字對的數目

REGR_R2：傳回回歸線的決定系數，計算式為：

If VAR_POP(expr2) = 0 then return NULL

If VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1

If VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0 then

return POWER(CORR(expr1,expr),2)

REGR_AVGX：計算回歸線的自變量(expr2)的平均值，去掉了空對(expr1, expr2)後，等于AVG(expr2)

REGR_AVGY：計算回歸線的應變量(expr1)的平均值，去掉了空對(expr1, expr2)後，等于AVG(expr1)

REGR_SXX： 傳回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)

REGR_SYY： 傳回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)

REGR_SXY: 傳回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

ROW_NUMBER，傳回有序組中一行的偏移量，進而可用于按特定标準排序的行号。

STDDEV ，計算目前行關于組的标準偏離（Standard Deviation）。

STDDEV_POP，該函數計算總體标準偏離，并傳回總體變量的平方根，其傳回值與VAR_POP函數的平方根相同（Standard Deviation－Population）。

STDDEV_SAMP，該函數計算累積樣本标準偏離，并傳回總體變量的平方根，其傳回值與VAR_POP函數的平方根相同（Standard Deviation－Sample）。

SUM，該函數計算組中表達式的累積和。

VAR_POP，（Variance Population）該函數傳回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

(SUM(expr2) - SUM(expr2) / COUNT(expr)) / COUNT(expr)。

VAR_SAMP，（Variance Sample）該函數傳回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)。

VARIANCE，該函數傳回表達式的變量，Oracle計算該變量如下：

如果表達式中行數為1，則傳回0

如果表達式中行數大于1，則傳回VAR_SAMP

group by 語句在基本文法外，還支援rollup 和 cube語句。

ROLLUP(A, B, C)，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。

GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。

GROUPING_ID()可以美化一下效果。