oracle函数介绍(6) 著名函数之分析函数

zhouqg

贡献于2012-11-03

字数:2403 关键词: Oracle 数据库服务器

 ORACLE函数介绍 第六篇 著名函数之分析函数 2007.8.28 1、 AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。 例如: --聚合函数 SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col; --分析函数 SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col; 2、 SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause ) 例如: --聚合函数 SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col; --分析函数 SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col; 3、 COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查询分组序列中各组行数。 例如: --分组查询col的数量 SELECT col,count(0) over(partition by col order by col) ct FROM tmp1; 4、 FIRST() 从DENSE_RANK返回的集合中取出排在第一的行。 例如: --聚合函数 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 [三思笔记]-Oracle函数介绍--著名及非著名函数 http://www.5ienet.com/ QQ:5454589君三思 GROUP BY col; --分析函数 SELECT col, MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col), MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col) FROM tmp1 ORDER BY col; 可以看到二者结果基本相似,但是ex1的结果是group by后的列,而ex2则是每一行都有返回。 5、 LAST()与上同,不详述。 例如:见上例。 6、 FIRST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的第一条记录 例如: insert into tmp1 values ('test6','287'); SELECT col, FIRST_VALUE(value) over(partition by col order by value) "First", LAST_VALUE(value) over(partition by col order by value) "Last" FROM tmp1; 7、 LAST_VALUE (col) OVER ( analytic_clause ) 返回over()条件查询出的最后一条记录 例如:见上例。 8、 LAG(col[,n][,n]) over([partition_clause] order_by_clause) lag是一个相当有意思的函数,其功能是返回指定列col前n1行的值(如果前n1行已经超出比照范围,则返回n2,如不指定n2则默认返回null),如不指定n1,其默认值为1。 例如: SELECT col, value, LAG(value) over(order by value) "Lag", LEAD(value) over(order by value) "Lead" FROM tmp1; 9、 LEAD(col[,n][,n]) over([partition_clause] order_by_clause) 与上函数正好相反,本函数返回指定列col后n1行的值。 例如:见上例 10、 MAX (col) OVER (analytic_clause) 获取分组序列中的最大值。 例如: --聚合函数 SELECT col, Max(value) "Max", Min(value) "Min" FROM tmp1 [三思笔记]-Oracle函数介绍--著名及非著名函数 http://www.5ienet.com/ QQ:5454589君三思 GROUP BY col; --分析函数 SELECT col, value, Max(value) over(partition by col order by value) "Max", Min(value) over(partition by col order by value) "Min" FROM tmp1; 11、 MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。 例如:见上例。 12、 RANK() OVER([partition_clause] order_by_clause) 关于RANK和DENSE_RANK前面聚合函数处介绍过了,这里不废话不,大概直接看示例吧。 例如: insert into tmp1 values ('test2',120); SELECT col, value, RANK() OVER(order by value) "RANK", DENSE_RANK() OVER(order by value) "DENSE_RANK", ROW_NUMBER() OVER(order by value) "ROW_NUMBER" FROM tmp1; 13、 DENSE_RANK () OVER([partition_clause] order_by_clause) 例如:见上例。 14、 ROW_NUMBER () OVER([partition_clause] order_by_clause) 这个函数需要多说两句,通过上述的对比相信大家应该已经能够看出些端倪。前面讲过,dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值依旧递增,rank则是重复值所在行的序列值相同,但其后的序列值从+重复行数开始递增,而row_number则不管是否有重复行,(分组内)序列值始终递增 例如:见上例。 [三思笔记]-Oracle函数介绍--著名及非著名函数 http://www.5ienet.com/ QQ:5454589君三思

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档