Créer des graphiques d'histogramme avec MySQL – Tutoriel

Pour créer une requête statistique afin de créer un histogramme avec MySQL, vous pouvez utiliser la fonction COUNT() avec GROUP BY pour compter les occurrences de valeurs dans une plage ou une catégorie spécifiée créée par la contrainte de regroupement.

En particulier pour les données de séries chronologiques, il existe de nombreux cas d'utilisation des histogrammes, comme la surveillance du nombre d'utilisateurs enregistrés à intervalle quotidien au cours des 30 derniers jours. Nous utilisons ces requêtes dans notre backend d'administration pour surveiller certains KPI importants.

Malheureusement, les bases de données SQL n'ont jamais eu de support natif pour représenter les histogrammes, mais elles font partie des graphiques les plus utilisés pour suivre des métriques de toutes sortes.

Dans cet article, je vais vous montrer comment créer des requêtes à cet effet et surmonter certaines limitations. Supposons que vous ayez une table nommée sales avec une colonne nommée sale_date contenant la date et l'heure de chaque vente. Vous souhaitez créer un histogramme montrant la répartition des ventes sur des intervalles mensuels.

SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
    COUNT(*) AS count
FROM 
    sales
GROUP BY 
    DATE_FORMAT(sale_date, '%Y-%m');

dans cette requête :

• DATE_FORMAT(sale_date, '%Y-%m') formate la colonne sale_date au format année-mois (par exemple, "2024-01" pour janvier 2024).
• COUNT(*) compte le nombre d'occurrences de ventes au cours de chaque mois.
• FROM sales spécifie la table à partir de laquelle récupérer les données.
• GROUP BY DATE_FORMAT(sale_date, '%Y-%m') regroupe les données de ventes en intervalles mensuels en fonction des dates de vente formatées.

Cette requête vous donnera le nombre d'occurrences de ventes au sein de chaque intervalle mensuel, vous permettant de créer un histogramme pour visualiser la répartition des ventes dans le temps.

Comment combler les lacunes dans la requête d'histogramme
En exécutant cette requête, vous verrez probablement des mois manquants dans l'ensemble de résultats, probablement parce que certains mois, vous n'avez aucune vente, la fonction GROUP BY ne peut donc pas générer de données pour ces intervalles.

Comment la requête peut-elle être ajustée pour combler ces lacunes avec une valeur nulle par défaut ? Sinon l'histogramme reste incomplet.

Pour combler les lacunes dans l'ensemble de résultats et garantir que tous les mois sont représentés, même s'il n'y a pas de ventes certains mois, vous pouvez générer dynamiquement les mois pour l'intervalle de calendrier sélectionné à l'aide d'une expression de table commune récursive (CTE).

Il génère une séquence de dates couvrant la plage horaire souhaitée. Ensuite, vous pouvez JOINDRE À GAUCHE cette séquence de dates avec vos données de ventes pour inclure tous les mois dans l'ensemble de résultats.

WITH RECURSIVE DateRange AS (
    SELECT 
        DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 11 MONTH), '%Y-%m-01') AS min_date,
        DATE_FORMAT(NOW(), '%Y-%m-01') AS max_date
    UNION ALL
    SELECT 
        DATE_FORMAT(DATE_ADD(min_date, INTERVAL 1 MONTH), '%Y-%m-01'),
        max_date
    FROM 
        DateRange
    WHERE 
        DATE_ADD(min_date, INTERVAL 1 MONTH) <= max_date
)
SELECT 
    DATE_FORMAT(DateRange.min_date, '%Y-%m') AS sales_month,
    COUNT(sales.sale_date) AS count
FROM 
    DateRange
LEFT JOIN 
    sales ON DATE_FORMAT(DateRange.min_date, '%Y-%m') = DATE_FORMAT(sales.sale_date, '%Y-%m')
GROUP BY 
    sales_month
ORDER BY 
    sales_month;

Le DateRange CTE (expression de table commune) génère de manière récursive une séquence de mois couvrant la plage de temps entre les dates de vente minimales et maximales dans votre table de ventes.

Cette requête génère dynamiquement les mois pour l'intervalle de calendrier sélectionné en fonction des dates de vente minimales et maximales dans votre table de ventes, garantissant que tous les mois sont représentés dans l'ensemble de résultats.

Comblez les lacunes avec du code

La solution SQL peut être un peu moins confortable pour les développeurs, et également le besoin de plus de personnalisations peut conduire à préférer une solution basée sur le code.

Dans ce cas, vous pouvez obtenir le même résultat en trois étapes simples :

• Créez le tableau avec l'intervalle de temps de vos intérêts ;
• Obtenir le résultat de la requête GROUP BY ;
• Fusionnez-les.

Voici l'extrait de code utilisant Laravel et la bibliothèque Carbon :

$dates = [];

// Create the array with the time interval of your interests
for(
    $day = now()->subDays(31);
    $day->startOfDay()->lte(now());
    $day->addDay()
) {
    $dates[] = [
        'day' => $day->format('Y-m-d'),
        'total' => 0,
    ];
}

// Get the result from the GROUP BY query
$sales = $product->sales()->select(DB::raw('DATE(sale_at) as day, CAST(SUM(qty) AS UNSIGNED) as total'))
    ->where('sale_at', '>=', now()->subDays(31))
    ->groupBy('day')
    ->get();

// Merge them
return array_map(function ($date) use ($sales) {
    foreach ($sales as $sale) {
        if ($date['day'] === $sale['day']) {
            return $sale;
        }
    }
}, $dates);

Si vous souhaitez lire plus d'articles sur la base de données, vous pouvez consulter les articles ci-dessous :

• Comment faire évoluer une base de données SQL
• Comment dupliquer une table MySQL
• Résolu - Violation de la contrainte d'intégrité
• Économisez 1,2 million de requêtes par jour avec Laravel Eager Loading
• Comment accélérer les performances des applications avec des requêtes SQL intelligentes
• Rendre votre application évolutive en optimisant les performances ORM
• Résolu – Délai d'attente du verrouillage MySQL dépassé à l'aide des files d'attente et des tâches Laravel

Surveillez votre application PHP gratuitement

Inspector est un outil de surveillance de l'exécution de code spécialement conçu pour les développeurs de logiciels. Vous n'avez pas besoin d'installer quoi que ce soit sur l'infrastructure cloud, installez simplement le package Laravel et vous êtes prêt à partir.

Inspector est très facile à utiliser et ne nécessite aucune configuration.

Si vous recherchez une surveillance HTTP, des informations sur les requêtes et la possibilité de transférer des alertes et des notifications vers votre environnement de messagerie préféré, essayez Inspector gratuitement. [Enregistrez votre compte+(https://app.inspector.dev/register).

Ou apprenez-en plus sur le site : https://inspector.dev

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!