Oracle的分析函数
程序员文章站
2022-06-20 12:48:04
Oracle的分析函数row_number(),rank(),dense_rank()的用法与区别 比如查询工资排名第7的员工信息,可以用分析函数来做。 但他们的用法还是有区别的,这3个函数的唯一区别在于当碰到相同数据时的排名策略。 ①ROW_NUMBER: Row_number函数返回一个唯一的值 ......
Oracle的分析函数row_number(),rank(),dense_rank()的用法与区别
比如查询工资排名第7的员工信息,可以用分析函数来做。
--查询工资排名第7的员工信息
select *
from (select emp.*, rank() over(order by sal desc) rank from emp)
where rank = 7;
select *
from (select emp.*, dense_rank() over(order by sal desc) dr from emp)
where dr = 7;
select *
from (select emp.*, row_number() over(order by sal desc) rn from emp)
where rn = 7;
但他们的用法还是有区别的,这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK:
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
依次贴图说明:
从图中可以看出,rownum遇到的数据无论是否相同,都按照顺序排序;dense_rank当遇到相同数据时,相同数据的排名是相同的,但会空出相应的排名;rank遇到相同的数据时,相同的数据排名相同,且不会空出相应的排名。
over()函数不能单独使用,必须和分析函数或者聚合函数一起使用
例如员工表中分部门进行排序
select ename, deptno, dense_rank() over(partition by deptno order by sal) dr from emp;
over函数和sum函数一起使用可以实现连续求和
select ename, deptno, sal, sum(sal) over(partition by deptno order by ename) part_sum from emp;
over()函数是一个窗口说明函数,用来说明前面所接函数的限制条件,如果over函数中无条件,则求的是所有员工的工资总和
select ename, deptno, sal, sum(sal) over() from emp;