Les 6 requêtes SQL à connaitre pour analyser vos données RH

Le langage de requête structuré (SQL) est un outil permettant de gérer et d'interroger des données dans des bases de données relationnelles. Les requêtes SQL avancées font passer la manipulation et l'analyse des données à un niveau supérieur. Dans cet article, nous allons explorer le monde des requêtes SQL en nous concentrant sur les six requêtes essentielles. Nous découvrirons non seulement la syntaxe de ces requêtes, mais aussi des cas concrets d'application pour illustrer leur utilité en utilisant l'outil Microsoft SQL Server Management Studio (SSMS).

Description du jeu de données utilisé

Afin d'illustrer l'utilité de ces analyses, des données RH fictives ont été crée contenant les colonnes suivantes: 

• EmployeeID: Identifiant unique de l'employé.
• FirstName: Prénom de l'employé.
• LastName: Nom de famille de l'employé.
• DepartmentID: Identifiant du département de l'employé.
• HireDate: Date d'embauche de l'employé.
• Salary: Salaire de l'employé.
• JobTitle: Intitulé du poste occupé par l'employé.
• ManagerID: Identifiant du supérieur hiérarchique de l'employé.
• BirthDate: Date de naissance de l'employé.
• Email: Adresse email de l'employé.

1) Regroupement des données par Manager (Group By)

SELECT ManagerID, COUNT(*) AS DirectReports
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
ORDER BY DirectReports DESC;

Cette requête SQL permet d'identifier les managers qui ont le plus d'employés sous leur responsabilité dans la table Employees. Elle sélectionne d'abord l'identifiant du manager et utilise la fonction COUNT(*) pour compter le nombre d'employés rattachés à chaque manager. Ces informations sont ensuite extraites de la table Employees en ne gardant que les lignes où l'identifiant du manager est renseigné (pour se concentrer uniquement sur les managers). Enfin, le résultat est regroupé par manager avec la fontion Group By et trié par ordre décroissant du nombre de subordonnés directs avec la fonction Order By, affichant ainsi en premier les managers qui encadrent le plus d'employés.

2) Accélérer la récupération des données dans les bases de données (Index)

CREATE INDEX idx_hire_date ON Employees (HireDate);
SELECT *
FROM Employees
WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';

 

 

 

La première partie du code, CREATE INDEX idx_hire_date ON Employees (HireDate), crée un index sur la colonne HireDate de la table Employees. Un index agit comme une table de référence spéciale qui accélère considérablement la récupération des données dans les bases de données.

La deuxième partie du code, récupère tous les enregistrements d'employés dont la HireDate se situe entre le 1er janvier 2023 et le 1er janvier 2024. C'est dans cette requête que l'index créé précédemment est utilisé.

En disposant d'un index sur HireDate, la base de données peut localiser efficacement les entrées pertinentes sans avoir besoin d'examiner chaque ligne de la table Employees. Cela améliore considérablement les performances de la requête, surtout lorsqu'il s'agit de grands jeux de données.

 

3) Identifier les employés qui gagnent plus que le salaire moyen de leur département avec l’utilisation des sous-requêtes (SubQuery)

 

SELECT e.EmployeeID, FirstName + ' ' + LastName AS FullName, Salary
FROM Employees e
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);

La requête principale sélectionne les informations sur les employés et utilise une sous-requête pour calculer le salaire moyen de chaque département. Le résultat de la sous-requête est ensuite utilisé pour filtrer les employés, ne gardant que ceux dont le salaire est supérieur à la moyenne de leur département. Cette approche permet de comparer dynamiquement les salaires individuels au contexte du salaire moyen par département, facilitant l'identification des employés les mieux payés.

La sous-requête joue un rôle clé car elle permet de calculer dynamiquement le salaire moyen par département et de l'utiliser ensuite comme critère de comparaison pour identifier les employés les mieux payés au sein de leur département. Sans sous-requête, il faudrait écrire une requête distincte pour chaque département, ce qui serait fastidieux et peu efficace.

4) Identifier les managers dont le salaire est supérieur au salaire moyen de leur département avec l’utilisation des expressions de table communes (CTE)

WITH DepartmentAverages AS (
  SELECT DepartmentID, AVG(Salary) AS DepartmentAverageSalary
  FROM Employees
  GROUP BY DepartmentID
)
SELECT d.DepartmentID, e.ManagerID, FirstName + ' ' + LastName AS ManagerName, e.Salary
FROM Employees e
INNER JOIN DepartmentAverages d ON e.DepartmentID = d.DepartmentID
WHERE ManagerID IS NOT NULL
  AND e.Salary > d.DepartmentAverageSalary;

Une table commune appelée DepartmentAverages fonctionne comme une mini-requête temporaire, en calculant d'abord le salaire moyen par département. Par la suite, la demande principale fait appel à la CTE afin d'associer les données sur les employés (département, Manager, prénom, nom et salaire) aux moyennes calculées par département stockées dans la CTE. En filtrant les Managers et en comparant leur salaire avec le salaire moyen de leur département, la demande détermine les personnes les plus rémunérées au sein de leur équipe. Dans cette situation, l'emploi d'une CTE permet d'améliorer la lisibilité du code en regroupant le calcul des moyennes et offre la possibilité de réutiliser cette logique dans d'autres parties de la requête.

5) Afficher la structure hiérarchique complète des employés et de leurs managers dans l'entreprise avec l’utilisation des expressions de table communes récursives

WITH RecursiveCTE AS (
  SELECT EmployeeID, FirstName, LastName, ManagerID
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL  SELECT e.EmployeeID, e.FirstName,e.LastName,  e.ManagerID
  FROM Employees e
  JOIN RecursiveCTE rc ON e.ManagerID = rc.EmployeeID
)
SELECT * FROM RecursiveCTE;

D'abord, on initialise la CTE en sélectionnant les dirigeants (employés sans manager). Ensuite, on utilise la récursivité pour explorer la hiérarchie niveau par niveau. À chaque itération, on relie les employés à leur manager identifié dans la CTE précédente. Finalement, on récupère l'intégralité de la CTE finale, affichant ainsi l'arborescence complète des employés et de leurs responsables. En résumé, les CTE récursives sont idéales pour traiter les structures hiérarchiques complexes

6) Calculer le salaire moyen par département pour chaque employé avec l’utilisation de la fonction de fenêtre (Window Functions)

SELECT
EmployeeID,
FirstName,
LastName,
Salary,
AVG(Salary) OVER (PARTITION BY DepartmentID) AS avg_department_salary
FROM Employees;

On sélectionne d'abord les informations de base sur les employés (identifiant, prénom, nom et salaire). Ensuite, la clause clé AVG(Salary) OVER (PARTITION BY DepartmentID) agit comme une fenêtre de calcul. Elle effectue la moyenne (AVG) du salaire séparément pour chaque département (grâce à la partition par DepartmentID). Ce résultat est ensuite ajouté comme un nouveau champ avg_department_salary pour chaque employé. Ainsi, on obtient à la fois le salaire individuel et le contexte du salaire moyen par département.

En résumé, la fonction de fenêtre permet d'effectuer des calculs d'agrégation (moyenne ici) sur des groupes de lignes définis par une clause de partitionnement (par département dans ce cas). Cela permet d'enrichir les données initiales en ajoutant du contexte (salaire moyen par département) et d'analyser plus facilement les disparités salariales au sein de l'entreprise.

Conclusion

Les requêtes SQL complexes vous permettent de gérer des tâches d'analyse de données complexes et de résoudre des problèmes complexes de manipulation de données. En traitant ces demandes, vous serez mieux préparé à gérer diverses tâches liées aux données et à prendre des décisions plus éclairées en fonction de vos données.

Au fur et à mesure que vous acquerrez de l'expérience avec SQL et les bases de données relationnelles, l'utilisation régulière de ces requêtes avancées vous aidera à développer votre expertise et à améliorer vos compétences en analyse de données.