SQL RANK() vs ROW_NUMBER()

只有在分区中为特定排序值设置了关联时，才会看到差异。

在这种情况下，RANK和DENSE_RANK是确定的，对于排序列和分区列具有相同值的所有行将最终得到相同的结果，而ROW_NUMBER将任意(非确定地)将递增结果分配给绑定行。

例子:(所有行都有相同的StyleID，所以在同一个分区中，在该分区中，前3行被绑定，当由ID排序时)

WITH T(StyleID, ID)
AS (SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2)
SELECT *,
RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS [RANK],
ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS [ROW_NUMBER],
DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS [DENSE_RANK]
FROM   T

返回

StyleID     ID       RANK      ROW_NUMBER      DENSE_RANK
----------- -------- --------- --------------- ----------
1           1        1         1               1
1           1        1         2               1
1           1        1         3               1
1           2        4         4               2

你可以看到，对于三个相同的行，ROW_NUMBER增加，RANK值保持不变，然后它跳转到4。DENSE_RANK也将相同的秩赋给所有三行，但下一个不同的值被赋值为2。

相当多:

一行的秩是1加上前面一行的秩数。

Row_number是行的唯一秩，在秩上没有任何差距。

http://www.bidn.com/blogs/marcoadf/bidn-blog/379/ranking-functions-row_number-vs-rank-vs-dense_rank-vs-ntile

ROW_NUMBER:为以1开头的每一行返回一个唯一的数字。对于具有重复值的行，将任意分配数字。

排名:为从1开始的每一行分配一个唯一的数字，除了有重复值的行，在这种情况下，分配相同的排名，并且在每个重复排名的序列中出现一个空白。

本文将介绍__ABC0和DENSE_RANK()之间的有趣关系 (RANK()函数没有被特别处理)。当你需要在SELECT DISTINCT语句上生成ROW_NUMBER()时，ROW_NUMBER()将生成不同的值之前，它们被DISTINCT关键字删除。例如这个查询

SELECT DISTINCT
v,
ROW_NUMBER() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

．.． 可能会产生这个结果(DISTINCT没有影响):

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |
+---+------------+

鉴于此查询:

SELECT DISTINCT
v,
DENSE_RANK() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

．.． 在这种情况下产生你可能想要的:

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| b |          2 |
| c |          3 |
| d |          4 |
| e |          5 |
+---+------------+

注意，DENSE_RANK()函数的ORDER BY子句需要SELECT DISTINCT子句中的所有其他列才能正常工作。

这样做的原因是逻辑上，窗口函数在应用DISTINCT之前计算. c。

这三种功能的比较

使用PostgreSQL / Sybase / SQL标准语法(WINDOW子句):

SELECT
v,
ROW_NUMBER() OVER (window) row_number,
RANK()       OVER (window) rank,
DENSE_RANK() OVER (window) dense_rank
FROM t
WINDOW window AS (ORDER BY v)
ORDER BY v

．.． 你会得到:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

看这个例子。

CREATE TABLE [dbo].#TestTable(
[id] [int] NOT NULL,
[create_date] [date] NOT NULL,
[info1] [varchar](50) NOT NULL,
[info2] [varchar](50) NOT NULL,
)

插入一些数据

INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/1/09', 'Blue', 'Green')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/2/09', 'Red', 'Yellow')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/3/09', 'Orange', 'Purple')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (2, '1/1/09', 'Yellow', 'Blue')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (2, '1/5/09', 'Blue', 'Orange')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (3, '1/2/09', 'Green', 'Purple')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (3, '1/8/09', 'Red', 'Blue')

1重复相同的值

插入dbo。#TestTable (id, create_date, info1, info2) VALUES '1/1/09'， '蓝色'，'绿色')

看所有

SELECT * FROM #TestTable

看看你的结果

SELECT Id,
create_date,
info1,
info2,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY create_date DESC) AS RowId,
RANK() OVER(PARTITION BY Id ORDER BY create_date DESC)    AS [RANK]
FROM #TestTable

需要了解的不同

另外，在使用RANK时，注意PARTITION中的ORDER BY(例如使用Standard AdventureWorks db)。

< p >选择as1。SalesOrderID as1。SalesOrderDetailID, RANK()结束 分区由as1。SalesOrderID ORDER BY as1。) ranknoequal ， RANK() OVER(分区为as1.)由SalesOrderID订购 as1。SalesOrderDetailId) ranknodiff FROM Sales。SalesOrderDetail as1 ORDER BY SalesOrderDetailId;

给出结果:

但是如果将顺序更改为(使用OrderQty:

< p >选择as1。SalesOrderID as1。OrderQty, RANK() OVER (PARTITION BY as1。SalesOrderID ORDER BY as1。ranknoequal, RANK() OVER(分区为as1。SalesOrderID ORDER BY as1。OrderQty) 从销售。WHERE SalesOrderId = 43659 ORDER BY OrderQty; < / p >

给:

请注意，当我们在ORDER BY中使用OrderQty(最右边的列第二表)时，Rank是如何变化的，以及当我们在ORDER BY中使用SalesOrderDetailID(最右边的列第一表)时，Rank是如何变化的。

我没有做任何关于秩的事情，但是我今天用row_number()发现了这一点。

select item, name, sold, row_number() over(partition by item order by sold) as row from table_name

这将导致一些重复的行号，因为在我的例子中，每个名称都包含所有项。每一件商品都将按售出的数量排序。

+--------+------+-----+----+
|glasses |store1|  30 | 1  |
|glasses |store2|  35 | 2  |
|glasses |store3|  40 | 3  |
|shoes   |store2|  10 | 1  |
|shoes   |store1|  20 | 2  |
|shoes   |store3|  22 | 3  |
+--------+------+-----+----+

没有分区子句的简单查询:

select
sal,
RANK() over(order by sal desc) as Rank,
DENSE_RANK() over(order by sal desc) as DenseRank,
ROW_NUMBER() over(order by sal desc) as RowNumber
from employee

输出:

    --------|-------|-----------|----------
sal     |Rank   |DenseRank  |RowNumber
--------|-------|-----------|----------
5000    |1      |1          |1
3000    |2      |2          |2
3000    |2      |2          |3
2975    |4      |3          |4
2850    |5      |4          |5
--------|-------|-----------|----------

注意，所有这些窗口函数都返回一个类似整型的值。

数据库通常会选择BIGINT数据类型，这占用的空间比我们需要的大得多。而且，我们很少需要从-9,223,372,036,854,775,808到+9,223,372,036,854,775,807的范围。

将结果转换为BYTEINT、SMALLINT或INTEGER。

这些现代系统和硬件是如此强大，所以你可能永远不会看到有意义的额外资源使用，但我认为这是最佳实践。