MENU
  1. Accueil
  2. SQL
  3. Comment agréger des données complexes à l’aide des tables de jointure SQL et de GROUP BY

Comment agréger des données complexes à l’aide des tables de jointure SQL et de GROUP BY

SQL

Dans les opérations de base de données utilisant SQL, il est courant de récupérer et d’agréger des données liées provenant de plusieurs tables. Surtout pour une analyse complexe sur de grands ensembles de données, tirer parti des tables de jointure et de GROUP BY est essentiel. Cet article fournit une explication détaillée des méthodes d’agrégation de données en utilisant ces fonctionnalités SQL, des concepts de base à la création pratique de requêtes et aux techniques d’optimisation.

Sommaire

Notions de base sur les tables de jointure

Les tables de jointure sont des fonctionnalités SQL utilisées pour combiner plusieurs tables afin de récupérer des données liées. Voici les principaux types de jointures et comment les utiliser.

INNER JOIN

INNER JOIN ne renvoie que les données communes aux deux tables. Il est principalement utilisé pour obtenir des enregistrements correspondants.

SELECT A.column1, B.column2
FROM TableA A
INNER JOIN TableB B ON A.id = B.id;

LEFT JOIN

LEFT JOIN renvoie toutes les données de la table de gauche et les données correspondantes de la table de droite. S’il n’y a pas de données correspondantes à droite, NULL est renvoyé.

SELECT A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id;

RIGHT JOIN

RIGHT JOIN renvoie toutes les données de la table de droite et les données correspondantes de la table de gauche. S’il n’y a pas de données correspondantes à gauche, NULL est renvoyé.

SELECT A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;

FULL JOIN

FULL JOIN renvoie toutes les données des deux tables et remplit les NULL pour les correspondances manquantes. Il est utilisé lorsque vous souhaitez inclure toutes les données des deux tables.

SELECT A.column1, B.column2<br>FROM TableA A<br>FULL JOIN TableB B ON A.id = B.id;

Notions de base sur GROUP BY

GROUP BY est une fonctionnalité SQL utilisée pour grouper des données en fonction de colonnes spécifiées et renvoyer des résultats pour chaque groupe en utilisant des fonctions d’agrégation. Il est principalement utilisé lorsque vous souhaitez agréger des données par catégories spécifiques.

Syntaxe de base de GROUP BY

La syntaxe de base de GROUP BY est la suivante.

SELECT column, AGGREGATE_FUNCTION(column)
FROM Table
GROUP BY column;

Ici, AGGREGATE_FUNCTION peut être SUM, AVG, COUNT, MAX, MIN, etc.

Exemples

Par exemple, une requête pour trouver le salaire moyen pour chaque département serait la suivante.

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Cette requête calcule le salaire moyen pour chaque département à partir de la table des employés.

Regroupement par plusieurs colonnes

Les données peuvent également être regroupées par plusieurs colonnes.

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

Cette requête compte le nombre d’employés par département et titre de poste.

Exemples d’agrégation de données complexes

En combinant des tables de jointure et GROUP BY, les données de plusieurs tables peuvent être agrégées pour effectuer une analyse plus complexe. Voici des exemples concrets d’agrégation de données liées aux employés et à leurs départements.

Agrégation de données provenant de plusieurs tables

Par exemple, pour agréger le nombre d’employés et le salaire moyen pour chaque département, vous pouvez utiliser la requête suivante.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Cette requête joint les tables des départements et des employés en utilisant INNER JOIN et agrège le nombre d’employés et le salaire moyen pour chaque département.

Utilisation de plusieurs fonctions d’agrégation

En outre, diverses informations statistiques peuvent être obtenues en combinant plusieurs fonctions d’agrégation.

SELECT d.department_name, 
       COUNT(e.employee_id) AS num_employees, 
       AVG(e.salary) AS avg_salary, 
       MAX(e.salary) AS max_salary, 
       MIN(e.salary) AS min_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Cette requête agrège le nombre d’employés, le salaire moyen, le salaire maximum et le salaire minimum pour chaque département à la fois.

Agrégation conditionnelle

Il est également possible de filtrer les données avec des conditions avant d’effectuer l’agrégation.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > 50000
GROUP BY d.department_name;

Cette requête agrège le nombre d’employés et le salaire moyen pour chaque département, en ne tenant compte que des employés ayant un salaire supérieur à 50 000.

Comme vous pouvez le voir, en combinant des tables de jointure et GROUP BY, il est possible de récupérer des données de plusieurs tables et d’analyser des données sous divers angles.

Création de requêtes pratiques

Ici, nous introduisons comment créer des requêtes complexes basées sur des scénarios commerciaux réels. Par exemple, considérez l’agrégation des ventes mensuelles pour chaque magasin à partir d’une base de données de ventes.

Scénario : Agrégation des ventes mensuelles pour chaque magasin

Dans ce scénario, les tables incluent les données suivantes :

  • stores table: contient des informations sur chaque magasin
  • orders table: contient des informations sur les commandes

Tout d’abord, créez une requête pour agréger les ventes mensuelles pour chaque magasin.

SELECT s.store_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

Les points clés de cette requête sont les suivants :

  • Utiliser INNER JOIN pour joindre les tables stores et orders
  • Utiliser la fonction DATE_FORMAT pour formater order_date par mois
  • Utiliser la fonction SUM pour calculer les ventes totales pour chaque mois
  • Grouper les données par nom de magasin et mois en utilisant GROUP BY
  • Trier les résultats par nom de magasin et mois en utilisant ORDER BY

Scénario : Agrégation des ventes mensuelles par catégorie de produits

Ensuite, considérez un scénario d’agrégation des ventes mensuelles par catégorie de produits. Les tables incluent :

  • products table: contient des informations sur chaque produit
  • categories table: contient des informations sur les catégories de chaque produit
  • order_items table: contient des détails sur les commandes

La requête est la suivante :

SELECT c.category_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(oi.quantity * p.price) AS total_sales
FROM categories c
INNER JOIN products p ON c.category_id = p.category_id
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_name, month
ORDER BY c.category_name, month;

Les points clés de cette requête sont les suivants :

  • Utiliser plusieurs INNER JOIN pour joindre les tables des catégories, des produits, des commandes et des articles de commande
  • Calculer les ventes pour chaque produit en utilisant quantity * price et agréger avec la fonction SUM
  • Grouper les données par nom de catégorie et mois en utilisant GROUP BY
  • Trier les résultats par nom de catégorie et mois en utilisant ORDER BY

Comme montré dans ces exemples, créer des requêtes basées sur des scénarios commerciaux réels nécessite de comprendre les relations entre les tables et de combiner les jointures appropriées et les fonctions d’agrégation.

Techniques d’optimisation

Pour améliorer les performances des requêtes complexes, il est important d’utiliser des techniques d’optimisation appropriées. Voici quelques méthodes pour améliorer la vitesse d’exécution des requêtes.

Utilisation des index

L’utilisation appropriée des index peut considérablement améliorer la vitesse de recherche de la base de données. Créez des index sur les colonnes utilisées dans les jointures et les conditions de recherche.

CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

Dans cet exemple, des index sont créés sur les colonnes department_id et order_date. Cela accélère les jointures et les conditions de recherche en utilisant ces index.

Utilisation des sous-requêtes

L’utilisation de sous-requêtes pour prétraiter les données peut améliorer l’efficacité de la requête principale. Cela est particulièrement efficace lorsque vous traitez de grandes quantités de données.

SELECT department_name, num_employees, avg_salary
FROM (
  SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
  FROM departments d
  INNER JOIN employees e ON d.department_id = e.department_id
  GROUP BY d.department_name
) sub;

Dans cet exemple, la sous-requête calcule le nombre d’employés et le salaire moyen pour chaque département, et la requête principale utilise ces résultats.

Optimisation de l’ordre des jointures

Optimiser l’ordre des jointures de tables peut améliorer les performances des requêtes. Joindre d’abord à partir de la plus petite table est efficace.

EXPLAIN SELECT s.store_name, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

Utilisez EXPLAIN pour vérifier le plan de la requête et vous assurer que le moteur de base de données utilise l’ordre de jointure optimal.

Utilisation appropriée des fonctions d’agrégation

L’utilisation appropriée des fonctions d’agrégation peut améliorer les performances des requêtes. Par exemple, n’utilisez que les fonctions d’agrégation minimales nécessaires pour éviter une agrégation redondante.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Dans cet exemple, seules les fonctions d’agrégation COUNT et AVG sont utilisées pour obtenir les informations nécessaires.

Utilisation des vues

Utiliser des vues peut simplifier les requêtes complexes et créer des requêtes réutilisables. Définir une vue améliore la lisibilité des requêtes.

CREATE VIEW department_summary AS
SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Dans cet exemple, une vue est créée pour calculer le nombre d’employés et le salaire moyen pour chaque département. En utilisant la vue, vous pouvez facilement référencer les résultats agrégés plus tard.

Conclusion

L’agrégation de données en utilisant des tables de jointure et GROUP BY est très utile pour une analyse de données complexe. En comprenant les types de jointures appropriés et l’utilisation de GROUP BY, et en utilisant des techniques d’optimisation des requêtes, une agrégation de données efficace et efficiente devient possible. Utilisez ces techniques pour maximiser les performances de votre base de données.

SQL
SQL LEFT JOIN INNER JOIN GROUP BY Optimisation de Requêtes Opérations de Base de Données RIGHT JOIN Aggrégation Conditionnelle Ordre des Joins Sous-requêtes FULL JOIN Tables Multiples Aggrégation de Données Indexes Tables Join
Sommaire