SQL RANK() versus ROW_NUMBER()

Question

Je ne comprends pas bien les différences entre les deux. L'exécution du SQL suivant me donne deux ensembles de résultats identiques. Quelqu'un peut-il m'expliquer les différences ?

SELECT ID, [Description], RANK()       OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank'      FROM SubStyle
SELECT ID, [Description], ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) as 'RowNumber' FROM SubStyle

Answer 1

Vous ne verrez la différence que si vous avez des liens dans une partition pour une valeur de commande particulière.

RANK et DENSE_RANK sont déterministes dans ce cas, toutes les lignes ayant la même valeur pour les colonnes d'ordonnancement et de partitionnement aboutiront à un résultat égal, alors que ROW_NUMBER attribuera arbitrairement (de manière non déterministe) un résultat incrémentiel aux lignes liées.

Exemple : (Toutes les rangées ont le même StyleID sont donc dans la même partition et à l'intérieur de cette partition, les 3 premières lignes sont à égalité lorsqu'elles sont classées par 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  

Renvoie à

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

Vous pouvez voir que pour les trois rangées identiques, l'indicateur ROW_NUMBER par incréments, le RANK reste la même, il passe à 4 . DENSE_RANK attribue également le même rang aux trois rangs, mais la valeur distincte suivante se voit attribuer la valeur 2.

Answer 2

ROW_NUMBER : Renvoie un numéro unique pour chaque ligne en commençant par 1. Pour les lignes qui ont des valeurs en double, les numéros sont attribués arbitrairement.

Rang : Attribue un numéro unique pour chaque ligne en commençant par 1, sauf pour les lignes qui ont des valeurs en double, auquel cas le même classement est attribué et un écart apparaît dans la séquence pour chaque classement en double.

Answer 3

Cet article traite d'une relation intéressante entre ROW_NUMBER() et DENSE_RANK() (le RANK() n'est pas traitée spécifiquement). Lorsque vous avez besoin d'un ROW_NUMBER() sur un SELECT DISTINCT déclaration, le ROW_NUMBER() produira des valeurs distinctes avant ils sont supprimés par le DISTINCT mot-clé. Par exemple, cette requête

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

... pourrait produire ce résultat ( DISTINCT n'a aucun effet) :

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

Alors que cette question :

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

... produit ce que vous voulez probablement dans ce cas :

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

Notez que le ORDER BY clause de la DENSE_RANK() aura besoin de toutes les autres colonnes du SELECT DISTINCT pour fonctionner correctement.

La raison en est que, logiquement, Les fonctions de fenêtre sont calculées avant DISTINCT est appliqué .

Les trois fonctions en comparaison

Utilisation de la syntaxe standard PostgreSQL / Sybase / SQL ( WINDOW clause) :

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

... vous obtiendrez :

+---+------------+------+------------+
| 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 |
+---+------------+------+------------+

Answer 4

Requête simple sans clause de partition :

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 

Sortie :

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

Answer 5

Un peu :

Le rang d'une ligne est égal à un plus le nombre de rangs qui précèdent la ligne en question.

Row_number est le rang distinct des rangs, sans aucun écart dans le classement.

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