关闭 x
    ITJS.cn - 技术改变世界
    IT采购网

    详解SQL Server 2005四种排名函数(1)

    出处：ITJS

    下面通过具体的方案将用来讨论和演示不同的函数和它们的子句。

    十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：  

    Code
    Code
    

    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 顺序计算不同演讲者的行号、排序和紧密排序值：
    




    

     




    下面通过具体的方案将用来讨论和演示不同的函数和它们的子句。
    

    十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：  
    

    


    

    Code
    Code
    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
    Mike DB   8     Medium
    Suzanne    DB 9     High
    Jessica    Dev 9     High
    Joe  Dev  6     Low
    Robert     Dev 6     Low
    Ron  Dev 9     High
    BrianSys 7     Medium
    Dan  Sys3     Low
    KathySys8     High
    Michele    Sys8     Medium
    
	
	

	
	
    
 上一篇返回首页 下一篇
    
			
	
    

    声明：   此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。
    
	
    
	
    
				
    
					
    别人在看