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:

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');

Ensuite, je crée une autre table incorporant les champs first_name, last_name, dept_idet 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);

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 :

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 $.

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é ?

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:

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:

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:

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

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;

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;

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é.