【aierong 】Sql 2005 Transact-SQL 新兵器學習總結之-排名函式

iDotNetSpace發表於2008-08-19
AISQL函式

Transact-SQL提供了4個排名函式: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()


下面是對這4個函式的解釋：
RANK()
返回結果集的分割槽內每行的排名。行的排名是相關行之前的排名數加一。
如果兩個或多個行與一個排名關聯，則每個關聯行將得到相同的排名。
例如，如果兩位銷售員具有相同的SalesYTD值，則他們將並列第一。由於已有兩行排名在前，所以具有下一個最大SalesYTD的銷售人員將排名第三。
因此，RANK 函式並不總返回連續整數。


DENSE_RANK()
返回結果集分割槽中行的排名，在排名中沒有任何間斷。行的排名等於所討論行之前的所有排名數加一。
如果有兩個或多個行受同一個分割槽中排名的約束，則每個約束行將接收相同的排名。
例如，如果兩位頂尖銷售員具有相同的 SalesYTD 值，則他們將並列第一。接下來 SalesYTD 最高的銷售人員排名第二。該排名等於該行之前的所有行數加一。
因此，DENSE_RANK 函式返回的數字沒有間斷，並且始終具有連續的排名。


ROW_NUMBER()
回結果集分割槽內行的序列號，每個分割槽的第一行從 1 開始。
ORDER BY 子句可確定在特定分割槽中為行分配唯一 ROW_NUMBER 的順序。


NTILE()
將有序分割槽中的行分發到指定數目的組中。各個組有編號，編號從一開始。對於每一個行，NTILE 將返回此行所屬的組的編號。
如果分割槽的行數不能被 integer_expression 整除，則將導致一個成員有兩種大小不同的組。按照 OVER 子句指定的順序，較大的組排在較小的組前面。
例如，如果總行數是 53，組數是 5，則前三個組每組包含 11 行，其餘兩個組每組包含 10 行。
另一方面，如果總行數可被組數整除，則行數將在組之間平均分佈。
例如，如果總行數為 50，有五個組，則每組將包含 10 行。


--演示例題，建一個table
create table rankorder(
orderid int,
qty int
)
go
--插入資料
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
--查詢出各類排名
SELECT orderid,qty,
  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
  RANK()       OVER(ORDER BY qty) AS [rank],
  DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
  NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty

--結果
--ROW_NUMBER()是按qty由小到大逐一排名，不併列，排名連續
--RANK()是按qty由小到大逐一排名，並列，排名不連續
--DENSE_RANK()是按qty由小到大逐一排名，並列，排名連續
--NTILE()是按qty由小到大分成3組逐一排名，並列，排名連續
orderid    qty    rownumber    rank    denserank    NTILE
30        10    1            1        1            1
10        10    2            1        1            1
80        10    3            1        1            1
40        10    4            1        1            1
30        15    5            5        2            2
30        20    6            6        3            2
22        20    7            6        3            2
21        20    8            6        3            2
10        30    9            9        4            3
30        30    10            9        4            3
40        40    11            11        5            3

sql 2005實現排名非常方便，但是用sql 2000實現排名就比較麻煩，下面是sql 2000的實現程式碼:


--RANK在sql 2000中的實現
select orderid,qty,
    (select count(1)+1 from rankorder where qty<r.qty) as [rank]
from rankorder r
ORDER BY qty
go
 
--ROW_NUMBER在sql 2000中的實現
--利用臨時表和IDENTITY（函式）
select identity(int,1,1) as [ROW_NUMBER],orderid,qty
into #tem             
from rankorder

select orderid,qty,[ROW_NUMBER]
from #tem

drop table #tem
go

--DENSE_RANK在sql 2000中的實現
select identity(int,1,1) as ids,  qty
into #t
from rankorder
group by qty
order by qty

select r.orderid,r.qty,t.ids as [DENSE_RANK]
from rankorder r join #t  t
on r.qty=t.qty

drop table #t
go


排名函式是與視窗函式OVER()配合一起使用的。
如果藉助OVER子句的引數PARTITION BY,就可以將結果集分為多個分割槽。排名函式將在每個分割槽內進行排名.


--例題
SELECT orderid,qty,
DENSE_RANK() OVER(ORDER BY qty) AS a ,
DENSE_RANK() OVER(PARTITION BY orderid ORDER BY qty) AS b     
FROM rankorder
ORDER BY qty

--說明：
--a列是在全部記錄上進行的排名
--b列是把orderid中的記錄分成了10,21,22,30,40,80這6個區，再在每個區上進行的排名。
orderid    qty    a    b
10        10    1    1
30        10    1    1
40        10    1    1
80        10    1    1
30        15    2    2
30        20    3    3
21        20    3    1
22        20    3    1
10        30    4    2
30        30    4    4
40        40    5    2


我們看到排名函式可以很簡便的得到各種型別的排名
以下是我對4個排名函式的類比表格：
                               排名連續性   排名並列性
RANK()                 不一定連續    有並列
DENSE_RANK()    連續               有並列
ROW_NUMBER()   連續             無並列
NTILE()                  連續               有並列

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-429982/，如需轉載，請註明出處，否則將追究法律責任。

相關文章