Comment analyser les données par mois et par année en utilisant les fonctions d’agrégation SQL

Les fonctions d’agrégation SQL sont des outils puissants pour agréger et analyser les données. Cet article se concentre sur la manière d’analyser les données par mois et par année, en expliquant en détail les bases des fonctions d’agrégation ainsi que des exemples concrets de requêtes. Apprenez comment utiliser SQL pour agréger les données efficacement et obtenir des insights commerciaux.

Bases des fonctions d’agrégation

Les fonctions d’agrégation sont des fonctions qui permettent d’obtenir un résultat unique à partir de plusieurs lignes d’une base de données. Voici les fonctions d’agrégation les plus courantes :

SUM

Calcule la somme des données numériques.

AVG

Calcule la moyenne des données numériques.

COUNT

Compte le nombre de lignes.

MAX

Renvoie la valeur maximale d’une colonne spécifiée.

MIN

Renvoie la valeur minimale d’une colonne spécifiée.

Ces fonctions peuvent être utilisées avec la clause GROUP BY pour agréger les données par groupe spécifique. Voyons ensuite comment analyser les données par mois et par année.

Bases de l’analyse des données mensuelles

L’analyse des données mensuelles consiste à agréger et analyser les données par mois. En SQL, vous pouvez utiliser une colonne de type DATE pour grouper les données par mois et appliquer des fonctions d’agrégation.

Structure de base d’une requête SQL

Voici une requête SQL de base pour agréger des données mensuelles :

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(sales) AS monthly_sales
FROM 
    orders
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m');

Dans cette requête, la fonction DATE_FORMAT est utilisée pour convertir la date de la colonne order_date au format « année-mois », et agréger les ventes pour chaque mois.

Bases de l’analyse des données annuelles

L’analyse des données annuelles consiste à agréger et analyser les données par année. En SQL, vous pouvez utiliser une colonne de type DATE pour grouper les données par année et appliquer des fonctions d’agrégation.

Structure de base d’une requête SQL

Voici une requête SQL de base pour agréger des données annuelles :

SELECT 
    YEAR(order_date) AS year,
    SUM(sales) AS annual_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date);

Dans cette requête, la fonction YEAR est utilisée pour extraire l’année de la colonne order_date et agréger les ventes pour chaque année.

Exemples concrets de requêtes SQL (données de ventes)

Voici des exemples concrets de requêtes SQL pour agréger les données de ventes par mois et par année.

Agrégation des données de ventes mensuelles

Exemple de requête pour agréger les ventes par mois :

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(sales) AS monthly_sales
FROM 
    orders
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    month;

Dans cette requête, la fonction DATE_FORMAT est utilisée pour convertir la date au format « année-mois » et calculer le total des ventes pour chaque mois. Les résultats sont triés par mois.

Agrégation des données de ventes annuelles

Exemple de requête pour agréger les ventes par année :

SELECT 
    YEAR(order_date) AS year,
    SUM(sales) AS annual_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date)
ORDER BY 
    year;

Dans cette requête, la fonction YEAR est utilisée pour extraire l’année de la date et calculer le total des ventes pour chaque année. Les résultats sont triés par année.

Exemples concrets de requêtes SQL (données des utilisateurs)

Voici des exemples concrets de requêtes SQL pour agréger les données des utilisateurs par mois et par année.

Agrégation du nombre d’inscriptions mensuelles

Exemple de requête pour agréger le nombre d’inscriptions d’utilisateurs par mois :

SELECT 
    DATE_FORMAT(registration_date, '%Y-%m') AS month,
    COUNT(*) AS monthly_registrations
FROM 
    users
GROUP BY 
    DATE_FORMAT(registration_date, '%Y-%m')
ORDER BY 
    month;

Dans cette requête, la fonction DATE_FORMAT est utilisée pour convertir la date au format « année-mois » et compter le nombre d’inscriptions d’utilisateurs pour chaque mois. Les résultats sont triés par mois.

Agrégation du nombre d’inscriptions annuelles

Exemple de requête pour agréger le nombre d’inscriptions d’utilisateurs par année :

SELECT 
    YEAR(registration_date) AS year,
    COUNT(*) AS annual_registrations
FROM 
    users
GROUP BY 
    YEAR(registration_date)
ORDER BY 
    year;

Dans cette requête, la fonction YEAR est utilisée pour extraire l’année de la date et compter le nombre d’inscriptions d’utilisateurs pour chaque année. Les résultats sont triés par année.

Applications avancées de regroupement et de filtrage

Nous allons expliquer comment effectuer une analyse de données plus détaillée en utilisant les clauses GROUP BY et HAVING.

Applications avancées de GROUP BY

La clause GROUP BY peut être utilisée pour regrouper les données à l’aide de plusieurs colonnes. Par exemple, pour agréger les ventes par mois et par catégorie de produit, utilisez la requête suivante :

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    product_category,
    SUM(sales) AS monthly_sales
FROM 
    orders
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m'),
    product_category
ORDER BY 
    month,
    product_category;

Dans cette requête, les données sont regroupées par mois en fonction de la date de commande et les ventes sont agrégées par catégorie de produit.

Filtrage avec la clause HAVING

La clause HAVING est utilisée pour spécifier des conditions sur les résultats regroupés par la clause GROUP BY. Par exemple, pour filtrer les mois avec des ventes mensuelles dépassant un certain montant, utilisez la requête suivante :

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(sales) AS monthly_sales
FROM 
    orders
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
HAVING 
    monthly_sales > 100000
ORDER BY 
    month;

Dans cette requête, seuls les mois avec des ventes mensuelles supérieures à 100 000 yens sont inclus dans les résultats.

Exemple avancé : Ventes annuelles d’une catégorie de produit spécifique

Exemple de requête pour agréger les ventes annuelles d’une catégorie de produit spécifique, et n’afficher que les années avec des ventes annuelles supérieures à 50 000 yens :

SELECT 
    YEAR(order_date) AS year,
    product_category,
    SUM(sales) AS annual_sales
FROM 
    orders
WHERE 
    product_category = 'Specific Category'
GROUP BY 
    YEAR(order_date),
    product_category
HAVING 
    annual_sales > 50000
ORDER BY 
    year;

Dans cette requête, les commandes de la catégorie de produit « Specific Category » sont regroupées par année, et seules les années avec des ventes annuelles supérieures à 50 000 yens sont filtrées et affichées.

Optimisation des performances

Nous expliquons ici des techniques et des bonnes pratiques pour améliorer les performances des requêtes d’agrégation.

Utilisation des index

L’utilisation d’index peut considérablement améliorer la vitesse d’exécution des requêtes. En particulier, il est efficace de créer des index sur les colonnes utilisées dans les fonctions d’agrégation, ainsi que sur les colonnes fréquemment utilisées dans les clauses GROUP BY ou WHERE.

CREATE INDEX idx_order_date ON orders(order_date);

Cette requête crée un index sur la colonne order_date, ce qui accélère les recherches et les agrégations basées sur la date de commande.

Optimiser la rédaction des requêtes

Vous pouvez améliorer les performances en structurant judicieusement vos requêtes. Par exemple, éviter l’utilisation de sous-requêtes et sélectionner uniquement les colonnes nécessaires sont des pratiques efficaces.

Exemple de requête inefficace

SELECT 
    YEAR(order_date) AS year,
    (SELECT SUM(sales) FROM orders WHERE YEAR(order_date) = year) AS annual_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date);

Cette requête est inefficace. Il est plus rapide de procéder à l’agrégation directement, sans utiliser de sous-requêtes.

Exemple de requête efficace

SELECT 
    YEAR(order_date) AS year,
    SUM(sales) AS annual_sales
FROM 
    orders
GROUP BY 
    YEAR(order_date);

Cette requête s’exécute plus rapidement car elle effectue l’agrégation directement.

Normalisation et dénormalisation des données

Il est essentiel de bien normaliser vos données lors de la conception de la base de données. Cependant, pour les requêtes analytiques en lecture seule, la dénormalisation peut parfois améliorer les performances. La dénormalisation consiste à tolérer la redondance des données pour éviter les jointures.

Utilisation des partitions

Pour les tables volumineuses, l’utilisation de partitions peut améliorer les performances. La partition est une méthode permettant de diviser logiquement une table et de distribuer les données en fonction de conditions spécifiques.

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    sales DECIMAL(10, 2),
    product_category VARCHAR(50),
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

Dans cette requête, la table est partitionnée par année en fonction de la date de commande. Cela permet d’améliorer les performances des requêtes pour une année spécifique.

Conclusion

L’analyse des données par mois et par année est essentielle pour obtenir des insights commerciaux importants. L’utilisation des fonctions d’agrégation SQL permet d’agréger les données de manière simple et efficace. Utilisez des exemples de requêtes concrets et des techniques avancées pour réaliser une analyse de données efficace. En optimisant l’utilisation des index et la rédaction des requêtes, vous pouvez également améliorer considérablement les performances. Utilisez SQL pour soutenir la prise de décision basée sur les données.