Oracle分析函数-OVER()排序
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
li2010-09-010101011
wang2010-09-010102011
dd2011-09-010201021
fff2012-09-010301031
分析函数可以简化查询逻辑，省掉⼀部分代码。
注：时间上会稍微长⼀些，在数据库速度容许情况下，适合使⽤分析函数。
下⾯具体说⼀下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函数返回⼀个唯⼀的值，除⾮遇到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间空出排名。是跳跃排序，有两个第⼆名时接下来就是第四名；rank函数的用法
--dense_rank()Dense_rank函数返回⼀个唯⼀的值，除⾮当碰到相同数据时，此时所有相同数据的排名都是⼀样的。是连续排序，有两个第⼆名时仍然跟着第三名
假如客户就只需要指定数⽬的记录，如取最⼤⼀个值，那么采⽤row_number是最简单的，但有漏掉的记录的危险。
假如客户需要所有达到排名⽔平的记录，那么采⽤rank或dense_rank是不错的选择。⾄于选择哪⼀种则看客户的需要，选择dense_rank或得到最⼤的记录
注：排序时，oracle会将空值列为最⼤，⽤NULLS LAST/FIRST告诉Oracle让空值排名最后后第⼀
注意：分析函数允许你对⼀个数据集进排序和筛选，这是SQL从来不能实现的.除了最后的Order by⼦句之外，分析函数是在查询中执⾏的最后的操作集，这样的话，就不能直接在谓词中使⽤分析函数，即不能在上⾯使⽤where或having⼦句
另外：分析函数不能⽤在exist中。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。