La fonction RANK dans SQL Server est une sorte de fonction de classement. Cette fonction va attribuer le numéro à chaque ligne dans la partition d'une sortie . Il attribue le rang à chaque ligne comme un plus le rang de la ligne précédente. Lorsque la fonction RANK trouve deux valeurs identiques dans la même partition, elle leur attribue le même numéro de rang. De plus, le numéro suivant dans le classement sera le rang précédent plus les numéros en double. Par conséquent, cette fonction n’attribue pas toujours le classement des lignes dans un ordre consécutif.
La fonction RANK est également une sous-partie des fonctions de fenêtre. Les points suivants doivent être rappelés lors de l'utilisation de cette fonction :
- Cela fonctionne toujours avec la clause OVER().
- Il attribue un rang à chaque ligne en fonction de la clause ORDER BY.
- Il attribue un rang à chaque ligne dans un ordre consécutif.
- Il attribue toujours un rang aux lignes, en commençant par un pour chaque nouvelle partition.
REMARQUE : Rank attribue des valeurs temporaires aux lignes de la partition lorsque la requête est exécutée.
SQL Server fournit les fonctions de classement suivantes :
- RANG()
- ROW_NUMBER()
- DENSE_RANK()
- NTILE()
Apprenons chaque fonction de classement en détail. Tout d’abord, nous allons créer un tableau pour démontrer toutes ces fonctions. Les instructions suivantes créent une table nommée rang_démo avec trois colonnes :
CREATE TABLE rank_demo ( first_name VARCHAR(60), last_name VARCHAR(40), city VARCHAR(30) );
Ensuite, nous insérerons quelques lignes dans ce tableau comme ci-dessous :
INSERT INTO rank_demo (first_name, last_name, city) VALUES ('Luisa', 'Evans', 'Texas'), ('Paul', 'Ward', 'Alaska'), ('Peter', 'Bennett', 'California'), ('Carlos', 'Patterson', 'New York'), ('Rose', 'Huges', 'Florida'), ('Marielia', 'Simmons', 'Texas'), ('Antonio', 'Butler', 'New York'), ('Diego', 'Cox', 'California');
Nous pouvons vérifier la table à l'aide de l'instruction SELECT. Il affiche la sortie ci-dessous :
Fonction RANG()
Cette fonction est utilisée pour déterminer le classement de chaque ligne dans le jeu de résultats. La syntaxe suivante illustre l'utilisation d'une fonction RANK dans SQL Server :
SELECT column_name RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
Dans cette syntaxe,
- Le Clause SUR définit le partitionnement et l'ordre d'un résultat avant que la fonction de fenêtre associée ne soit appliquée.
- Le Clause PARTITION PAR divise la sortie produite par la clause FROM dans la partition. Ensuite, la fonction est appliquée à chaque partition et réinitialisée lorsque la frontière de division traverse les partitions. Si nous n'avons pas défini cette clause, la fonction traitera toutes les lignes comme une seule partition.
- Le COMMANDÉ PAR est une clause obligatoire qui détermine l'ordre des lignes de manière décroissante ou ascendante en fonction d'un ou plusieurs noms de colonnes avant que la fonction ne soit appliquée.
Exemple
Voyons comment fonctionne la fonction RANK() dans SQL Server. L'instruction ci-dessous utilisera la fonction de classement pour attribuer une numérotation à chaque ligne :
SELECT first_name, last_name, city, RANK () OVER (ORDER BY city) AS Rank_No FROM rank_demo;
Puisque nous n'avons pas utilisé le Clause PARTITION PAR , la fonction traitait l'ensemble du résultat comme une seule partition. L'exécution de l'instruction affichera le résultat ci-dessous :
Dans cette sortie, nous pouvons voir que certaines lignes obtiennent le même rang car elles ont la même valeur dans le colonne de la ville . Et le numéro suivant dans le classement sera son rang précédent plus un certain nombre de numéros en double.
La déclaration suivante est un autre exemple où nous allons utiliser une clause partition by qui divisera les lignes en fonction du ville colonne et attribuez un classement à chaque ligne d’une partition. L'ordre de sortie est basé sur le prénom :
SELECT first_name, last_name, city, RANK () OVER (PARTITION BY city ORDER BY first_name) AS Rank_No FROM rank_demo;
Il affichera la sortie ci-dessous :
Fonction ROW_NUMBER()
Cette fonction est utilisée pour renvoie le numéro séquentiel unique pour chaque ligne au sein de sa partition. La numérotation des lignes commence à un et augmente de un jusqu'à ce que le nombre total de lignes de la partition soit atteint. Il renverra les différents rangs de la ligne ayant des valeurs similaires qui la rendent différente de la fonction RANK().
La syntaxe ci-dessous illustre l'utilisation d'une fonction ROW_NUMBER() dans SQL Server :
SELECT column_name ROW_NUMBER() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Exemple
Exécutez la requête suivante pour attribuer un numéro de séquence à chaque ligne :
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Il attribuera le classement du tableau selon leur ville . Ici, nous pouvons voir qu'il attribue des rangs différents à la ligne qui a les mêmes valeurs de ville.
Si nous changeons l'ordre de tri de ascendant à descendant avec la clause ORDER BY, cette fonction modifiera également le RANK en conséquence. Voir la déclaration ci-dessous :
SELECT first_name, last_name, city, ROW_NUMBER() OVER(ORDER BY city DESC) AS my_rank FROM rank_demo;
Voici le résultat :
Fonction DENSE_RANK()
Cette fonction attribue un rang unique pour chaque ligne dans une partition selon la valeur de colonne spécifiée sans aucun espace. Il précise toujours le classement par ordre consécutif. Si nous obtenons un valeur en double , cette fonction lui attribuera le même rang, et le rang suivant étant le numéro séquentiel suivant. Cette caractéristique diffère de la fonction DENSE_RANK() de la fonction RANK().
La syntaxe ci-dessous illustre l'utilisation d'une fonction DENSE_RANK() dans SQL Server :
SELECT column_name DENSE_RANK() OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'rank_name' FROM table_name;
Exemple
La requête suivante utilise la fonction DENSE_RANK() pour attribuer un numéro de classement à chaque ligne :
SELECT first_name, last_name, city, DENSE_RANK() OVER(ORDER BY city) AS my_rank FROM rank_demo;
Il renverra la sortie ci-dessous où nous pouvons voir que les valeurs en double ont le même rang, et le rang suivant sera le numéro séquentiel suivant.
C'est un autre exemple de la fonction DENSE_RANK() utilisant la clause PARTITION BY. Cette clause divisera les lignes en fonction de la colonne city et l'ordre d'un ensemble de résultats est basé sur le prénom :
SELECT first_name, last_name, city, DENSE_RANK() OVER(PARTITION BY city ORDER BY first_name) AS my_rank FROM rank_demo;
Nous obtiendrons le résultat ci-dessous car il n’y a pas deux noms identiques. Dans ce cas, le résultat est similaire à la fonction RANK().
Mettons à jour le nom avec la requête suivante :
Update rank_demo set first_name = 'Diego' WHERE city = 'California'
Maintenant, exécutez à nouveau la même requête. Nous verrons que cette table porte le même nom dans Ville de Californie . Par conséquent, le rang est également le même pour les deux noms.
Fonction NTILE(N)
Cette fonction est utilisée pour répartir les lignes d'une partition ordonnée en un nombre prédéfini (N) de groupes à peu près égaux. Chaque groupe de lignes obtient son classement en fonction de la condition définie et commence la numérotation à partir d'un groupe. Il attribue un numéro de seau pour chaque ligne d'un groupe représentant le groupe auquel elle appartient.
La syntaxe suivante illustre l'utilisation d'une fonction NTILE() dans SQL Server :
mysql afficher tous les utilisateurs
SELECT column_name NTILE(N) OVER ( PARTITION BY expression ORDER BY expression [ASC|DESC]) AS 'my_rank' FROM table_name;
Exemple
La requête suivante utilise la fonction NTILE() pour attribuer un numéro de rang à chaque ligne :
SELECT first_name, last_name, city, NTILE(3) OVER(ORDER BY city) AS my_rank FROM rank_demo;
La table spécifiée a huit enregistrements . Par conséquent, la NTILE(3) indique que l'ensemble de résultats doit avoir un groupe de trois enregistrements . L'exécution de l'instruction affichera le résultat ci-dessous :
Cet article expliquera toutes les fonctions RANK utilisées dans SQL Server avec divers exemples. Il montre également les principales différences entre ces fonctions. Ces fonctions sont très utiles aux développeurs pour bien explorer et gérer leurs données.