Comment trouver le nième salaire le plus élevé par département avec SQL

Apprenez à trouver le nième salaire le plus élevé en SQL, et vous apprendrez à obtenir la nième valeur dans n'importe quelle donnée.

Dans cet article, je vais me concentrer sur un problème et vous donner plusieurs solutions à ce problème. Le problème est déjà énoncé dans le titre : trouver le nième salaire le plus élevé par département en utilisant SQL.

Cela peut sembler trop spécifique. Mais en apprenant à le résoudre, vous serez en mesure de trouver la nième valeur dans n'importe quelle donnée, et pas seulement dans les salaires. Vous aurez une idée de la façon de résoudre d'autres problèmes similaires que vous rencontrerez.

Comme je l'ai dit, nous n'examinerons qu'un seul problème, mais nous explorerons plusieurs solutions. En fait, je vais vous montrer quatre solutions utilisant ces fonctions de fenêtre :

• NTH_VALUE()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Si vous avez besoin d'aide avec la syntaxe ou tout autre détail concernant ces fonctions, n'hésitez pas à consulter la Cheat Sheet de SQL Fonctions de fenêtrage . Vous pouvez également jeter un coup d'œil à cet exemple pour voir comment les fonctions de fenêtre fonctionnent généralement.

Les données que je vais utiliser

Nous allons utiliser des données stockées dans les deux tableaux suivants :

• employee
• department

Voici à quoi ressemblent les données du tableau employee ressemblent :

Le tableau department contient les données suivantes :

Quelle est la tâche ici ?

Trouvons le troisième salaire le plus élevé par département. Cela signifie qu'il faut trouver la troisième valeur la plus élevée, non pas globalement mais dans chaque sous-ensemble, où un sous-ensemble contient les salaires d'un département donné. L'outil le plus utile pour ce faire est la fonction fenêtre.

Voici donc la première solution utilisant une fonction fenêtre.

Utilisation de NTH_VALUE()

L'objectif de la fonction NTH_VALUE() est d'obtenir la valeur de la nième ligne de l'ensemble de données. Voici comment nous pouvons l'utiliser pour obtenir le troisième salaire le plus élevé par département :

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		NTH_VALUE (salary, 3) OVER (
PARTITION BY department_name ORDER BY salary DESC
			RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest_salary
FROM department d JOIN employee e ON d.id = e.department_id;

Ce code sélectionne d'abord certaines colonnes dans les tableaux employee et department. Pour utiliser NTH_VALUE(), nous devons spécifier la colonne et la valeur de N. Puisque nous voulons obtenir le troisième salaire le plus élevé salary, la colonne est le salaire, et N = 3; par conséquent, nous avons NTH_VALUE(salary, 3). Nous obtenons ainsi le troisième salaire le plus élevé.

Pour qu'une fonction fenêtre fonctionne, nous devons utiliser une clause OVER(). N'oubliez pas que nous recherchons le troisième salaire le plus élevé dans le département chaque département. Pour ce faire, nous utilisons PARTITION BY pour diviser les données en sous-ensembles par la colonne nom_département de la table department. Puisque nous recherchons le troisièmele plus élevé salaire, nous utilisons ORDER BY pour que les fonctions de la fenêtre effectuent les calculs du salaire le plus élevé au salaire le plus bas. C'est la raison pour laquelle nous utilisons ORDER BY salaire DESC dans le code. Nous avons également besoin de la clause RANGE, dans ce cas, RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Cela signifie que la fonction fenêtre va chercher entre la première et la dernière ligne de la partition.

Bien sûr, pour obtenir tout cela, nous devons joindre les deux tables.

Voici le résultat :

Le résultat nous indique que le troisième salaire le plus élevé en comptabilité est de 5 371,02, ce qui correspond au salaire de Lois Skain. Le salaire d'Abra Clemon (5 564,25) est le troisième salaire le plus élevé dans la catégorie Ressources humaines. En Recherche et développement, le salaire de Zachariah Rapi est le troisième plus élevé (6 657,11).

Utilisation de ROW_NUMBER()

La deuxième option pour obtenir le troisième salaire le plus élevé par département est d'utiliser ROW_NUMBER(). Cette fonction de fenêtre renvoie les numéros d'ordre des lignes d'un ensemble de données. Si nous classons les salaires dans chaque département, il sera facile de trouver le troisième salaire le plus élevé. Voici à quoi ressemble la solution :

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		ROW_NUMBER() OVER (
PARTITION BY d.id ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
ORDER BY department_name;

Cette solution sélectionne toutes les mêmes colonnes que dans la solution précédente. La différence est que nous utilisons ici ROW_NUMBER() au lieu de NTH_VALUE(). Tous les critères de la clause OVER() sont les mêmes que dans la solution précédente.

Le résultat ressemble à ceci :

Il semble un peu différent du résultat de la solution précédente. La dernière colonne ne contient pas la valeur du troisième salaire le plus élevé. Au lieu de cela, elle contient un classement, et nous pouvons facilement voir que l'effet est le même. Les valeurs du troisième salaire le plus élevé par département sont 5 371,02 (Comptabilité), 5 564,25 (Ressources humaines) et 6 657,11 (Recherche et développement), soit les mêmes que celles obtenues dans la première solution.

Nous pouvons également utiliser ROW_NUMBER() dans un CTE. Si vous n'êtes pas familier avec les CTE, cet article explique ce que c'est. Par exemple, nous pouvons écrire le code ci-dessous :

WITH salaries_ranks AS (
SELECT e.first_name,
 e.last_name,
 d.department_name,
 salary,
 ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY salary DESC
 ) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
)

SELECT *
FROM salaries_ranks
WHERE salary_rank = 3;

Nous commençons notre CTE en utilisant un WITH, suivi du nom du CTE, salaries_ranks. Ensuite, nous écrivons une instruction SELECT, qui est exactement la même que dans le code précédent.

Maintenant que nous avons écrit un CTE, nous pouvons l'utiliser comme n'importe quelle autre table. Nous le faisons dans une instruction distincte SELECT qui sélectionne toutes les colonnes du CTE salaries_ranksen ne montrant que les lignes où salary_rank = 3.

Le résultat de ce code est :

C'est une solution assez élégante. Nous obtenons uniquement les données nécessaires - seulement trois lignes indiquant le troisième salaire le plus élevé pour chaque département.

Utilisation de RANK()

La troisième option consiste à utiliser la fonction RANK(). Elle est similaire à ROW_NUMBER(), mais elle classe également les lignes au sein d'une partition. Similaire, mais pas identique. Je parlerai des différences plus tard, ou vous pouvez les lire ici.

Écrivons le code en utilisant RANK() :

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Encore une fois, ce code n'est pas très différent des précédents, sauf pour la fonction fenêtre spécifique utilisée. Ici, il s'agit de RANK(), avec tous les mêmes critères dans la clause OVER(). Le résultat sera exactement le même que lorsque nous avons utilisé ROW_NUMBER(), donc je ne pense pas qu'il soit nécessaire de montrer à nouveau le même tableau de résultats.

Utilisation de DENSE_RANK()

La dernière solution que je vais vous présenter ici est la fonction de fenêtre DENSE_RANK(). Tout comme ROW_NUMBER() et RANK(), elle classe les valeurs dans un ensemble de données.

Le code n'est pas vraiment différent non plus. Il est le même que précédemment, mais utilise une autre fonction de fenêtre :

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		DENSE_RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC 
		) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Lorsque nous exécutons ce code, nous obtenons exactement le même résultat que celui obtenu avec les deux solutions précédentes.

Aperçu des concepts et de leurs différences

Comme vous l'avez vu, vous pouvez utiliser n'importe laquelle de ces quatre fonctions de fenêtre pour obtenir le troisième salaire le plus élevé par département.

La fonction NTH_VALUE() vous indique explicitement la valeur du troisième salaire le plus élevé par département.

Les fonctions ROW_NUMBER(), RANK(), et DENSE_RANK() classent les salaires au sein de chaque département. Il vous suffit ensuite de trouver la valeur salariale associée au rang 3. Ces trois fonctions sont similaires mais pas identiques. Elles semblent identiques dans cet exemple, mais d'autres données peuvent révéler leurs différences.

Voici une explication de leurs différences pour vous éviter de mauvaises surprises.

Les différences apparaissent lorsque vous avez des liens (plusieurs lignes avec la même valeur) dans vos données. Je savais que je n'avais pas de liens dans mes données, et donc que les trois fonctions me donneraient la même solution.

Mais que se passe-t-il si vous avez des liens ? Dans ce cas, ROW_NUMBER() attribue les rangs de manière séquentielle, c'est-à-dire que les ex æquo ont des numéros de rang différents attribués de manière arbitraire. En revanche, RANK() attribue le même numéro de rang aux mêmes valeurs et saute un numéro de rang pour le compenser lorsqu'il arrive à une ligne avec une valeur différente. Enfin, DENSE_RANK() attribue le même numéro de rang aux égalités, sans sauter un numéro de rang à la valeur suivante. Voici un exemple à titre d'illustration :

Si vous cherchez la valeur du troisième salaire le plus élevé dans ces données, ROW_NUMBER() vous donnera la solution correcte ; le salaire de 4 000 est le troisième plus élevé. Cependant, la solution est incorrecte si vous recherchez tous les employés ayant le troisième salaire le plus élevé. Elle n'afficherait que l'employé 2 alors que l'employé 5 devrait également être affiché. Dans ce cas, RANK() ou DENSE_RANK() serait un meilleur choix.

Et si vous recherchez la valeur du quatrième salaire le plus élevé ? La fonction ROW_NUMBER() vous donnerait une solution totalement erronée, puisque les troisième et quatrième valeurs les plus élevées sont les mêmes. L'utilisation de RANK() ne donne aucun résultat, car elle ignore le numéro de rang quatre - comme nous l'avons expliqué précédemment, elle attribue le même numéro de rang aux égalités et ignore le numéro de rang suivant pour le compenser. Seule DENSE_RANK() vous donne une solution correcte dans ce cas.

Avant de décider de la fonction à utiliser, assurez-vous de bien comprendre les données et ce que vous essayez d'obtenir comme solution. La solution la plus sûre est d'utiliser NTH_VALUE(). Si vous souhaitez utiliser une fonction de classement, il est généralement préférable d'utiliser DENSE_RANK() lorsque vous ne savez pas s'il y a des égalités dans les données. Si vous utilisez plutôt ROW_NUMBER() ou RANK(), assurez-vous de savoir à quoi ressemblerait le résultat. Il se peut que l'une de ces deux fonctions ou les deux vous donnent ce dont vous avez besoin. Choisissez la fonction en fonction de vos besoins.

Vous pouvez également utiliser ces quatre fonctions lorsque vous souhaitez trouver le salaire maximum ou minimum par département. Ou, par exemple, le produit le plus ou le moins cher par catégorie de produits. Ou encore le produit dont le prix est le n-ième plus élevé. Vous pouvez rechercher le nième chiffre d'affaires le plus élevé ou le nième chiffre d'affaires le plus bas, les recettes, les heures travaillées, les coûts, le nombre de " likes ", de connexions, d'engagements, de flux, de commentaires, etc.

Si vous utilisez MySQL, cette introduction complète vous présentera ces quatre fonctions et toutes les autres fonctions de fenêtre de MySQL.

Différentes manières d'obtenir la Nième valeur SQL

La fonction NTH_VALUE() est parfaite pour trouver le nième salaire le plus élevé ou la nième valeur de toute autre colonne. Après tout, elle a été conçue exactement dans ce but. Je vous ai montré comment obtenir le même effet avec trois autres fonctions de fenêtre : ROW_NUMBER(), RANK(), et DENSE_RANK(). Utilisez celle qui convient le mieux à vos besoins et à vos données.

Mais il ne s'agit là que de quatre fonctions de fenêtre. Ce n'est que la partie émergée de l'iceberg, et il y en a bien d'autres ! Ces fonctions et d'autres fonctions de fenêtre sont expliquées dans ce cours sur les fonctions de fenêtre.