SQL RANK（）与ROW_NUMBER（）

Question

我对这些之间的差异感到困惑。运行以下SQL会获得两个完美的结果集。有人可以解释一下这些差异吗？SELECT ID, [Description], RANK()       OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank'      FROM SubStyleSELECT ID, [Description], ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) as 'RowNumber' FROM SubStyle

Answer 1

ROW_NUMBER：返回以1开头的每一行的唯一编号。对于具有重复值的行，将对该数字进行仲裁。

排名：为以1开头的每一行指定唯一编号，但具有重复值的行除外，在这种情况下，将为每个重复排名分配相同的排名并在序列中显示间隙。

Answer 2

如果您在特定订购值的分区中存在关联，则只会看到差异。

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。

Answer 3

本文介绍了ROW_NUMBER()和之间有趣的关系DENSE_RANK()（RANK()函数没有特别处理）。当您需要ROW_NUMBER()在SELECT DISTINCT语句上生成时，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 |

+---+------------+

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

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

所有三个功能相比较

使用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 |

+---+------------+------+------------+