《oracle函数介绍著名函数之聚合函数》由会员分享,可在线阅读,更多相关《oracle函数介绍著名函数之聚合函数(3页珍藏版)》请在金锄头文库上搜索。
1、ORACLE函数介绍第三篇 著名函数之聚合函数 2007.8.16注:N表示数字型,C表示字符型,D表示日期型,表示内中参数可被忽略,fmt表示格式。聚合函数可被用于select,order by以及having子句中。其运算可以基于group by的结果,也可以直接对所有记录进行运算。当然,这种类型语句相信大家平常也用的比较多,概念性的就不多介绍了,有一点需要注意的是,除了count和grouping,其它的统计运算均会忽略值为null的列。为更好演示此类函数应用,我们需要首先构建一个测试表,如下:create table tmp1(col varchar2(10),value number
2、(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 int
3、o 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。本类函数中部分函数同时也属于分析函数。其做为分析函数时的具体用法将会在后面章节介绍,本节不做描述。1、 AVG(distinct|all
4、n) 求取记录集中的平均值。例如:SELECT col, avg(value) FROM tmp1 GROUP BY col ORDER BY col2、 COUNT(distinct|allcol) 该函数返回查询涉及到的记录行数。例如:SELECT col, count(0) FROM tmp1 GROUP BY col ORDER BY col;SELECT count(col), count(distinct col) FROM tmp1;3、 DENSE_RANK(n1,n2.) WITHIN GROUP (ORDER BY col1 desc|asc nulls first|las
5、t ,col2 desc|asc nulls first|last.) 计算指定值在记录集中的排序值。函数的参值必须一一对应group中的列,并且二者数据类型应该一致。至于order by子句中的nulls first|last则是用来设置记录集中值为null的列的排序在前或在后。例如:计算数值100在记录集中的排序值。SELECT dense_rank(100) within group( order by value) FROM tmp14、 RANK() 参数及形式完全与上同,二区最大的区别是:RANK函数在处理指定数值在记录集中的排序值时,如果值有重复,则后面的排序值会跳过这个值,直接
6、从当前排序值重复记录数开始,而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;5、 FIRST() 此函数格式描述看起来极复杂无比,其实也确实复杂无比。其本质是从DENSE_RANK
7、返回的集合中取出排在第一的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录,功能与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 col6、 LAST 形式参数等均与上同,不过就是把FIRST换成LAST即可。FIRST是取第一行,LAST则是取最后一行。例如:7、 MAX(dist
8、inct|all col) 取同组序列数据集中最大值。例如:SELECT col, MAX(value) Max Value, MIN(value) Min Value FROM tmp1 GROUP BY col8、 MIN(distinct|all col) 取同组序列数据集中最小值。形式参数均与上同,不详述例如:SELECT col, MAX(value) Max Value, MIN(value) Min Value FROM tmp1 GROUP BY col9、 SUM(distinct|all col) 取指定列所在分组序列的值的和并返回。例如:SELECT col, SUM(value) Sum Value FROM tmp1 GROUP BY col10、 GROUPING(col) cube、rollup子句的辅助函数,并且必须配合cube或rollup才能使用。用来确定当前记录行是否是由cube或rollup生成,如果是则返回1,不则返回0。例如:SELECT col, sum(value), grouping(value) FROM tmp1 GROUP BY rollup(col, value)三思笔记-Oracle函数介绍-著名及非著名函数 http:/ QQ:5454589君三思