ORACLE函式介紹第四篇 非著名函式之聚合函式

oracle函式介紹(4) 非著名函式之聚合函式(含如下函式介紹)：
CUME_DIST
GROUP_ID
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
STDDEV
STDDEV_SAMP
STDDEV_POP
VAR_POP
VAR_SAMP
VARIANCE

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　聚合函式可被用於select,order by以及having子句中。其運算可以基於group by的結果，也可以直接對所有記錄進行運算。當然，這種型別語句相信大家平常也用的比較多，概念性的就不多介紹了，有一點需要注意的是，除了count和grouping之後，其它的統計運算均會忽略值為null的列。

　　注意，多數聚合函式都支援DISTINCT子句(distinct是什麼？過濾記錄集中重複記錄用的)，如果不指定則預設針對所有計錄，舉個最簡單的例子：select count(col),count(distinct col) from tmp1。
　　本類函式中部分函式同時也屬於分析函式。其做為分析函式時的具體用法將會在後面章節介紹，本節不做描述。

CUME_DIST(expr[,expr]...) WITHIN GROUP
(ORDER BY
expr [DESC | ASC] [NULLS {FIRST | LAST}]
[,expr [DESC | ASC] [NULLS {FIRST | LAST}]]...)非常有意思的一個函式。查詢指定數值在指定分組序列中的相對位置，返回值是介於 0 和 1 之間的小數值。我們後面還會再講幾個相關的函式，均是實現型別功能，只是值的表現形式不同。
例如：SELECT CUME_DIST(120) WITHIN GROUP (ORDER BY value) FROM TMP1

GROUP_ID() 該函式必須配合group by子句使用。主要是用來區分group by 生成的記錄集中是否是被重複生成的記錄，如果該條記錄是則返回1，否則返回0。
例如：SELECT t.col, group_id() FROM TMP1 t GROUP BY col, ROLLUP(col)

PERCENT_RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)本函式語法及形式與cume_dist非常相似，也是返回指定資料在指定分組序列中所佔的相對位置，不過與cume_dist不同的是。不管指定值與分組序列中某值是否重複，均將此值視為序列一部分，而cume_dist則不同。如果指定值與分組序列中某值重複，則將二值視為一個值處理(上帝保佑，但願俺講明白了，廢話不說，大家通過示例來理解吧)。
例如：SELECT PERCENT_RANK(120) WITHIN GROUP( ORDER BY value) FROM TMP1

PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 根據輸入值返回該值對應的分組序列中數值。輸入值應該是介於0到1之間。原因請參照前面的PERCENT_RANK函式。
如果分組序列中沒有存在對應值的話，會根據如下規則來計算返回值：
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)

例如：
SELECT col, max(value), min(value), sum(value),
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,
PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b
FROM TMP1
group by col;

PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 語法與形式與上例相同，不過在計算方法上略有差異。如果分組序列中沒有對應值的話，那麼將會返回該序列中最近最大的一個值。

相對而言，我認為某些情況下本函式被應用的機率還是相當大地，起碼比上述幾個函式的應用範圍會廣。

例如：
SELECT col, max(value), min(value),
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,
PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b
FROM TMP1
group by col;

STDDEV([DISTINCT|ALL] n) 該函式返回樣本的標準偏差。
例如：SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;

STDDEV_SAMP(n) 語法與形式與上同，其與STDDEV函式最大的區別是，如果該分組序列只有一行的話，則STDDEV_SAMP函式返回空值，而STDDEV則返回0。
例如：SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;

STDDEV_POP(n) 返回該分組序列總體標準偏差。
例如：SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;

VAR_POP(n) 該函式返回分組序列的總體方差，VAR_POP進行如下計算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)。
例如：SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;

VAR_SAMP(n) 與上類似，該函式返回分組序列的樣本方差，，其計算公式為：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)。
例如：SELECT col, VAR_POP(value),VAR_SAMP(value) FROM TMP1 GROUP BY col;

VARIANCE(n) 該函式返回分組序列方差，Oracle計算該變數如下：
如果表示式中行數為1，則返回0，如果表示式中行數大於1，則返回VAR_SAMP
例如：SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUP BY col;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-22281/，如需轉載，請註明出處，否則將追究法律責任。