SQLServer针对排名函数ROWNUMBER()、RANK()、DE

系统 1731 0

 

     相信大家在软件工程中经常会遇到对某些数据进行排名的问题,尤其是对于电子商务的HR来说“大手笔”是非常具有潜在价值的!~至于都有哪些价值这个超出本文的范畴不予进行说明,但是不得不说的是每一个精明的HR以下类似的需求:

    

  • 我需要系统告诉我,截止到目前为止,近几个月内销售人员的订单交易数量排名、奖金排名(对内部员工员工)
  • 我需要系统告诉我,截止到目前为止,商品热度的排名、购买力度的排名、充值力度的排名、提款力度的排名,相关地区的的排名(对客户)

 

     对于SQL新人来说,第一个想到的函数 TOP 配合 ROW_NUMBER() ORDER BY ,如果你用了这3个配合,那么恭喜你, You're Wrong! 

     因为上述的情况,可能会发生相同数据的排名,那么一旦排名的数据发生相同,因为 ROWNUMBER() 类似于 IDENTITY (起始1,自增1)所以对排名的准确性就不那么明确了。  

    下面来看具体的例子:

    基础数据准备:

 

        
          
            /*
          
          
            以下代码执行完成只是为了讲解说明,执行完成需要刷新下IntelliSence缓存,更新下当前智能提示 *键盘快捷键 Ctrl+Shift+R。 *@author 系统管理员-咔咔 *@time 2013-11-25 
          
          
            */
          
          
            USE
          
          
             MyDB; 
          
          
            IF
          
          
            EXISTS
          
           (
          
            Select
          
          
            *
          
          
            From
          
           sys.objects 
          
            Where
          
           name 
          
            =
          
          N
          
            '
          
          
            EmployeOrdersCount
          
          
            '
          
          
            And
          
           Type 
          
            In
          
           (
          
            '
          
          
            S
          
          
            '
          
          ,
          
            '
          
          
            U
          
          
            '
          
          
            )) 
          
          
            DROP
          
          
            TABLE
          
          
             EmployeOrdersCount 
          
          
            ELSE
          
          
            CREATE
          
          
            TABLE
          
           EmployeOrdersCount 
          
            --
          
          
            员工订单统计
          
          
             ( Id 
          
          
            INT
          
          
            PRIMARY
          
          
            KEY
          
          
            IDENTITY
          
          ,
          
            --
          
          
            主键ID
          
          

  EmployeNO 
          
            NVARCHAR
          
          (
          
            15
          
          ),     
          
            --
          
          
            员工编号
          
          

  OrdersCount 
          
            INT
          
          ,            
          
            --
          
          
            订单数量
          
          
             ) 
          
          
            INSERT
          
          
            INTO
          
          
             EmployeOrdersCount(EmployeNO,OrdersCount) 
          
          
            VALUES
          
          (
          
            '
          
          
            100
          
          
            '
          
          ,
          
            100
          
          ),(
          
            '
          
          
            102
          
          
            '
          
          ,
          
            100
          
          ),(
          
            '
          
          
            103
          
          
            '
          
          ,
          
            100
          
          ),(
          
            '
          
          
            104
          
          
            '
          
          ,
          
            100
          
          
            ), (
          
          
            '
          
          
            105
          
          
            '
          
          ,
          
            100
          
          ),(
          
            '
          
          
            106
          
          
            '
          
          ,
          
            99
          
          ),(
          
            '
          
          
            107
          
          
            '
          
          ,
          
            99
          
          ),(
          
            '
          
          
            108
          
          
            '
          
          ,
          
            99
          
          ),(
          
            '
          
          
            109
          
          
            '
          
          ,
          
            98
          
          
            ), (
          
          
            '
          
          
            110
          
          
            '
          
          ,
          
            98
          
          ),(
          
            '
          
          
            111
          
          
            '
          
          ,
          
            97
          
          ),(
          
            '
          
          
            112
          
          
            '
          
          ,
          
            96
          
          ),(
          
            '
          
          
            113
          
          
            '
          
          ,
          
            100
          
          )
        
      

  执行命令:

      
        SELECT
      
       ROW_NUMBER() 
      
        OVER
      
      (
      
        ORDER
      
      
        BY
      
       OrdersCount 
      
        desc
      
      ) 
      
        AS
      
      
        '
      
      
        RowNumber
      
      
        '
      
      
        ,

     RANK() 
      
      
        OVER
      
      (
      
        ORDER
      
      
        BY
      
       OrdersCount 
      
        desc
      
      ) 
      
        AS
      
      
        '
      
      
        Rank
      
      
        '
      
      
        ,

     DENSE_RANK() 
      
      
        OVER
      
      (
      
        ORDER
      
      
        BY
      
       OrdersCount 
      
        desc
      
      ) 
      
        AS
      
      
        '
      
      
        Dense_rank
      
      
        '
      
      
        ,

     NTILE(
      
      
        4
      
      ) 
      
        OVER
      
      (
      
        ORDER
      
      
        BY
      
       OrdersCount 
      
        desc
      
      ) 
      
        AS
      
      
        '
      
      
        ntile
      
      
        '
      
      
        

        ,EmployeNO, OrdersCount

      
      
      
        FROM
      
       EmployeOrdersCount
    

 结果如下:

      
        RowNumber            Rank                 Dense_rank           ntile                EmployeNO       OrdersCount


      
      
        --
      
      
        ------------------ -------------------- -------------------- -------------------- --------------- -----------
      
      
        1
      
      
        1
      
      
        1
      
      
        1
      
      
        100
      
      
        100
      
      
        2
      
      
        1
      
      
        1
      
      
        1
      
      
        102
      
      
        100
      
      
        3
      
      
        1
      
      
        1
      
      
        1
      
      
        103
      
      
        100
      
      
        4
      
      
        1
      
      
        1
      
      
        1
      
      
        104
      
      
        100
      
      
        5
      
      
        1
      
      
        1
      
      
        2
      
      
        105
      
      
        100
      
      
        6
      
      
        1
      
      
        1
      
      
        2
      
      
        113
      
      
        100
      
      
        7
      
      
        7
      
      
        2
      
      
        2
      
      
        106
      
      
        99
      
      
        8
      
      
        7
      
      
        2
      
      
        3
      
      
        107
      
      
        99
      
      
        9
      
      
        7
      
      
        2
      
      
        3
      
      
        108
      
      
        99
      
      
        10
      
      
        10
      
      
        3
      
      
        3
      
      
        109
      
      
        98
      
      
        11
      
      
        10
      
      
        3
      
      
        4
      
      
        110
      
      
        98
      
      
        12
      
      
        12
      
      
        4
      
      
        4
      
      
        111
      
      
        97
      
      
        13
      
      
        13
      
      
        5
      
      
        4
      
      
        112
      
      
        96
      
      
        



(
      
      
        13
      
       行受影响)
    

  结论如下: 

      
        
          ROWNUMBER():
        
        不关心行具有相同的值的问题,持续递增,类似于
        
          IDENTITY
        
          RANK():
        
        允许行具有相同的值的时候相同的排名,在遇到不同的值得时候重新进行
        
          ROWNUMBER()
        
        排名。

         
        
           例如N个相同的值排名为1, 那么在N
        
      
      
        +1的时候排名采用
        
          ROWNUMBER()
        
        的值也就是N+
        
          1
        
      
      
        
          . 
        
        
          DENSE_RANK():
        
        允许行具有相同的时候相同的排名,在遇到不同的值得时候采用上次的排名进行
      
      +
      
        1处理。 

         
        
          例如N个相同的值排名为1,那么在N
        
      
      
        +1的时候排名 采用上次的排名值也就是N+
        
          1
        
      
      
        
          . 
        
        
          NTILE(X)
        
        :这个函数可以说很少使用。几乎是个废柴,看上面的代码就明白了。
      
    

   

SQLServer针对排名函数ROWNUMBER()、RANK()、DENSE_RANK()、NTILE的研究!~


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧,狠狠点击下面给点支持吧,站长非常感激您!手机微信长按不能支付解决办法:请将微信支付二维码保存到相册,切换到微信,然后点击微信右上角扫一扫功能,选择支付二维码完成支付。

【本文对您有帮助就好】

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论