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


  • FrançaisFrançais


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

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

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

    Source

    La Rédaction

    L'équipe rédactionnnelle du site

    Pour contacter personnellement le taulier :

    Laisser un commentaire

    Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

    Copy code