Oracle的分析函数之rank()、dense_rank()和row_number 的使用

首先说明一下，以下信息是我在网上查阅资料时候看到的，拿到这来和大家分享一下。

下面的几个例子比较易懂，一起学习吧！

 

 

　1.row_number() 的使用
　　原表信息：
　　
　　SQL> break on deptno skip 1　-- 为效果更明显，把不同部门的数据隔段显示。
　　SQL> select deptno,ename,sal
　　2　from emp
　　3　order by deptno,sal desc;
　　
　　DEPTNO ENAME　　　　　　 SAL
　　---------- ---------- ----------
　　　　　　10 KING　　　　　　 5000
　　　　　　　 CLARK　　　　　　2450
　　　　　　　 MILLER　　　　　 1300
　　
　　　　　　20 SCOTT　　　　　　3000
　　　　　　　 FORD　　　　　　 3000
　　　　　　　 JONES　　　　　　2975
　　　　　　　 ADAMS　　　　　　1100
　　　　　　　 SMITH　　　　　　 800
　　
　　　　　　30 BLAKE　　　　　　2850
　　　　　　　 ALLEN　　　　　　1600
　　　　　　　 TURNER　　　　　 1500
　　　　　　　 WARD　　　　　　 1250
　　　　　　　 MARTIN　　　　　 1250
　　　　　　　 JAMES　　　　　　 950
　　
　　
　　已选择14行。
　　
　　
　　使用row_number()查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个。
　　
　　SQL> select * from (
　　2　select deptno,row_number() over(partition by deptno order by sal desc) rw,ename,sal
　　3　from emp
　　4　)
　　5　where rw<=3;
　　　　DEPTNO　　　　 RW ENAME　　　　　　 SAL
　　---------- ---------- ---------- ----------
　　　　　　10　　　　　1 KING　　　　　　 5000
　　　　　　　　　　　　2 CLARK　　　　　　2450
　　　　　　　　　　　　3 MILLER　　　　　 1300
　　
　　　　　　20　　　　　1 SCOTT　　　　　　3000
　　　　　　　　　　　　2 FORD　　　　　　 3000
　　　　　　　　　　　　3 JONES　　　　　　2975
　　
　　　　　　30　　　　　1 BLAKE　　　　　　2850
　　　　　　　　　　　　2 ALLEN　　　　　　1600
　　　　　　　　　　　　3 TURNER　　　　　 1500
　　
　　
　　已选择9行。
　　
　　体会：row_number() 返回的主要是“行”的信息，并没有按照sal排名，如
　　20　　　　　1 SCOTT　　　　　　3000
　　2 FORD　　　　　　 3000
　　scott与ford薪水一样多，ford前面的2仅仅是行数、记录条数的感念。

2.rank()、dense_rank() 的使用
　　
　　原表信息：
　　
　　SQL> break on deptno skip 1　-- 为效果更明显，把不同部门的数据隔段显示。
　　SQL> select deptno,ename,sal
　　2　from emp
　　3　order by deptno,sal desc;
　　
　　　　DEPTNO ENAME　　　　　　 SAL
　　---------- ---------- ----------
　　　　　　10 KING　　　　　　 5000
　　　　　　　 CLARK　　　　　　2450
　　　　　　　 MILLER　　　　　 1300
　　
　　　　　　20 SCOTT　　　　　　3000
　　　　　　　 FORD　　　　　　 3000
　　　　　　　 JONES　　　　　　2975
　　　　　　　 ADAMS　　　　　　1100
　　　　　　　 SMITH　　　　　　 800
　　
　　　　　　30 BLAKE　　　　　　2850
　　　　　　　 ALLEN　　　　　　1600
　　　　　　　 TURNER　　　　　 1500
　　　　　　　 WARD　　　　　　 1250
　　　　　　　 MARTIN　　　　　 1250
　　　　　　　 JAMES　　　　　　 950
　　
　　已选择14行。
　　
　　使用rank()查出各部门薪水前三名的员工姓名、薪水。
　　SQL> select * from (
　　2　select deptno,rank() over(partition by deptno order by sal desc) rk,ename,sal
　　3　from emp
　　4　)
　　5　where rk<=3
　　6　/
　　　　DEPTNO　　　　 RK ENAME　　　　　　 SAL
　　---------- ---------- ---------- ----------
　　　　　　10　　　　　1 KING　　　　　　 5000
　　　　　　　　　　　　2 CLARK　　　　　　2450
　　　　　　　　　　　　3 MILLER　　　　　 1300
　　
　　　　　　20　　　　　1 SCOTT　　　　　　3000
　　　　　　　　　　　　1 FORD　　　　　　 3000
　　　　　　　　　　　　3 JONES　　　　　　2975
　　
　　　　　　30　　　　　1 BLAKE　　　　　　2850
　　　　　　　　　　　　2 ALLEN　　　　　　1600
　　　　　　　　　　　　3 TURNER　　　　　 1500
　　
　　已选择9行。
　　
　　使用dense_rank()查出各部门薪水前三名的员工姓名、薪水。
　　SQL> select * from (
　　2　select deptno,dense_rank() over(partition by deptno order by sal desc) drk,ename,sal
　　3　from emp
　　4　)
　　5　where drk<=3
　　6　/
　　　　DEPTNO　　　　DRK ENAME　　　　　　 SAL
　　---------- ---------- ---------- ----------
　　　　　　10　　　　　1 KING　　　　　　 5000
　　　　　　　　　　　　2 CLARK　　　　　　2450
　　　　　　　　　　　　3 MILLER　　　　　 1300
　　
　　　　　　20　　　　　1 SCOTT　　　　　　3000
　　　　　　　　　　　　1 FORD　　　　　　 3000
　　　　　　　　　　　　2 JONES　　　　　　2975
　　　　　　　　　　　　3 ADAMS　　　　　　1100
　　
　　　　　　30　　　　　1 BLAKE　　　　　　2850
　　　　　　　　　　　　2 ALLEN　　　　　　1600
　　　　　　　　　　　　3 TURNER　　　　　 1500
　　
　　
　　已选择10行。
 row_number() ＋1递增返回行号
　　dense_rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级数量等级的排名+1
　　rank() 同数量等级的数据返回相同名次，下级数量等级的排名为上级所有数据总数+1

开窗函数的的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）