首页 >> 资讯 >> 数据库 >> SQL Server >> 查看全文

详解SQL Server 2005四种排名函数

来源: 数据库 SQL Server | 作者: landluo | 发布: 2009-7-08 10:09

2、RANK, DENSE_RANK

RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：

Code

SELECT speaker, track, score,

ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,

RANK() OVER(ORDER BY score DESC) AS rnk,

DENSE_RANK() OVER(ORDER BY score DESC) AS drnk

FROM SpeakerStats

speaker trackscore rownum rnk drnk

---------- ---------- ----------- ------ --- ----

Jessica Dev 9 1 1 1

Ron Dev 9 21 1

Suzanne DB 9 31 1

KathySys 8 44 2

Michele Sys 854 2

Mike DB 864 2

KevinDB 777 3

BrianSys 787 3

Joe Dev 699 4

Robert Dev 6 10 9 4

Dan Sys 311 11 5

正如前面讨论的那样，score 列不唯一，因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序，但是具有相同得分的演讲者仍然获得不同的行号。但是请注意，在结果中，所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说，当 ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于，排序代表：具有较高得分的行号加 1，而紧密排序代表：具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中，您可以推导出当 ORDER BY 列表唯一时，ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。

3、NTILE

NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码：第一组为 1，第二组为 2，等等。您可以在函数名称后面的括号中指定所请求的组号，在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n，则前面 n 个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照 score 降序将三个组号分配给不同的 speaker 行：

Code

SELECT speaker, track, score,

ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,

NTILE(3) OVER(ORDER BY score DESC) AS tile

FROM SpeakerStats

以下为结果集：

speaker trackscore rownum tile

---------- ---------- ----------- ------ ----

Jessica Dev 9 11

Ron Dev 9 21

Suzanne DB 9 31

KathySys 8 41

Michele Sys 8 52

Mike DB 8 62

KevinDB 7 72

BrianSys 7 82

Joe Dev 6 93

Robert Dev 6 10 3

Dan Sys 3 11 3

在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2，这意味着前面 2 个组将获得一个附加行（每个组中有 4 行），而第三个组则不会得到附加行（该组中有 3 行）。组号（tile 号）1 被分配给行 1 到 4，组号 2 被分配给行 5 到 8，组号 3 被分配给行 9 到 11。通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的演讲者，第二个梯级表示具有中等得分的演讲者，第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义：

Code

SELECT speaker, track, score,

CASE NTILE(3) OVER(ORDER BY score DESC)

WHEN 1 THEN 'High'

WHEN 2 THEN 'Medium'

WHEN 3 THEN 'Low'

END AS scorecategory

FROM SpeakerStats

以下为结果集：

speaker trackscore scorecategory

---------- ---------- ----------- -------------

KevinDB 7 Medium