分析函数——排序排列（rank、dense_rank、row_number）

Oracle从8i开始就提供了3个分析函数：rank，dense_rank，row_number


(1)Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，
   同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
   
(2)Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。


(3)Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。


例:
create table s_score
( s_id number(6)
 ,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);

select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score;

   S_ID  SCORE       RANK DENSE_RANK ROW_NUMBER
------- ------ ---------- ---------- ----------
      3  99.00          1          1          1
      1  98.00          2          2          2
      4  98.00          2          2          3
      5  98.00          2          2          4
      6  80.00          5          3          5
      2  66.50          6          4          6
        
排名/排序的时候，有时候，我们会想到利用伪列rownum，利用rownum确实可以解决某些场景下的问题(但是相对也比较复杂)，而且有些
场景下的问题却很难解决。

例:取成绩前三名，并且前三名含有并列的情况    
通过上面例子，我们可以直观的看到，结果应该有5条记录。
select
    s_id 
   ,score
   ,dense_rank
from (
select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score
) t
where dense_rank <= 3;

   S_ID  SCORE DENSE_RANK
------- ------ ----------
      3  99.00          1
      1  98.00          2
      5  98.00          2
      4  98.00          2
      6  80.00          3
      
如果只是简单的想到去用rownum <= 3 得到的结果显然不可能是正确的。

组内的排名或者排序是经常遇到的一种场景。
例如，取每个销售部门内，销售业绩最好的前三名。
      取每个班级内成绩排名信息等等..
      
取每个班级内每门课成绩排名第一的同学信息     
drop table S_SCORE;
create table S_SCORE
(
  S_ID  NUMBER(6),
  CLASS_ID VARCHAR2(2),
  COURSE VARCHAR2(20),
  SCORE NUMBER(5,2)
);

INSERT INTO S_SCORE VALUES(1001,'A','MATH','67');
INSERT INTO S_SCORE VALUES(1004,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1002,'A','MATH','99');
INSERT INTO S_SCORE VALUES(1003,'A','MATH','55');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','70');
INSERT INTO S_SCORE VALUES(1001,'A','ORACLE','97');
INSERT INTO S_SCORE VALUES(1004,'B','ORACLE','48');
INSERT INTO S_SCORE VALUES(1002,'A','ORACLE','79');
INSERT INTO S_SCORE VALUES(1003,'A','ORACLE','65');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','82');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','78');

select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
;

   S_ID CLASS_ID COURSE                 SCORE        DRK
------- -------- -------------------- ------- ----------
   1002 A        MATH                   99.00          1
   1001 A        MATH                   67.00          2
   1003 A        MATH                   55.00          3
   1001 A        ORACLE                 97.00          1
   1002 A        ORACLE                 79.00          2
   1003 A        ORACLE                 65.00          3
   1004 B        MATH                   88.00          1
   1001 B        MATH                   88.00          1
   1001 B        MATH                   70.00          2
   1001 B        ORACLE                 82.00          1
   1001 B        ORACLE                 78.00          2
   1004 B        ORACLE                 48.00          3
   
select
   s_id
  ,class_id
  ,course
  ,score
from (
select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
) t
where drk = 1
;


   S_ID CLASS_ID COURSE                 SCORE
------- -------- -------------------- -------
   1002 A        MATH                   99.00
   1001 A        ORACLE                 97.00
   1004 B        MATH                   88.00
   1001 B        MATH                   88.00
   1001 B        ORACLE                 82.00   
   
rank()和dense_rank()用法相似，这里就不在举例说明了。可以将上面的例子中dense_rank()替换成rank()实现。


接下来,看一个使用row_number()的场景
例:查看每个部门最近一笔销售记录
select * from criss_sales order by dept_id,sale_date desc;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D01     2014/4/30   G03                800
D01     2014/4/8    G01                200
D01     2014/3/4    G00                700
D02     2014/5/2    G03                900
D02     2014/4/27   G01                300
D02     2014/4/8    G02                100
D02     2014/3/6    G00                500

即,我们希望得到
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900
这两条记录

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc)
from criss_sales;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT ROW_NUMBER()OVER(PARTITIONBYDE
------- ----------- ---------- ----------- ------------------------------
D01     2014/5/4    G02                 80                              1
D01     2014/4/30   G03                800                              2
D01     2014/4/8    G01                200                              3
D01     2014/3/4    G00                700                              4
D02     2014/5/2    G03                900                              1
D02     2014/4/27   G01                300                              2
D02     2014/4/8    G02                100                              3
D02     2014/3/6    G00                500                              4

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
from (
select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc) rn
from criss_sales
) t
where rn = 1
;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28929558/viewspace-1180283/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28929558/viewspace-1180283/