row_number() over(partition by ... order by ...)

rank() over(partition by ... order by ...)

dense_rank() over(partition by ... order by ...)

/*--建表和插入测试数据

create table test (A1 number, A2 number );

insert into test(a1,a2) values(1, 3);

insert into test(a1,a2) values(2, 4);

insert into test(a1,a2) values(3, 2);

insert into test(a1,a2) values(3, 5);

insert into test(a1,a2) values(4, 2);

select a1,a2,

        rank () over (order by a1) rank,

        dense_rank () over (order by a1) dense_rank,

        row_number () over (order by a1) row_number

from test;

 */

         A1         A2       RANK DENSE_RANK ROW_NUMBER

---------- ---------- ---------- ---------- ----------

         1          3          1          1          1

         2          4          2          2          2

         3          2          3          3          3

         3          5          3          3          4

         4          2          5          4          5

rank()是跳跃排序，有两个第3名时接下来就是第四名

dense_rank在有出现相同的rank后，rank的值是紧接上一次的rank值。

top N 的实现应该采用row_number() over 而不是rank () over，

/*--有两列，按第一列求第二列的累计和,第一列不要求唯一

create table TEST

(

  A1 NUMBER,

  A2 NUMBER

);

/

insert into TEST (A1, A2)

values (3, 3);

insert into TEST (A1, A2)

values (1, 3);

insert into TEST (A1, A2)

values (2, 4);

insert into TEST (A1, A2)

values (3, 2);

insert into TEST (A1, A2)

values (4, 5);

insert into TEST (A1, A2)

values (5, 2);

insert into TEST (A1, A2)

values (3, 1);

commit;

select a1, a2, sum(a2) over(order by a1 rows unbounded preceding) from test; 或者

select a1, a2, sum(a2) over(order by t) from (select a1, a2 , row_number() over(order by (a1)) t from test);

*/

因为row_number 是给记录加一个序号标记，而rank 是相当于进行排序后的排名，也就是如果排序列是相同的，

那么他们的返回值也是相同的，而row_number则不可能返回相同值。

count() over(partition by ... order by ...)

max() over(partition by ... order by ...)

min() over(partition by ... order by ...)

sum() over(partition by ... order by ...)

avg() over(partition by ... order by ...)

first_value() over(partition by ... order by ...)

last_value() over(partition by ... order by ...)

lag() over(partition by ... order by ...)

lead() over(partition by ... order by ...)

 

--查找每个部门工资最高前三名员工信息

/*select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp)

where a<=3 order by deptno asc,sal desc ;*/

--求每个部门的平均工资以及每个人与所在部门的工资差额

/*select deptno,ename,sal ,

     round(avg(sal) over(partition by deptno)) as dept_avg_sal,

     round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff

from emp;*/