Localisation du contenu des groupes de valeur la plus élevée dans SQL

Code SQL (Structured Query Language) sur écran d'ordinateur et arrière-plan de la salle des serveurs. Exemple de code SQL pour interroger les données d'une base de données. — Code SQL (Structured Query Language) sur écran d’ordinateur et arrière-plan de la salle des serveurs. Exemple de code SQL pour interroger les données d’une base de données.

Très souvent, en parcourant SQL pour se préparer aux entretiens, on se pose la question de trouver l’employé avec le salaire le plus élevé ou le 2e plus élevé en joignant une table contenant des informations sur les employés à une autre contenant des informations sur le service. Cela soulève la question : qu’en est-il de trouver l’employé qui gagne le nième salaire le plus élevé ? par département?

Il peut également y avoir un scénario dans lequel un ou plusieurs départements particuliers ne contiennent pas la nième valeur la plus élevée, par exemple, un département avec seulement deux employés n’aura pas d’employé gagnant le 3ème salaire le plus élevé. Cet article tend à mettre l’accent sur un tel scénario.

Commençons donc par créer deux tables :

1. département

Cette table comprend des champs tels que : dept_id et dept_name.

CREATE TABLE department (
    dept_id int,
    dept_name varchar(60)
);

Insérons 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');

2. Employé

Cette table intègre les champs : prénom, nom, service_id et salaire.

CREATE TABLE employee (
    first_name varchar(100),
    last_name varchar(100),
    dept_id int,
    salary int
);

Insertion de 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);

Fig 2. Table des employés triée par dept_id

Ce qui suit est déduit de la table des employés :

Department_ID :Nombre d’employés :

780:4

781:3

782:3

783:4

Pour comprendre ce que cet article vise à réaliser, observons la sortie souhaitée pour différentes entrées.

Considérant que nous voulons les informations des 2èmes employés les mieux rémunérés de différents départements avec le nom de leur département (en considérant l’utilisation de DENSE_RANK), la sortie sera la suivante :

Fig 3. Informations sur les 2e employés les mieux rémunérés par département

Si nous appliquons la même requête pour trouver les 4èmes employés les mieux rémunérés, voici le résultat :

Notez que bien que le département 783 compte quatre employés, il y en a deux qui seront classés comme les 3èmes employés les mieux rémunérés de ce département. Passons maintenant à la question principale : que se passe-t-il si nous voulions afficher le dept_ID et le dept_name avec des valeurs nulles pour les champs liés aux employés lorsque le ou les départements ont/n’ont pas un nième employé le mieux rémunéré ? Pour cela, le résultat représenté sur la Fig. 4 est modifié comme suit :

Le tableau affiché sur la Fig. 5, c’est ce que l’on visera à obtenir lorsque certains départements n’auront pas un nième salarié le mieux rémunéré.

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;

Cela peut sembler écrasant, mais décomposons-le étape par étape :

(i) Utilisation de DENSE_RANK() pour afficher les informations sur les employés et le service (n’impliquant pas de valeur nulle pour l’absence du nième membre le mieux rémunéré) :

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:

(ii) En excluant la colonne ‘rank1’ du tableau de la figure 6 :

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:

(iii) Indiquer les départements du tableau des départements qui n’ont pas un nième employé le mieux rémunéré :

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:

Remplacez ‘full1’ dans la dernière ligne du code ci-dessus par ‘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, nous devons remplir les valeurs nulles de dept_id et dept_name de la table de la Fig. 9 avec les valeurs correspondantes dans le tableau de la Fig. 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;

Fig 10. Le résultat de la requête ‘nulled’

La requête ‘nulled’ utilise CASE WHEN sur les valeurs nulles rencontrées dans les colonnes dept_id et dept_name de la table ‘null1’ pour les remplacer par les valeurs correspondantes dans la table ‘full1’. Maintenant, tout ce que nous avons à faire est d’appliquer UNION sur les tables obtenues à la Fig. 7 et Fig. 10. Cela peut être accompli en déclarant la dernière requête dans le code précédent à l’aide de WITH puis en l’UNIONisant 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;

On peut déduire de la Fig. 11 que ‘Marketing’, ‘Ventes’ et ‘Web Dev’ sont les départements sans employé gagnant le 4e salaire le plus élevé.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Leave a Comment Cancel reply