Agrégation et regroupement de données MySQL

Introduction

Dans ce laboratoire (lab), nous explorerons l'agrégation et le regroupement de données dans MySQL, des compétences essentielles pour analyser et synthétiser les données dans les bases de données. Vous apprendrez à utiliser les fonctions d'agrégation pour effectuer des calculs sur plusieurs lignes, à regrouper les données en fonction des valeurs de colonnes et à filtrer les résultats regroupés. Ces techniques sont fondamentales pour générer des rapports, analyser les tendances et extraire des informations significatives à partir de vos données. Grâce à des exercices pratiques, vous acquerrez une expérience concrète de ces opérations cruciales sur les bases de données et comprendrez comment analyser efficacement les données dans MySQL.

Skills Graph

Fonctions d'agrégation de base

Dans cette étape, nous explorerons les fonctions d'agrégation fondamentales dans MySQL. Ces fonctions effectuent des calculs sur plusieurs lignes et renvoient une seule valeur, ce qui les rend essentielles pour l'analyse de données.

Tout d'abord, connectons-nous à MySQL et sélectionnons notre base de données :

sudo mysql -u root

Une fois connecté :

USE sales_db;

Fonction COUNT

Commençons par la fonction COUNT, qui compte le nombre de lignes dans un ensemble de résultats :

-- Compter le nombre total de ventes
SELECT COUNT(*) as total_sales
FROM sales;

Vous devriez voir une sortie comme celle-ci :

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT peut être utilisé de différentes manières :

-- Compter les produits uniques vendus
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- Compter les ventes par catégorie
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

Explication :

• COUNT(*) compte toutes les lignes de la table, y compris les valeurs NULL
• COUNT(DISTINCT column) compte seulement les valeurs uniques dans la colonne spécifiée
• Lorsqu'il est utilisé avec GROUP BY, COUNT calcule les totaux pour chaque groupe séparément
• Le mot-clé as crée des alias pour les colonnes de résultat, rendant la sortie plus lisible

Fonction SUM

La fonction SUM calcule le total des colonnes numériques :

-- Calculer la quantité totale vendue
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- Calculer le revenu total
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

Explication :

• SUM ne peut être utilisé qu'avec des colonnes numériques
• Des calculs comme quantity * unit_price sont effectués avant l'agrégation
• ROUND(x, 2) arrondit les nombres à 2 décimales
• Pour les calculs de revenu, la multiplication se fait avant la sommation pour maintenir la précision

Fonction AVG

La fonction AVG calcule la valeur moyenne :

-- Calculer le prix unitaire moyen
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- Calculer la quantité moyenne par vente
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

Explication :

• AVG ignore automatiquement les valeurs NULL dans ses calculs
• Le deuxième paramètre de ROUND spécifie le nombre de décimales
• Les résultats sont automatiquement convertis en type DECIMAL pour plus de précision
• AVG est couramment utilisé pour trouver les valeurs typiques dans vos données

Fonctions MAX et MIN

Ces fonctions trouvent les valeurs les plus élevées et les plus basses :

-- Trouver la plage de prix des produits
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- Trouver les dates de la première et de la dernière vente
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

Explication :

• MIN/MAX fonctionnent avec des nombres, des chaînes de caractères et des dates
• Pour les dates, MIN trouve la date la plus ancienne tandis que MAX trouve la date la plus récente
• Plusieurs fonctions d'agrégation peuvent être combinées dans une seule instruction SELECT
• Comme les autres fonctions d'agrégation, elles ignorent automatiquement les valeurs NULL
• Ces fonctions sont utiles pour trouver les plages de valeurs et les limites dans vos données

Regroupement de données avec GROUP BY

Dans cette étape, nous apprendrons à regrouper les données en utilisant la clause GROUP BY. Le regroupement nous permet d'effectuer des calculs d'agrégation sur des sous-ensembles de nos données en fonction de valeurs de colonnes spécifiques.

Regroupement de base

Commençons par des opérations de regroupement simples :

-- Nombre de ventes et quantité totale par catégorie
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

Cette requête montre combien de ventes et quelle quantité totale ont été vendues dans chaque catégorie. Vous devriez voir une sortie comme celle-ci :

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

Explication :

• GROUP BY regroupe les lignes ayant la même catégorie en une seule ligne
• L'instruction ORDER BY total_quantity DESC trie les résultats par quantité décroissante
• Chaque fonction d'agrégation (COUNT, SUM) opère indépendamment au sein de chaque groupe
• Les colonnes non agrégées dans la clause SELECT doivent apparaître dans la clause GROUP BY

Regroupement par plusieurs colonnes

Nous pouvons regrouper par plusieurs colonnes pour obtenir des informations plus détaillées :

-- Analyse des ventes par catégorie et région
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

Explication :

• Le regroupement par plusieurs colonnes crée des sous-groupes pour chaque combinaison unique
• L'ordre des colonnes dans la clause GROUP BY affecte la manière dont les données sont regroupées
• Les résultats sont triés d'abord par catégorie, puis par revenu total au sein de chaque catégorie
• Cette approche permet d'identifier les régions les plus performantes au sein de chaque catégorie

Regroupement basé sur les dates

MySQL propose des fonctions pour extraire des parties de dates, utiles pour le regroupement basé sur le temps :

-- Résumé des ventes quotidiennes
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explication :

• Regroupe toutes les transactions qui ont eu lieu le même jour
• L'instruction ORDER BY sale_date arrange les résultats chronologiquement
• Compte les transactions par jour et calcule les totaux quotidiens
• Utile pour identifier les modèles et les tendances des ventes quotidiennes
• Peut être modifié pour regrouper par mois ou par année en utilisant des fonctions DATE

Filtrer les données regroupées avec HAVING

Dans cette étape, nous allons apprendre à utiliser la clause HAVING, qui nous permet de filtrer les résultats après le regroupement. Alors que la clause WHERE filtre les lignes individuelles avant le regroupement, la clause HAVING filtre les groupes eux-mêmes.

Utilisation de base de HAVING

Trouvons les catégories pour lesquelles le nombre total d'articles vendus est supérieur à 15 :

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

Cela affiche seulement les catégories pour lesquelles le nombre total d'articles dépasse 15 :

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

Explication :

• La clause HAVING filtre les groupes après l'application de la clause GROUP BY
• Vous pouvez faire référence aux résultats des fonctions d'agrégation dans la clause HAVING
• La condition de filtre utilise l'alias 'total_quantity'
• Les catégories avec 15 articles ou moins sont exclues des résultats

Combinaison de WHERE et HAVING

Nous pouvons utiliser les clauses WHERE et HAVING ensemble. La clause WHERE filtre les lignes avant le regroupement, tandis que la clause HAVING filtre après le regroupement :

-- Trouver les catégories à haut volume dans la région Nord
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

Explication :

• La clause WHERE filtre les lignes individuelles (region = 'North') avant le regroupement
• Ensuite, les données sont regroupées par catégorie
• Enfin, la clause HAVING filtre les résultats regroupés (total_quantity > 5)
• L'ordre est important : FROM → WHERE → GROUP BY → HAVING → SELECT

Conditions HAVING complexes

Nous pouvons utiliser plusieurs conditions dans la clause HAVING :

-- Trouver les catégories avec un haut volume de ventes et un haut revenu
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

Explication :

• Plusieurs conditions peuvent être combinées à l'aide de AND/OR
• Tous les calculs d'agrégation sont disponibles pour les conditions HAVING
• Vous pouvez utiliser des opérations arithmétiques dans la clause HAVING
• La clause ORDER BY est appliquée après l'évaluation des filtres HAVING
• Ce type de requête est utile pour identifier les catégories les plus performantes

Techniques d'agrégation avancées

Dans cette étape finale, nous explorerons des techniques d'agrégation plus avancées en combinant tout ce que nous avons appris. Nous allons créer des rapports de ventes complets qui démontrent le pouvoir de ces outils ensemble.

Tableau de bord des performances des ventes

Créons une analyse complète des ventes :

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Explication :

• Crée un aperçu complet des performances des catégories
• Utilise DISTINCT pour compter les produits uniques au sein de chaque catégorie
• Combine plusieurs fonctions d'agrégation pour une analyse complète
• Affiche les plages de prix avec MIN et MAX
• Calcule les quantités moyennes et le revenu total
• Les résultats sont triés par revenu pour mettre en évidence les meilleures performances

Analyse des performances régionales

Analysons les performances des ventes par région :

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

Explication :

• Regroupe les données de ventes par régions géographiques
• Montre la diversité des produits avec des comptes DISTINCT
• Calcule les indicateurs de revenu total et de quantité
• Le prix moyen par unité (avg_price_per_unit) est calculé en divisant le revenu par la quantité
• La clause HAVING filtre les régions avec un revenu inférieur
• Aide à identifier les régions les plus et les moins performantes

Analyse des tendances quotidiennes

Créons un rapport sur les tendances quotidiennes des ventes :

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explication :

• Suit les indicateurs de performance des ventes quotidiens
• Montre la diversité des produits et des catégories par jour
• Compte les transactions et les quantités quotidiennes
• Calcule le revenu quotidien et la valeur moyenne des transactions
• Le tri chronologique aide à identifier les tendances
• Utile pour détecter les modèles et les effets saisonniers
• Peut aider à la planification des stocks et aux décisions d'effectif

Résumé

Dans ce laboratoire (LabEx), nous avons couvert les aspects essentiels de l'agrégation et du regroupement de données dans MySQL :

1. Utilisation des fonctions d'agrégation de base (COUNT, SUM, AVG, MAX, MIN) pour résumer les données
2. Regroupement des données à l'aide de la clause GROUP BY pour analyser les modèles et les tendances
3. Filtrage des résultats regroupés à l'aide de la clause HAVING
4. Combinaison de plusieurs techniques pour créer des analyses de données complètes

Ces compétences sont fondamentales pour l'analyse et la génération de rapports de données dans MySQL. Comprendre comment aggréger et regrouper efficacement les données vous permet d'extraire des informations significatives de vos bases de données et de créer des rapports d'entreprise précieux.