ORACLE函式介紹第七篇 非著名函式之分析函式

oracle函式介紹(7) 非著名函式之分析函式(含如下函式介紹)：
CUME_DIST
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RATIO_TO_REPORT
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP
VARIANCE

CUME_DIST() OVER([partition_clause] order_by_clause) 返回該行在分組序列中的相對位置，返回值介於0到1之間。注意喲，如果order by的列是desc，則該分組內最大的行返回列值1，如果order by為asc，則該分組內最小的行返回列值1。
例如：SELECT col, value, CUME_DIST() OVER(ORDER BY value DESC) FROM tmp1;

NTILE(n) OVER([partition_clause] order_by_clause)
ntile是個很有意思的統計函式。它會按照你指定的組數(n)對記錄做分組
例如：SELECT t.*,ntile(5) over(order by value desc) FROM tmp1 t;

PERCENT_RANK() OVER([partition_clause] order_by_clause) 與CUME_DIST類似，本函式返回分組序列中各行在分組序列的相對位置。其返回值也是介於0到1之間，不過其起始值始終為0而終結值始終為1。
例如：SELECT col, value, PERCENT_RANK() OVER(ORDER BY value) FROM tmp1;

PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)
本函式功能與前面聚合函式處介紹的完全相同，只是一個是聚合函式，一個是分析函式。
例如：
--聚合函式
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;
--分析函式
SELECT col,
value,
sum(value) over(partition by col) "Sum",
PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",
PERCENTILE_CONT(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"
FROM TMP1;

PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)
本函式功能與前面聚合函式處介紹的完全相同，只是一個是聚合函式，一個是分析函式。
例如：
--聚合函式
SELECT col, max(value), min(value), sum(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;
--分析函式
SELECT col,
value,
sum(value) over(partition by col) "Sum",
PERCENTILE_DISC(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",
PERCENTILE_DISC(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"
FROM TMP1;

RATIO_TO_REPORT(col) over ([partition_clause]) 本函式計算本行col列值在該分組序列sum(col)中所佔比率。如果col列為空，則返回空值。
例如：
SELECT col, value,
RATIO_TO_REPORT(value) OVER(PARTITION BY col) "RATIO_TO_REPORT"
FROM TMP1

STDDEV ([distinct|all] col) OVER (analytic_clause) 返回列的標準偏差。
例如：
--聚合函式
SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;
--分析函式
SELECT col, value,
STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV"
FROM TMP1;

STDDEV_SAMP(col) OVER (analytic_clause) 功能與上相同，與STDDEV不同地方在於如果該分組序列只有一行的話，則STDDEV_SAMP函式返回空值，而STDDEV則返回0。
例如：
--聚合函式
SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;
--分析函式
SELECT col, value,
STDDEV(value) OVER(PARTITION BY col ORDER BY value) "STDDEV",
STDDEV_SAMP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_SAMP"
FROM TMP1;

STDDEV_POP(col) OVER (analytic_clause) 返回該分組序列總體標準偏差
例如：
--聚合函式
SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;
--分析函式
SELECT col, value,
STDDEV_POP(value) OVER(PARTITION BY col ORDER BY value) "STDDEV_POP"
FROM TMP1;

VAR_POP(col) OVER (analytic_clause) 返回分組序列的總體方差，VAR_POP進行如下計算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
例如：
--聚合函式
SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;
--分析函式
SELECT col, value,
VAR_POP(value) OVER(PARTITION BY col ORDER BY value) "VAR_POP"
FROM TMP1;

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

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

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