SQL Server-- Ranking functions

来源：互联网发布：移动硬盘加密软件编辑：程序博客网时间：2024/05/28 15:03

今天学习了sql server 2005新增的几个函数,分别是row_number(),rank,DENSE_RANK,ntile,下面以例子
分别简单讲解之

1.row_number
先来点数据,先建个表

SET NOCOUNT ONCREATE TABLE Person(FirstName VARCHAR(10),Age INT,Gender CHAR(1))INSERT INTO Person VALUES ('Ted',23,'M')INSERT INTO Person VALUES ('John',40,'M')INSERT INTO Person VALUES ('George',6,'M')INSERT INTO Person VALUES ('Mary',11,'F')INSERT INTO Person VALUES ('Sam',17,'M')INSERT INTO Person VALUES ('Doris',6,'F')INSERT INTO Person VALUES ('Frank',38,'M')INSERT INTO Person VALUES ('Larry',5,'M')INSERT INTO Person VALUES ('Sue',29,'F')INSERT INTO Person VALUES ('Sherry',11,'F')INSERT INTO Person VALUES ('Marty',23,'F')直接用例子说明问题
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],FirstName,AgeFROM Person出现的数据如下
 Row Number by Age    FirstName  Age-------------------- ---------- -----------1                    Larry      52                    Doris      63                    George     64                    Mary       115                    Sherry     116                    Sam        177                    Ted        238                    Marty      239                    Sue        2910                   Frank      3811                   John       40
 
可以观察到,是根据年龄升序排列了，并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,
 
如果不想按年龄排序,可以这样写
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],FirstName,AgeFROM Person
另外一个例子
SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
FirstName,Age,GenderFROM Person
这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下
Partition by Gender  FirstName  Age         Gender-------------------- ---------- ----------- ------1                    Doris      6           F2                    Mary       11          F3                    Sherry     11          F4                    Sue        29          F1                    Larry      5           M2                    George     6           M3                    Sam        17          M4                    Ted        23          M5                    Marty      23          M6                    Frank      38          M7                    John       40          M
注意,姓名M开始,序号又从1,2,3开始了

2 RANK函数
  先看例子
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],FirstName,AgeFROM Person

输出如下

Rank by Age          FirstName  Age-------------------- ---------- -----------1                    Larry      52                    Doris      62                    George     64                    Mary       114                    Sherry     116                    Sam        177                    Ted        237                    Marty      239                    Sue        2910                   Frank      3811                   John       40

看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],

FirstName, Age, Gender FROM Person输出为

Partition by Gender  FirstName  Age         Gender-------------------- ---------- ----------- ------1                    Doris      6           F2                    Mary       11          F2                    Sherry     11          F4                    Sue        29          F1                    Larry      5           M2                    George     6           M3                    Sam        17          M4                    Ted        23          M4                    Marty      23          M6                    Frank      38          M7                    John       40          M

可以看到,按性别分组了,每个性别分组里,继续是用了rank函数

3 DENSE_RANK 函数
SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
FirstName,
Age
FROM Person
输出结果为
Dense Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40

看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了

4 ntile函数

SELECT FirstName,Age,NTILE(3) OVER (ORDER BY Age) AS [Age Groups]FROM Person

输出

FirstName  Age         Age Groups---------- ----------- --------------------Larry      5           1Doris      6           1George     6           1Mary       11          1Sherry     11          2Sam        17          2Ted        23          2Marty      23          2Sue        29          3Frank      38          3John       40          3

这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1
段,sherry到maty是第2段,sue到john是第3段了