SQL SERVER排名函數RANK，DENSE_RANK，NTILE，ROW_NUMBER

前言

本文意于用執行個體資料幫助了解SQL SERVER排名函數RANK，DENSE_RANK，NTILE，ROW_NUMBER。

準備工作

建立測試表：

create table test(
id int identity(1,1) primary key,
testid int,
name varchar(100)
)
           

插入測試資料：

insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 2,'LeeWhoee University'
insert into test(testid,name)select 3,'排名函數'
insert into test(testid,name)select 4,'排名函數'
insert into test(testid,name)select 4,'排名函數'
           

用一個SQL語句來檢視各個函數的使用：

select id,testid,
ROW_NUMBER() over( order by testid) as rownum,
RANK() over(order by testid) as ranknum,
DENSE_RANK() over(order by testid) as denseranknum,
Ntile(4) over ( order by testid) as ntilenum
from test
order by testid
           

下面是運作結果：

id    testid    rownum    ranknum    denseranknum    ntilenum

1    1            1                 1                   1                             1

2    1            2                 1                   1                             1

3    1            3                 1                   1                             2

4    2            4                 4                   2                             2

5    3            5                 5                   3                             3

6    4            6                 6                   4                             3

7    4            7                 6                   4                             4

ROW_NUMBER() over( order by testid) 

按testid升序排列為每一個testid生成與之對應的一個序列數字，這些數字是從1開始由小到大的不間斷數字。每個序列數字是唯一的。

RANK() over(order by testid) 

按testid升序排列為每一個testid生成與之對應的一個排名數字，這些數字是從1開始由小到大排序（可能間斷）。相同的testid生成的排名數字也相同，但是下一排名數字不是由之前的排名數字加1計算出的，而是排名總數即行數。

DENSE_RANK() over(order by testid) 

按testid升序排列為每一個testid生成與之對應的一個排名數字，這些數字是從1開始由小到大排序的不間斷數字（可能重複）。相同的testid生成的排名數字也相同，但是下一排名數字是由之前的排名數字加1計算出，而不是排名總數或行數。

Ntile(4) over ( order by testid) 

按testid升序排列并将所有testid平均分成4組（最後一組testid總數可能少于其它組），然後為每一個testid生成與之對應的一個所屬組編号。組編号是從1開始由小到大的不間斷數字。

partition by

下面看一個帶partition by的SQL語句來檢視各個函數的使用：

select id,testid,name,
ROW_NUMBER() over(partition by name order by testid) as rownum,
RANK() over(partition by name order by testid) as ranknum,
DENSE_RANK() over(partition by name order by testid) as denseranknum,
Ntile(2) over (partition by name order by testid) as ntilenum
from test
order by name
           

運作結果：

id    testid    name                         rownum    ranknum    denseranknum    ntilenum

1    1         LeeWhoee University    1                 1                   1                             1

2    1         LeeWhoee University    2                 1                   1                             1

3    1         LeeWhoee University    3                 1                   1                             2

4    2         LeeWhoee University    4                 4                   2                             2

5    3            排名函數                     1                 1                   1                             1

6    4            排名函數                     2                 2                   2                             1

7    4            排名函數                     3                 2                   2                             2

ROW_NUMBER() over(partition by name order by testid) 

按name先進行資料分區，然後對每一區進行正常的ROW_NUMBER()計算。

RANK() over(partition by name order by testid) 

按name先進行資料分區，然後對每一區進行正常的RANK()計算。

DENSE_RANK() over(partition by name order by testid) 

按name先進行資料分區，然後對每一區進行正常的DENSE_RANK()計算。

Ntile(2) over (partition by name order by testid) 

按name先進行資料分區，然後對每一區進行正常的Ntile()計算。

為了便于區分資料，在這裡進行了NTILE(2)而不是NTILE(4)。