ROW_NUMBER、RANK、DENSE_RANK的用法 (加补充)

 

ROW_NUMBER、 RANK、 DENSE_RANK的用法

 

  (爱新觉罗 .毓华   2007-12-16  广东深圳 )
  SQL Server 2005 引入几个新的排序 (排名 )函数 ,如 ROW_NUMBER、 RANK、 DENSE_RANK等。 这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。  
  --------------------------------------------------------------------------
  ROW_NUMBER()

  说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
  语法： ROW_NUMBER () OVER ( [  <partition_by_clause> ]  <order_by_clause> ) 。

  备注： ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

  参数：

<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。  
  <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
 

  返回类型： bigint 。
 

简单实例：

CREATE TABLE testdd

    ( deptno                         NUMBER (*, 0 ),

    ename                          VARCHAR2 ( 4000 ),

    sal                            NUMBER ,

    team                           VARCHAR2 ( 4000 ))

/

 

1．以 ” deptno , team ” 分区（组），按照 sal 排序

select deptno , team , row_number () over ( partition by deptno , team order by sal desc ) rn , ename , sal

from testdd

 

DEPTNO	TEAM	RN	ENAME	SAL
1	101	1	Lim	4000
1	101	2	Lee	3000
1	101	3	Qin	2500
1	102	1	Xu	50000
1	102	2	Wang	3100
1	102	3	Zhu	2400
2	102	1	Wong1	2200
2	102	2	Wong2	2000
2	103	1	Ding	3500
2	103	2	Ding1	2500
2	103	3	Ding2	2200
2	103	4	Ding3	2000

 

 

 

 

2．整个 table按照 sal 排序

select deptno , team , row_number () over ( order by sal desc ) rn , ename , sal

from testdd

 

DEPTNO	TEAM	RN	ENAME	SAL
1	102	1	Xu	50000
1	101	2	Lim	4000
2	103	3	Ding	3500
1	102	4	Wang	3100
1	101	5	Lee	3000
1	101	6	Qin	2500
2	103	7	Ding1	2500
1	102	8	Zhu	2400
2	102	9	Wong1	2200
2	103	10	Ding2	2200
2	103	11	Ding3	2000
2	102	12	Wong2	2000

 

 

3．以 ” deptno ” 分区（组），按照 sal 排序

select deptno , team , row_number () over ( partition by deptno order by sal desc ) rn , ename , sal

from testdd

 

DEPTNO	TEAM	RN	ENAME	SAL
1	102	1	Xu	50000
1	101	2	Lim	4000
1	102	3	Wang	3100
1	101	4	Lee	3000
1	101	5	Qin	2500
1	102	6	Zhu	2400
2	103	1	Ding	3500
2	103	2	Ding1	2500
2	102	3	Wong1	2200
2	103	4	Ding2	2200
2	102	5	Wong2	2000
2	103	6	Ding3	2000

 

4．使用实例： (去掉重复记录 )

 

update INQUIRE_ALL_DETAIL_CURRENT

set email_type = 'P'

--delete from INQUIRE_ALL_DETAIL_CURRENT

--delete from inquire_all_email_current

--delete from INQUIRE_ALL_EMAIL_ADDRESS_cur

  where email_id in

(

select distinct ( email_id )

FROM ( SELECT INQUIRE_ALL_DETAIL_CURRENT .*, ROW_NUMBER () OVER ( PARTITION BY INQUIRY_ID , supplier_id , product_id , email_type ORDER BY EMAIL_ID DESC ) RN ,

      COUNT ( 1 ) OVER ( PARTITION BY INQUIRY_ID , supplier_id , product_id , email_type ) COUNTS

  FROM INQUIRE_ALL_DETAIL_CURRENT ) A

  WHERE COUNTS = 2 and rn = 1

  )

 

5．利用 CTE来过滤 ROW_NUMBER()的用法 （选择出每个区的前三名）：

 

--CTE filter row_number

  with NumberedRows AS

  (

  select deptno , team , row_number () over ( partition by deptno order by sal desc ) rn , ename , sal

  from testdd

)

  select * from NumberedRows

  where rn between 1 and 3

DEPTNO	TEAM	RN	ENAME	SAL
1	101	1	Lim	50000
1	102	2	Xu	50000
1	102	3	Wang	3100
2	103	1	Ding	3500
2	103	2	Ding1	2500
2	102	3	Wong1	2200

 

  --------------------------------------------------------------
  RANK()

  说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
  语法： RANK () OVER ( [  < partition_by_clause > ]  < order_by_clause > )
  备注：如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
        例如，如果两位顶尖销售员具有同样的 SalesYTD 值，他们将并列第一。
        由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。
        因此， RANK 函数并不总返回连续整数。  
        用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
  参数： < partition_by_clause > ：将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。  
         < order_by_clause >：确定将 RANK 值应用于分区中的行时所基于的顺序。
  返回类型： bigint

 

‑简单例子：

1．按 deptno 分区 排名

SELECT  RANK () OVER ( PARTITION BY deptno order by sal desc ) as RANK , deptno , team , ename , sal

  FROM testdd 

 

1	1	101	Lim	50000
1	1	102	Xu	50000
3	1	102	Wang	3100
4	1	101	Lee	3000
5	1	101	Qin	2500
6	1	102	Zhu	2400
1	2	103	Ding	3500
2	2	103	Ding1	2500
3	2	102	Wong1	2200
3	2	103	Ding2	2200
5	2	102	Wong2	2000
5	2	103	Ding3	2000

 

2.按每个 depno 内的 team 分区排名

SELECT  RANK () OVER ( PARTITION BY deptno , team order by sal desc ) as RANK , deptno , team , ename , sal FROM testdd 

 

RANK	DEPTNO	TEAM	ENAME	SAL
1	1	101	Lim	50000
2	1	101	Lee	3000
3	1	101	Qin	2500
1	1	102	Xu	50000
2	1	102	Wang	3100
3	1	102	Zhu	2400
1	2	102	Wong1	2200
2	2	102	Wong2	2000
1	2	103	Ding	3500
2	2	103	Ding1	2500
3	2	103	Ding2	2200
4	2	103	Ding3	2000

  3.按每个 depno 内的平均分以及排名

    -- get average number

  with teamTotal AS

  (

  select deptno , team , sum ( sal ) as sumSal from testdd group by deptno , team

  )

 

  select deptno , team , sumSal ,

  rank () OVER ( PARTITION BY deptno ORDER BY sumSal , deptno DESC ) AS Rank ,

  AVG ( sumSal ) OVER ( PARTITION BY deptno ) AS Average

  from teamTotal

 

DEPTNO	TEAM	SUMSAL	RANK	AVERAGE
1	101	55500	1	55500
1	102	55500	1	55500
2	102	4200	1	7200
2	103	10200	2	7200

 

 

DENSE_RANK()
 
  说明：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一 。
  语法： DENSE_RANK () OVER ( [  < partition_by_clause > ]  < order_by_clause > )
  备注：如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
        例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。
        接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。
        因此， DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。  
        整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行 。
  参数： < partition_by_clause > ：将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。  
         < order_by_clause >：确定将 DENSE_RANK 值应用于分区中各行的顺序。
  返回类型： bigint

简单实例：

1．按 deptno 分区 排名

SELECT  dense_RANK () OVER ( PARTITION BY deptno order by sal desc ) as RANK , deptno , team , ename , sal FROM testdd 

 

RANK	DEPTNO	TEAM	ENAME	SAL
1	1	101	Lim	50000
1	1	102	Xu	50000
2	1	102	Wang	3100
3	1	101	Lee	3000
4	1	101	Qin	2500
5	1	102	Zhu	2400
1	2	103	Ding	3500
2	2	103	Ding1	2500
3	2	102	Wong1	2200
3	2	103	Ding2	2200
4	2	102	Wong2	2000
4	2	103	Ding3	2000