Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数group的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
例如需要查询员工信息中每个部门最早入职员工的个人信息
字段有:用户名name,日期start_date,工号emp_id,部门dept_id
1)聚合函数:select e.* from emp e, (select max(start_date)max_ start_date, dept_id from emp group by dept_id) emax where e.start_date= emax.max_start_date and e.dept_id = emax.dept_id
2)分析函数:select * from (select e.*,dense_rank()over(partition by code,name order by date) cnt from emp e) where cnt = 1
name |
start_date |
emp_id |
dept_id |
cnt |
li |
2010-09-01 |
0101 |
01 |
1 |
wang |
2010-09-01 |
0102 |
01 |
1 |
dd |
2011-09-01 |
0201 |
02 |
1 |
fff |
2012-09-01 |
0301 |
03 |
1 |
分析函数可以简化查询逻辑,省掉一部分代码。
注:时间上会稍微长一些,在数据库速度容许情况下,适合使用分析函数。
下面具体说一下Over函数。
①Over函数指明在那些字段上做分析,其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。
例如row_number()over(partition by code,name order by date) cnt
就是将表中相同code,name的数据进行分组,并排序,根据每一组中日期进行序号标记
②Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如Rank,Dense_rank等。
排序函数row_number()、rank()和dense_rank()的区别是:
--row_number()函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增;
--rank()Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。是跳跃排序,有两个第二名时接下来就是第四名;
--dense_rank()Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。是连续排序,有两个第二名时仍然跟着第三名
假如客户就只需要指定数目的记录,如取最大一个值,那么采用row_number是最简单的,但有漏掉的记录的危险。
假如客户需要所有达到排名水平的记录,那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要,选择dense_rank或得到最大的记录
注:排序时,oracle会将空值列为最大,用NULLS LAST/FIRST告诉Oracle让空值排名最后后第一
注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的.除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!
另外:分析函数不能用在exist中。
相关推荐
oracle分析函数-计算移动平均、累计、分布
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
oracle分析函数,oracle分析函数over_及开窗函数用法。
GPFS-over-Oracle-RAC-Intro完整版资料.ppt
c3p0-oracle-thin-extras-0.9.2.1
主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () ...
Oracle自定义聚合函数,分析函数.
oracle函数大全-数字处理函数,包括数字、字符串处理函数
Oracle内置SQL函数-分类整理大全 ---------------------------------------------------- From: lihaichen http://blog.oracle.com.cn/?uid/19500 Oracle内置SQL函数-分类整理大全 1 F.1字符函数——返回字符值 ...
关于ORACLE分析函数的教程,教程描述描述清晰
Oracle函数大全-详细介绍,包含分析函数,单值函数,聚合函数等多种函数。
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数
oracle的分析函数over 及开窗函数
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....
ORACLE分析函数.pdf
Oracle分析函数.doc
oracle资料大全--函数,编程大全oracle资料大全--函数,编程大全oracle资料大全--函数,编程大全oracle资料大全--函数,编程大全