Oracle分析函数-OVER()排序

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中。