SQL SERVER次序函數RANK，DENSE_RANK，NTILE，ROW_NUMBER

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 ntilenumfrom testorder 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 ntilenumfrom testorder 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)。