Comment afficher la présence et l’absence des nièmes valeurs de groupe les plus élevées en SQL

En parcourant SQL pour préparer des entretiens, je tombe souvent sur cette question : trouvez l’employé avec le salaire le plus élevé ou (le deuxième plus élevé) en joignant une table contenant des informations sur l’employé à une autre qui contient des informations sur le service. Cela soulève une autre question : qu’en est-il de trouver l’employé qui gagne le nième-le salaire le plus élevé à l’échelle du département ?
Maintenant, je veux poser un scénario plus complexe : que se passera-t-il lorsqu’un service n’a pas d’employé gagnant le nième salaire le plus élevé ? Par exemple, un département avec seulement deux employés n’aura pas un employé gagnant le troisième salaire le plus élevé.
Voici mon approche à cette question:
Contents
Créer des tables de département et d’employés
Je crée une table qui inclut des champs tels que dept_id
et dept_name
.
CREATE TABLE department (
dept_id INT,
dept_name VARCHAR(60)
);
Maintenant, j’insère divers départements dans la nouvelle table.
INSERT INTO department (dept_id,dept_name)
VALUES (780,'HR');
INSERT INTO department (dept_id,dept_name)
VALUES (781,'Marketing');
INSERT INTO department (dept_id,dept_name)
VALUES (782,'Sales');
INSERT INTO department (dept_id,dept_name)
VALUES (783,'Web Dev');
Figure 1. Le tableau des départements (Mohammed Kamil Khan, CC BY-SA 4.0)
Ensuite, je crée une autre table incorporant les champs first_name
, last_name
, dept_id
et salary
.
CREATE TABLE employee (
first_name VARCHAR(100),
last_name VARCHAR(100),
dept_id INT,
salary INT
);
Ensuite, j’insère des valeurs dans le tableau :
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Sam','Burton',781,80000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Peter','Mellark',780,90000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Happy','Hogan',782,110000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Steve','Palmer',782,120000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Christopher','Walker',783,140000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Richard','Freeman',781,85000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Alex','Wilson',782,115000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Harry','Simmons',781,90000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Thomas','Henderson',780,95000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Ronald','Thompson',783,130000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('James','Martin',783,135000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Laurent','Fisher',780,100000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Tom','Brooks',780,85000);
INSERT INTO employee (first_name,last_name,dept_id,salary)
VALUES ('Tom','Bennington',783,140000);
Figure 2. Tableau des employés classés par ID de service (Mohammed Kamil Khan, CC BY-SA 4.0)
Je peux déduire le nombre d’employés dans chaque service à l’aide de ce tableau (ID de service : nombre d’employés) :
Si je veux voir les deuxièmes employés les mieux rémunérés de différents services, ainsi que le nom de leur service (en utilisant DENSE_RANK
), le tableau sera le suivant :
Figure 3. Le deuxième employé le mieux rémunéré de chaque département (Mohammed Kamil Khan, CC BY-SA 4.0)
Si j’applique la même requête pour trouver les quatrièmes employés les mieux rémunérés, le résultat ne sera que Tom Brooks du département 780 (RH), avec un salaire de 85 000 $.
Figure 4. Le quatrième employé le mieux rémunéré (Mohammed Kamil Khan, CC BY-SA 4.0)
Bien que le département 783 (Web Dev) compte quatre employés, deux (James Martin et Ronald Thompson) seront classés comme les troisièmes employés les mieux rémunérés de ce département, puisque les deux premiers salariés ont le même salaire.
Trouver le nième plus haut
Passons maintenant à la question principale : que se passe-t-il si je veux afficher le dept_ID
et dept_name
avec des valeurs nulles pour les champs liés aux employés pour les départements qui n’ont pas d’employé au nième salaire le plus élevé ?
Figure 5. Tous les départements répertoriés, qu’ils aient ou non un nième employé le mieux rémunéré (Mohammed Kamil Khan, CC BY-SA 4.0)
Le tableau affiché à la figure 5 est ce que je vise à obtenir lorsque des départements spécifiques n’ont pas d’employé le mieux rémunéré : les départements marketing, ventes et développement Web sont répertoriés, mais les champs de nom et de salaire contiennent une valeur nulle.
La requête ultime qui permet d’obtenir le tableau de la figure 5 est la suivante :
SELECT * FROM (WITH null1 AS (SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT NULL)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * FROM null1
UNION
SELECT * FROM nulled
ORDER BY dept_id)
B;
Répartition de la requête
Je vais décomposer la requête pour la rendre moins écrasante.
Utilisation DENSE_RANK()
pour afficher les informations sur les employés et le service (n’impliquant pas null pour l’absence du nième membre le plus rémunérateur):
SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4
Production:
Figure 6. Le quatrième revenu le plus élevé (Mohammed Kamil Khan, CC BY-SA 4.0)
Exclure le rank1
colonne du tableau de la figure 6, qui identifie un seul employé avec le quatrième salaire le plus élevé, même s’il y a quatre employés dans un autre service.
SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A
Production:
Figure 7. Tableau du quatrième revenu le plus élevé sans la colonne de rang 1 (Mohammed Kamil Khan, CC BY-SA 4.0)
Indiquez les départements du tableau des départements qui n’ont pas d’employé au nième salaire le plus élevé :
SELECT * FROM (WITH null1 AS (SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT NULL))
SELECT * FROM full1)B
Production:
Figure 8. Le tableau complet1 répertoriant les départements sans quatrième revenu le plus élevé (Mohammed Kamil Khan, CC BY-SA 4.0)
Remplacer full1
dans la dernière ligne du code ci-dessus avec null1
:
SELECT * FROM (WITH null1 AS (SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT NULL))
SELECT * FROM null1)B
Figure 9. Le tableau null1 répertoriant tous les départements, avec des valeurs nulles pour ceux qui n’ont pas de quatrième revenu le plus élevé (Mohammed Kamil Khan, CC BY-SA 4.0)
Maintenant, je remplis les valeurs nulles du dept_id
et dept_name
champs de la Figure 9 avec les valeurs correspondantes de la Figure 8.
SELECT * FROM (WITH null1 AS (SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT NULL)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * FROM nulled) B;
Figure 10. Le résultat de la requête annulée (Mohammed Kamil Khan, CC BY-SA 4.0)
La requête annulée utilise CASE WHEN
sur les nuls rencontrés dans dept_id
et dept_name
colonnes de la null1
tableau et les remplace par les valeurs correspondantes dans le full1
table. Maintenant, il ne me reste plus qu’à postuler UNION
aux tables obtenues dans la figure 7 et la figure 10. Cela peut être accompli en déclarant la dernière requête dans le code précédent en utilisant WITH
et alors UNION
– le dimensionner avec null1
.
SELECT * FROM (WITH null1 AS (SELECT A.dept_id, A.dept_name, A.first_name, A.last_name, A.salary
FROM (SELECT * FROM (
SELECT department.dept_id, department.dept_name, employee.first_name, employee.last_name,
employee.salary, DENSE_RANK() OVER (PARTITION BY employee.dept_id ORDER BY employee.salary DESC) AS Rank1
FROM employee INNER JOIN department
ON employee.dept_id=department.dept_id) AS k
WHERE rank1=4)A),
full1 AS (SELECT dept_id, dept_name FROM department WHERE dept_id NOT IN (SELECT dept_id FROM null1 WHERE dept_id IS NOT NULL)),
nulled AS(SELECT
CASE WHEN null1.dept_id IS NULL THEN full1.dept_id ELSE null1.dept_id END,
CASE WHEN null1.dept_name IS NULL THEN full1.dept_name ELSE null1.dept_name END,
first_name,last_name,salary
FROM null1 RIGHT JOIN full1 ON null1.dept_id=full1.dept_id)
SELECT * FROM null1
UNION
SELECT * FROM nulled
ORDER BY dept_id)
B;
Figure 11. Le résultat final (Mohammed Kamil Khan, CC BY-SA 4.0)
Maintenant, je peux déduire de la figure 11 que le marketing, les ventes et le développement Web sont les départements qui n’ont aucun employé gagnant le quatrième salaire le plus élevé.