Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法(收藏)2007-03-09 11:27Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

最新推荐文章于 2024-05-10 06:56:34 发布

lmhine

最新推荐文章于 2024-05-10 06:56:34 发布

阅读量1.1k

点赞数

使用Oracle的分析函数ROW_NUMBER、DENSE_RANK、RANK

2008-03-19 09:59

这三个分析函数都可以在各个分组内从1开始排序。 ROW_NUMBER()是没有重复值的，可以利用它实现分页显示。 DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名。 RANK()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。

SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp ) WHERE Top3 <= 3 /

DEPTNO ENAME SAL TOP3 ---------- ---------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 2 20 JONES 2975 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 9 rows selected.

SELECT * FROM ( SELECT deptno, ename, sal, DENSE_RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC ) TopN FROM emp ) WHERE TopN <= 3 ORDER BY deptno, sal DESC / DEPTNO ENAME SAL TOPN ---------- ---------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 <--- ! 20 FORD 3000 1 <--- ! 20 JONES 2975 2 20 ADAMS 1100 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 10 rows selected.

SELECT * FROM ( SELECT deptno, ename, sal, RANK() OVER ( PARTITION BY deptno ORDER BY sal DESC ) TopN FROM emp ) WHERE TopN <= 3 ORDER BY deptno, sal DESC / DEPTNO ENAME SAL TOPN ---------- ---------- ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 <--- ! 20 FORD 3000 1 <--- ! 20 JONES 2975 3 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 9 rows selected

Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法(收藏)

2007-03-09 11:27

Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

RANK() 类似，不过RANK 排序的时候跟派名次一样，可以并列2个第一名之后是第3名

LAG 表示分组排序后，组内后面一条记录减前面一条记录的差，第一条可返回 NULL

BTW: EXPERT ONE ON ONE 上讲的最详细,还有很多相关特性，文档看起来比较费劲

row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序） rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 lag（arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。

SQL> set pagesize 100; SQL> select rownum from emp;

ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14

已选择14行。

已用时间: 00: 00: 00.10 SQL> select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;

DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------------- 10 1 2 3

20 1 2 3 4 5

30 1 2 3 4 5 6

已选择14行。

已用时间: 00: 00: 00.41 SQL> select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;

DEPTNO RANK()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------- 10 1 2 3

20 1 2 3 4 4

30 1 2 2 4 5 6

已选择14行。

已用时间: 00: 00: 00.21 SQL> select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;

DEPTNO DENSE_RANK()OVER(PARTITIONBYDEPTNOORDERBYSAL) ---------- --------------------------------------------- 10 1 2 3

20 1 2 3 4 4

30 1 2 2 3 4 5

已选择14行。

已用时间: 00: 00: 00.20 SQL> select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;

DEPTNO ENAME SAL LAG(ENAME, ---------- ---------- ---------- ---------- 10 CLARK 2450 KING 5000 CLARK MILLER 1300 KING

20 ADAMS 1100 FORD 3000 ADAMS JONES 2975 FORD SCOTT 3000 JONES SMITH 800 SCOTT

30 ALLEN 1600 BLAKE 2850 ALLEN JAMES 950 BLAKE MARTIN 1250 JAMES TURNER 1500 MARTIN WARD 1250 TURNER

已选择14行。

已用时间: 00: 00: 00.31 SQL> select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p order by deptno;

DEPTNO ENAME SAL LAG(ENAME, ---------- ---------- ---------- ---------- 10 CLARK 2450 example KING 5000 example MILLER 1300 CLARK

20 ADAMS 1100 example FORD 3000 example JONES 2975 ADAMS SCOTT 3000 FORD SMITH 800 JONES

30 ALLEN 1600 example BLAKE 2850 example JAMES 950 ALLEN MARTIN 1250 BLAKE TURNER 1500 JAMES WARD 1250 MARTIN

已选择14行。