Introduction
Dans ce laboratoire, vous explorerez la puissance des fonctions de fenêtre MySQL. Vous apprendrez à effectuer des calculs sur des ensembles de lignes de table qui sont liées à la ligne actuelle.
Vous commencerez par créer un ensemble de données d'exemple, puis utiliserez des fonctions telles que ROW_NUMBER(), SUM(), AVG() et LAG() combinées avec la clause OVER() pour effectuer une analyse de données avancée. Ce laboratoire fournit des exemples pratiques, étape par étape, pour vous aider à comprendre et à appliquer ces fonctions puissantes.
Configurer la Base de Données et la Table
Avant d'utiliser les fonctions de fenêtre, vous avez besoin d'une base de données et d'une table avec des données d'exemple. Dans cette étape, vous allez créer une base de données nommée company et une table nommée employees.
Tout d'abord, ouvrez le terminal depuis votre bureau.
Connectez-vous au serveur MySQL en tant qu'utilisateur root. Comme il s'agit d'un environnement de laboratoire, vous pouvez utiliser sudo pour vous connecter sans mot de passe.
sudo mysql -u root
Une fois connecté, vous verrez l'invite MySQL (mysql>).
Maintenant, créez la base de données company et basculez vers celle-ci. La clause IF NOT EXISTS empêche une erreur si la base de données existe déjà.
CREATE DATABASE IF NOT EXISTS company;
USE company;
Ensuite, créez la table employees. Cette table stockera l'ID de l'employé, le nom, le département et le salaire.
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
Maintenant, insérez des données d'exemple dans la table employees.
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
employee_name = VALUES(employee_name),
department = VALUES(department),
salary = VALUES(salary);
Pour vérifier que les données ont été insérées correctement, vous pouvez afficher toutes les lignes de la table employees.
SELECT * FROM employees;
La sortie devrait afficher les cinq enregistrements que vous avez insérés :
+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary |
+-------------+---------------+------------+----------+
| 1 | Alice | Sales | 60000.00 |
| 2 | Bob | Marketing | 55000.00 |
| 3 | Charlie | Sales | 65000.00 |
| 4 | David | IT | 70000.00 |
| 5 | Eve | Marketing | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)
Avec la base de données et la table prêtes, vous pouvez passer à l'étape suivante pour en savoir plus sur les fonctions de fenêtre.
Classer les Lignes avec ROW_NUMBER()
La fonction ROW_NUMBER() attribue un entier séquentiel unique à chaque ligne au sein d'une partition d'un ensemble de résultats. Elle est couramment utilisée pour le classement et la pagination.
La syntaxe de base est :
ROW_NUMBER() OVER (ORDER BY nom_colonne [ASC|DESC])
OVER(): Cette clause définit la fenêtre (l'ensemble des lignes) pour la fonction.ORDER BY: Cette clause, à l'intérieur deOVER(), spécifie l'ordre dans lequel les numéros de ligne sont attribués.
En continuant dans le shell MySQL, vous allez maintenant utiliser ROW_NUMBER() pour classer les employés par leur salaire dans l'ordre décroissant.
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;
Cette requête ajoute une colonne salary_rank, numérotant les employés du salaire le plus élevé au plus bas.
+---------------+------------+----------+-------------+
| employee_name | department | salary | salary_rank |
+---------------+------------+----------+-------------+
| David | IT | 70000.00 | 1 |
| Charlie | Sales | 65000.00 | 2 |
| Eve | Marketing | 62000.00 | 3 |
| Alice | Sales | 60000.00 | 4 |
| Bob | Marketing | 55000.00 | 5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)
Comme vous pouvez le constater, David a un rang de 1 car il a le salaire le plus élevé. Cela démontre comment ROW_NUMBER() peut être utilisé pour créer un classement simple.
Calculer un Total Cumulé avec SUM()
Un total courant, ou somme cumulative, est la somme d'une séquence de nombres qui est mise à jour à mesure que chaque nouveau nombre est ajouté. En SQL, vous pouvez le calculer en utilisant SUM() OVER().
La syntaxe est :
SUM(nom_colonne) OVER (ORDER BY nom_colonne [ASC|DESC])
Cette fonction additionne les valeurs d'une colonne dans l'ordre spécifié par la clause ORDER BY.
Maintenant, calculons le total courant des salaires, classés par employee_id.
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
employees;
Le résultat affichera le salaire de chaque employé et la somme cumulative jusqu'à cette ligne.
+---------------+----------+---------------+
| employee_name | salary | running_total |
+---------------+----------+---------------+
| Alice | 60000.00 | 60000.00 |
| Bob | 55000.00 | 115000.00 |
| Charlie | 65000.00 | 180000.00 |
| David | 70000.00 | 250000.00 |
| Eve | 62000.00 | 312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)
Par exemple, le running_total pour Bob est la somme de son salaire et de celui d'Alice (60000.00 + 55000.00 = 115000.00). Ceci est utile pour suivre des métriques cumulatives comme les ventes ou les dépenses au fil du temps.
Regrouper les Calculs avec PARTITION BY
La clause PARTITION BY divise l'ensemble des résultats en partitions (groupes) et applique la fonction de fenêtre à chaque partition indépendamment. Ceci est utile pour effectuer des calculs au sein de catégories spécifiques.
La syntaxe est :
function() OVER (PARTITION BY nom_colonne ORDER BY ...)
Utilisons PARTITION BY pour classer les employés au sein de chaque département en fonction de leur salaire.
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
employees;
Cette requête partitionne les données par department et classe ensuite les employés de chaque département par salaire.
+---------------+------------+----------+--------------+
| employee_name | department | salary | rank_in_dept |
+---------------+------------+----------+--------------+
| David | IT | 70000.00 | 1 |
| Eve | Marketing | 62000.00 | 1 |
| Bob | Marketing | 55000.00 | 2 |
| Charlie | Sales | 65000.00 | 1 |
| Alice | Sales | 60000.00 | 2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)
Remarquez que le classement redémarre pour chaque département. Par exemple, Eve et Charlie sont tous deux classés 1, mais au sein de leurs départements respectifs 'Marketing' et 'Sales'. Cela permet une analyse plus granulaire par rapport à un classement global.
Comparer les Lignes avec LAG()
La fonction LAG() donne accès à une ligne située à un décalage physique spécifié avant la ligne courante. Elle est utile pour comparer une valeur de la ligne courante avec une valeur d'une ligne précédente.
La syntaxe est :
LAG(expression, décalage, valeur_par_défaut) OVER (ORDER BY ...)
expression: La colonne ou l'expression à récupérer.décalage: Le nombre de lignes à remonter (par défaut, c'est 1).valeur_par_défaut: La valeur à retourner si le décalage est hors limites (par exemple, pour la première ligne).
Trouvons le salaire de l'employé précédent dans la liste, classé par employee_id.
SELECT
employee_name,
salary,
LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
employees;
Cette requête récupère le salaire de la ligne précédente. Pour la première ligne, où il n'y a pas de ligne précédente, elle retourne NULL.
+---------------+----------+-----------------+
| employee_name | salary | previous_salary |
+---------------+----------+-----------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | 60000.00 |
| Charlie | 65000.00 | 55000.00 |
| David | 70000.00 | 65000.00 |
| Eve | 62000.00 | 70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)
Vous pouvez l'utiliser pour calculer la différence entre les salaires consécutifs. Lorsque le salaire précédent est NULL (pour la première ligne), le résultat sera également NULL.
SELECT
employee_name,
salary,
salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
employees;
Cette requête calcule la différence entre le salaire de l'employé actuel et celui du précédent.
+---------------+----------+-------------+
| employee_name | salary | salary_diff |
+---------------+----------+-------------+
| Alice | 60000.00 | NULL |
| Bob | 55000.00 | -5000.00 |
| Charlie | 65000.00 | 10000.00 |
| David | 70000.00 | 5000.00 |
| Eve | 62000.00 | -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)
Vous avez maintenant pratiqué plusieurs fonctions de fenêtre clés. Vous pouvez quitter le shell MySQL.
exit;
Résumé
Dans ce laboratoire, vous avez exploré l'utilisation des fonctions de fenêtre MySQL. Vous avez appris à attribuer des numéros de ligne avec ROW_NUMBER(), à calculer des totaux cumulés avec SUM() OVER(), à effectuer des calculs sur des groupes spécifiques avec PARTITION BY, et à accéder aux données des lignes précédentes avec LAG().
En appliquant ces fonctions à un ensemble de données d'exemple, vous avez acquis une expérience pratique dans la réalisation d'analyses de données avancées directement dans vos requêtes SQL. Ce sont des compétences précieuses pour générer des rapports complexes et des informations à partir de vos données.
