Oracle聚合函式/分析函式

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()可以美化一下效果。