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

时间:2022-05-07 10:42:30

相信大家在软件工程中经常会遇到对某些数据进行排名的问题,尤其是对于电子商务的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),('',100),('',100),
('',100),('',99),('',99),('',99),('',98),
('',98),('',97),('',96),('',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+.
DENSE_RANK():允许行具有相同的时候相同的排名,在遇到不同的值得时候采用上次的排名进行+1处理。
例如N个相同的值排名为1,那么在N+1的时候排名 采用上次的排名值也就是N+.
NTILE(X):这个函数可以说很少使用。几乎是个废柴,看上面的代码就明白了。