ORACLE函式介紹第三篇 著名函式之聚合函式
oracle函式介紹(3) 著名函式之聚合函式(含如下函式介紹):
AVG
COUNT
DENSE_RANK
RANK
FIRST
LAST
MAX
MIN
SUM
GROUPING
注:N表示數字型,C表示字元型,D表示日期型,[]表示內中引數可被忽略,fmt表示格式。
聚合函式可被用於select,order by以及having子句中。其運算可以基於group by的結果,也可以直接對所有記錄進行運算。當然,這種型別語句相信大家平常也用的比較多,概念性的就不多介紹了,有一點需要注意的是,除了count和grouping之後,其它的統計運算均會忽略值為null的列。
為更好演示此類函式應用,我們需要首先構建一個測試表,如下:
create table tmp1(col varchar2(10),value number(5));
insert into tmp1 values ('test1',60);
insert into tmp1 values ('test2',120);
insert into tmp1 values ('test3',460);
insert into tmp1 values ('test4',225);
insert into tmp1 values ('test5',119);
insert into tmp1 values ('test6',350);
insert into tmp1 values ('test7',23);
insert into tmp1 values ('test1',120);
insert into tmp1 values ('test3',69);
insert into tmp1 values ('test4',89);
insert into tmp1 values ('test6',145);
後續章節示例中也均以此表做例。
注意,多數聚合函式都支援DISTINCT子句(distinct是什麼?過濾記錄集中重複記錄用的),如果不指定則預設針對所有計錄,舉個最簡單的例子:select count(col),count(distinct col) from tmp1。
本類函式中部分函式同時也屬於分析函式。其做為分析函式時的具體用法將會在後面章節介紹,本節不做描述。
AVG([distinct|all]n) 求取記錄集中的平均值。
例如:SELECT col, avg(value) FROM tmp1 GROUP BY col ORDER BY col
COUNT([distinct|all]col) 該函式返回查詢涉及到的記錄行數。
例如:
SELECT col, count(0) FROM tmp1 GROUP BY col ORDER BY col;
SELECT count(col), count(distinct col) FROM tmp1;
DENSE_RANK(n1[,n2]...) WITHIN GROUP (ORDER BY col1 [desc|asc] [nulls first|last] [,col2 [desc|asc] [nulls first|last]]...) 計算指定值在記錄集中的排序值。函式的參值必須一一對應group中的列,並且二者資料型別應該一致。至於order by子句中的nulls first|last則是用來設定記錄集中值為null的列的排序在前或在後。
例如:計算數值100在記錄集中的排序值。
SELECT dense_rank(100) within group( order by value) FROM tmp1
RANK() 引數及形式完全與上同,二區最大的區別是:RANK函式在處理指定數值在記錄集中的排序值時,如果值有重複,則後面的排序值會跳過這個值,直接從當前排序值+重複記錄數開始,而DENSE_RANK則不會,排序值依然是個連續的序列。提到這個,在後面講分析函式講到row_number()時大家會又發現不同,先刨個坑,後面再填。
例如: insert into tmp1 values ('test2',120);
SELECT dense_rank(121) within group( order by value) FROM tmp1;
SELECT rank(121) within group( order by value) FROM tmp1;
FIRST() 此函式格式描述看起來極複雜無比,其實也確實複雜無比。其本質是從DENSE_RANK返回的集合中取出排在第一的行(可能多行,因為值可能相等),因此完整的語法需要在開始處加上一個集合函式以從中取出記錄,功能與FIRST_VALUE相同。
例如:
SELECT col,
MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",
MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"
FROM tmp1
GROUP BY col
LAST 形式引數等均與上同,不過就是把FIRST換成LAST即可。FIRST是取第一行,LAST則是取最後一行。
例如:
MAX([distinct|all] col) 取同組序列資料集中最大值。
例如:
SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"
FROM tmp1
GROUP BY col
MIN([distinct|all] col) 取同組序列資料集中最小值。形式引數均與上同,不詳述
例如:
SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"
FROM tmp1
GROUP BY col
SUM([distinct|all] col) 取指定列所在分組序列的值的和並返回。
例如:SELECT col, SUM(value) "Sum Value" FROM tmp1 GROUP BY col
GROUPING(col) cube、rollup子句的輔助函式,並且必須配合cube或rollup才能使用。用來確定當前記錄行是否是由cube或rollup生成,如果是則返回1,不則返回0。
例如:
SELECT col, sum(value), grouping(value)
FROM tmp1
GROUP BY rollup(col, value)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7607759/viewspace-22280/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE函式介紹第四篇 非著名函式之聚合函式Oracle函式
- ORACLE函式介紹第一篇 著名函式之單值函式Oracle函式
- ORACLE函式介紹第二篇 非著名函式之單值函式Oracle函式
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式
- ORACLE函式介紹第七篇 非著名函式之分析函式Oracle函式
- 原創:oracle聚合函式介紹Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- 【函式】Oracle TRIM函式語法介紹函式Oracle
- ORACLE函式介紹Oracle函式
- oracle 10g函式大全--聚合函式Oracle 10g函式
- oracle常用函式介紹Oracle函式
- oracle REPLACE 函式 介紹Oracle函式
- oracle 日期函式介紹Oracle函式
- MySQL函式大全(字串函式,數學函式,日期函式,系統級函式,聚合函式)MySql函式字串
- 【函式】Oracle中聚合函式rank()使用方法函式Oracle
- 聚合函式與數字函式函式
- 函式表示式和函式宣告簡單介紹函式
- Oracle 的基本函式介紹Oracle函式
- ORACLE函式介紹第五篇 分析函式簡述Oracle函式
- Oracle OCP(04):聚合函式Oracle函式
- Oracle 聚合函式詳解Oracle函式
- oracle 自定義聚合函式Oracle函式
- ORACLE 字串聚合函式 strCatOracle字串函式
- TypeScript 函式介紹TypeScript函式
- stoi函式介紹函式
- Stream聚合函式函式
- Django:聚合函式Django函式
- Oracle 8 的函式介紹(轉)Oracle函式
- Oracle 分析函式使用介紹(轉)Oracle函式
- ORACLE單行函式與多行函式之七:多行函式之分組函式示例Oracle函式
- 【函式】ORACLE函式大全函式Oracle
- Signal ()函式詳細介紹 Linux函式函式Linux
- ORACLE單行函式與多行函式之四:日期函式示例Oracle函式
- ORACLE單行函式與多行函式之六:通用函式示例Oracle函式
- 【函式】Oracle函式系列(2)--數學函式及日期函式函式Oracle
- Oracle 函式大全(字串函式,數學函式,日期函式,邏輯運算函式,其他函式)Oracle函式字串
- 【函式】Oracle EXTRACT()函式與to_char() 函式函式Oracle
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式