par: Koen Verbeeck

aperçu

le sujet de cette partie concerne les fonctions de classement. Ils ont été introduits dans SQLServer 2005. ROW_NUMBER est également une fonction de classement, mais elle a déjà été couverte de manière extensive dans la partie précédente de ce tutoriel.

RANK and DENSE_RANK

contrairement à la fonction ROW_NUMBER,RANK andDENSE_RANK n’a pas besoin de générer de nombres uniques. La différence entretoutes ces fonctions est la façon dont elles gèrent les liens.,

• ROW_NUMBER générera toujours des valeurs uniques sans aucune lacune, même s’il y a des liens.
• RANK peut avoir des lacunes dans sa séquence et lorsque les valeurs sont les mêmes, elles obtiennent le même rang.
• DENSE_RANK renvoie également le même rang pour les liens, mais n’a pas d’anygaps dans la séquence.

illustrons avec un exemple., Supposons que nous ayons les valeurs d’échantillonnage suivantes:

Nous pouvons écrire la requête suivante pour tester les trois fonctions de classement:

cela nous donne cet ensemble de résultats:

comme vous pouvez le voir, RANK renverra le même rang pour les cravates. Cependant, le prochain groupe de rankin A est le numéro 3, omettant le rang 2. DENSE_RANK d’autre part doesn’tskip rang 2. Lorsqu’il n’y a pas d’égalité dans l’ensemble d’enregistrements, les trois fonctions de classement retournent exactement le même résultat.,

avec ROW_NUMBER et RANK, nous pouvons créer une construction alternative pour détecter duplicaterows. Avec les mêmes exemples de données utilisés dans la partie 5 de ce tutoriel, nous pouvons créer la requête suivante pour trouver toutes les lignes qui ont une clé commerciale en double:

le jeu de résultats:

maintenant, nous n’avons besoin de filtrer que sur les enregistrements Cela renverra un enregistrement pour Bob, donc nous savons qu’il y a un enregistrement en double pour cet employé.

quand utiliser RANK ou DENSE_RANK?,

comme spécifié précédemment, RANK peut avoir des lacunes dans la séquence et DENSE_RANK ne le fait pas.supposons que vous ayez la tâche de trouver les 5 meilleurs clients en fonction de la valeur des ventes.Avec RANK, vous pouvez classer vos clients, puis filtrer tous les clients avec arank plus grand que 5. Si vous faites la même chose avec DENSE_RANK, vous pourriez vous retrouver avec plus de 6 clients!,

Supposons que nous avons de cet ensemble de données sur nos clients:

Lors de l’ajout de rangs avec RANG et DENSE_RANK, nous avons les données suivantes:

Lorsque le filtrage sur le grade <= 5, vous obtiendrez exactement 5 lignes retournées. Cependant, si vous filtrez sur denserank <= 5, vous obtiendrez 6 lignes, ce qui pourrait ne pas être ce que vous voulez. Plus il y a de liens, plus dense_rank peut renvoyer de lignes. Si la cravate serait exactement sur la ligne numéro 5, la fonction RANK renverrait également 6 lignes., Dans ce cas, vous devez  » casser la cravate”. Vous pouvez le faire en utilisant ROW_NUMBERfunction ou en triant sur une colonne supplémentaire.

NTILE

La dernière fonction de classement est un peu spécial. NTILE divise les rangées en grossièrement seaux de taille égale. Supposons que vous ayez 20 lignes et que vous spécifiez NTILE (2). Cela vous donnera 2 seaux de 10 rangées chacun. Lorsque vous utilisez NTILE (3), vous obtenez 2 seaux avec 7 rangées et 1 seau avec 6 rangées.,

réutilisons la requête de la section précédente, mais ajoutons une expressionen utilisant NTILE:

nous obtenons les résultats suivants:

un exemple où NTILE a été utilisé pour diviser les données en compartiments est décrit dans letiphow pour créer un graphique de carte thermique dans SQL Server Reporting Services 2016.,

informations supplémentaires

• autres conseils sur les fonctions de classement:
  • SQL Server 2005 et 2008 fonctions de classement DENSE_RANK et NTILE
  • SQL Server 2005 et 2008 fonctions de classement Row_Number et Rank
  • Fonctions SQL Server oubliées – VARP, SOUNDEX et NTILE

dernière mise à jour: 31/5/2018