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(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',); insert into tmp1 values ('test6',145);
后续章节示例中也均以此表做例。
注意，多数聚合函数都支持DISTINCT子句(distinct是什么？过滤记录集中重复记录用的)，如果不指定则默认针对所有计录，举个最简单的例子：select count(col),count(distinct col) from tmp1。 本类函数中部分函数同时也属于分析函数。其做为分析函数时的具体用法将会在后面章节介绍，本节不做描述。
1、AVG([distinct|all]n) 求取记录集中的平均值。
例如：SELECT col, avg(value) FROM tmp1 GROUP BY col ORDER BY col
2、COUNT([distinct|all]col) 该函数返回查询涉及到的记录行数。
例如：
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|last] [,col2 [desc|asc]
[三思笔记]-Oracle函数介绍--著名及非著名函数 http://www.5ienet.com/ QQ：54545君三思
[nulls first|last]]...) 计算指定值在记录集中的排序值。函数的参值必须一一对应group中的列，并且二者数据类型应该一致。至于order by子句中的nulls first|last则是用来设置记录集中值为null的列的排序在前或在后。
例如：计算数值100在记录集中的排序值。
SELECT dense_rank(100) within group( order by value) FROM tmp1
4、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;
5、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
6、LAST 形式参数等均与上同，不过就是把FIRST换成LAST即可。FIRST是取第一行，LAST则是取最后一行。
例如：
7、MAX([distinct|all] col) 取同组序列数据集中最大值。
例如：
SELECT col, MAX(value) \"Max Value\MIN(value) \"Min Value\" FROM tmp1 GROUP BY col
8、MIN([distinct|all] col) 取同组序列数据集中最小值。形式参数均与上同，不详述
[三思笔记]-Oracle函数介绍--著名及非著名函数 http://www.5ienet.com/ QQ：54545君三思
例如：
SELECT col, MAX(value) \"Max Value\MIN(value) \"Min Value\" FROM tmp1 GROUP BY col
9、SUM([distinct|all] col) 取指定列所在分组序列的值的和并返回。
例如：SELECT col, SUM(value) \"Sum Value\" FROM tmp1 GROUP BY col
10、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://www.5ienet.com/ QQ：54545君三思
