Fonction de fenêtrage DENSE_RANK

La fonction de fenêtrage DENSE_RANK détermine le rang d’une valeur dans un groupe de valeurs, en fonction de l’expression ORDER BY dans la clause OVER. Si la clause PARTITION BY facultative est présente, les rangs sont réinitialisés pour chaque groupe de lignes. Les lignes avec des valeurs égales pour les critères de rang reçoivent le même rang. La fonction DENSE_RANK diffère de RANK sur un point : si deux lignes ou plus sont à égalité, il n’y a pas d’écart dans la séquence des valeurs classées. Par exemple, si deux lignes sont classées 1, le prochain rang est 2.

Vous pouvez avoir des fonctions de rang avec différentes clauses PARTITION BY et ORDER BY dans la même requête.

Syntaxe

DENSE_RANK() OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list ]
) 

Arguments

( )

La fonction ne prend pas d’arguments, mais les parenthèses vides sont obligatoires.

OVER

Clauses de fenêtrage pour la fonction DENSE_RANK.

PARTITION BY expr_list

(Facultatif) Une ou plusieurs expressions qui définissent le fenêtrage.

ORDER BY order_list

(Facultatif) Expression sur laquelle sont basées les valeurs de rang. Si aucune clause PARTITION BY n’est spécifiée, ORDER BY utilise toute la table. Si ORDER BY n’est pas spécifié, la valeur renvoyée est 1 pour toutes les lignes.

Si ORDER BY ne génère pas d’ordonnancement unique, l’ordre des lignes est non déterministe. Pour plus d'informations, consultez Ordonnancement unique des données pour les fonctions de fenêtrage.

Type de retour

INTEGER

Exemples

Les exemples suivants utilisent l’exemple de table pour les fonctions de fenêtrage. Pour plus d’informations, consultez Exemple de tableau contenant des exemples de fonctions de fenêtrage.

L’exemple suivant ordonne la table en fonction de la quantité vendue et affecte un rang dense et un rang standard à chaque ligne. Les résultats sont triés une fois que les résultats de la fonction de fenêtrage sont appliqués.

SELECT salesid, qty,
DENSE_RANK() OVER(ORDER BY qty DESC) AS d_rnk,
RANK() OVER(ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY 2,1;

+---------+-----+-------+-----+
| salesid | qty | d_rnk | rnk |
+---------+-----+-------+-----+
|   10001 |  10 |     5 |   8 |
|   10006 |  10 |     5 |   8 |
|   30001 |  10 |     5 |   8 |
|   40005 |  10 |     5 |   8 |
|   30003 |  15 |     4 |   7 |
|   20001 |  20 |     3 |   4 |
|   20002 |  20 |     3 |   4 |
|   30004 |  20 |     3 |   4 |
|   10005 |  30 |     2 |   2 |
|   30007 |  30 |     2 |   2 |
|   40001 |  40 |     1 |   1 |
+---------+-----+-------+-----+

Notez la différence entre les rangs affectés au même ensemble de lignes lorsque les fonctions DENSE_RANK et RANK sont utilisées côte à côte dans la même requête.

L’exemple suivant partitionne la table en fonction de sellerid, ordonne chaque partition selon la quantité et affecte un rang dense à chaque ligne. Les résultats sont triés une fois que les résultats de la fonction de fenêtrage sont appliqués.

SELECT salesid, sellerid, qty,
DENSE_RANK() OVER(PARTITION BY sellerid ORDER BY qty DESC) AS d_rnk
FROM winsales
ORDER BY 2,3,1;

+---------+----------+-----+-------+
| salesid | sellerid | qty | d_rnk |
+---------+----------+-----+-------+
|   10001 |        1 |  10 |     2 |
|   10006 |        1 |  10 |     2 |
|   10005 |        1 |  30 |     1 |
|   20001 |        2 |  20 |     1 |
|   20002 |        2 |  20 |     1 |
|   30001 |        3 |  10 |     4 |
|   30003 |        3 |  15 |     3 |
|   30004 |        3 |  20 |     2 |
|   30007 |        3 |  30 |     1 |
|   40005 |        4 |  10 |     2 |
|   40001 |        4 |  40 |     1 |
+---------+----------+-----+-------+

Pour utiliser correctement le dernier exemple, utilisez la commande suivante pour insérer une ligne dans la table WINSALES. Cette ligne possède les mêmes buyerid, sellerid et qtysold qu’une autre ligne. Cela entraîne une égalité entre deux lignes dans le dernier exemple et montre ainsi la différence entre les fonctions DENSE_RANK et RANK.

INSERT INTO winsales VALUES(30009, '2/2/2003', 3, 'b', 20, NULL);

L’exemple suivant partitionne la table en fonction de buyerid et sellerid, ordonne chaque partition selon la quantité et affecte un rang dense et un rang standard à chaque ligne. Les résultats sont triés après l’application de la fonction de fenêtrage.

SELECT salesid, sellerid, qty, buyerid,
DENSE_RANK() OVER(PARTITION BY buyerid, sellerid ORDER BY qty DESC) AS d_rnk,
RANK() OVER (PARTITION BY buyerid, sellerid ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY rnk;

+---------+----------+-----+---------+-------+-----+
| salesid | sellerid | qty | buyerid | d_rnk | rnk |
+---------+----------+-----+---------+-------+-----+
|   20001 |        2 |  20 | b       |     1 |   1 |
|   30007 |        3 |  30 | c       |     1 |   1 |
|   10006 |        1 |  10 | c       |     1 |   1 |
|   10005 |        1 |  30 | a       |     1 |   1 |
|   20002 |        2 |  20 | c       |     1 |   1 |
|   30009 |        3 |  20 | b       |     1 |   1 |
|   40001 |        4 |  40 | a       |     1 |   1 |
|   30004 |        3 |  20 | b       |     1 |   1 |
|   10001 |        1 |  10 | c       |     1 |   1 |
|   40005 |        4 |  10 | a       |     2 |   2 |
|   30003 |        3 |  15 | b       |     2 |   3 |
|   30001 |        3 |  10 | b       |     3 |   4 |
+---------+----------+-----+---------+-------+-----+