Erstellen Sie Histogrammdiagramme mit MySQL – Tutorial

Um eine statistische Abfrage zum Erstellen eines Histogrammdiagramms mit MySQL zu erstellen, können Sie die Funktion COUNT() zusammen mit GROUP BY verwenden, um das Vorkommen von Werten innerhalb eines angegebenen Bereichs oder einer bestimmten Kategorie zu zählen, die durch die Gruppierungsbeschränkung erstellt wurde.

Insbesondere für Zeitreihendaten gibt es viele Anwendungsfälle für Histogramme wie die Überwachung der Anzahl der registrierten Benutzer in einem täglichen Intervall in den letzten 30 Tagen. Wir nutzen diese Abfragen in unserem Administrations-Backend, um einige wichtige KPIs zu überwachen.

Leider hatten SQL-Datenbanken nie native Unterstützung für die Darstellung von Histogrammen, aber sie gehören zu den am häufigsten verwendeten Diagrammen zur Verfolgung von Metriken aller Art.

In diesem Artikel zeige ich Ihnen, wie Sie für diesen Zweck Abfragen erstellen und einige Einschränkungen überwinden. Nehmen wir an, Sie haben eine Tabelle namens „sales“ mit einer Spalte namens „sale_date“, die das Datum und die Uhrzeit jedes Verkaufs enthält. Sie möchten ein Histogramm erstellen, das die Verteilung der Verkäufe über monatliche Intervalle zeigt.

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

n dieser Abfrage:

• DATE_FORMAT(sale_date, '%Y-%m') formatiert die Spalte sale_date in das Jahr-Monat-Format (z. B. „2024-01“ für Januar 2024).
• COUNT(*) zählt die Anzahl der Verkaufsereignisse innerhalb jedes Monats.
• FROM sales gibt die Tabelle an, aus der die Daten abgerufen werden sollen.
• GROUP BY DATE_FORMAT(sale_date, '%Y-%m') gruppiert die Verkaufsdaten basierend auf den formatierten Verkaufsdaten in monatliche Intervalle.

Diese Abfrage liefert Ihnen die Anzahl der Verkaufsereignisse innerhalb jedes Monatsintervalls und ermöglicht Ihnen die Erstellung eines Histogrammdiagramms, um die Verteilung der Verkäufe im Zeitverlauf zu visualisieren.

So füllen Sie Lücken in der Histogrammabfrage
Wenn Sie diese Abfrage ausführen, werden Sie wahrscheinlich einige fehlende Monate in der Ergebnismenge sehen, wahrscheinlich weil Sie in bestimmten Monaten keine Verkäufe haben, sodass die GROUP BY-Funktion für diese Intervalle keine Daten generieren kann.

Wie kann die Abfrage angepasst werden, um diese Lücken mit einem Standardwert von Null zu füllen? Ansonsten bleibt das Histogramm unvollständig.

Um die Lücken im Ergebnissatz zu schließen und sicherzustellen, dass alle Monate dargestellt werden, auch wenn in bestimmten Monaten keine Verkäufe stattfinden, können Sie die Monate für das ausgewählte Kalenderintervall mithilfe eines rekursiven allgemeinen Tabellenausdrucks (CTE) dynamisch generieren.

Es generiert eine Folge von Daten, die den gewünschten Zeitraum abdecken. Anschließend können Sie diese Datumsfolge mit Ihren Verkaufsdaten LINKS VERBINDEN, um alle Monate in die Ergebnismenge einzubeziehen.

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;

Der DateRange CTE (gemeinsamer Tabellenausdruck) generiert rekursiv eine Folge von Monaten, die den Zeitbereich zwischen dem minimalen und maximalen Verkaufsdatum in Ihrer Verkaufstabelle abdeckt.

Diese Abfrage generiert dynamisch die Monate für das ausgewählte Kalenderintervall basierend auf den minimalen und maximalen Verkaufsterminen in Ihrer Verkaufstabelle und stellt sicher, dass alle Monate im Ergebnissatz dargestellt werden.

Füllen Sie die Lücken mit Code

Die SQL-Lösung ist für Entwickler möglicherweise etwas weniger komfortabel, und auch der Bedarf an weiteren Anpassungen kann dazu führen, dass eine codebasierte Lösung bevorzugt wird.

In diesem Fall können Sie in drei einfachen Schritten das gleiche Ergebnis erzielen:

• Erstellen Sie das Array mit dem Zeitintervall Ihrer Interessen;
• Erhalten Sie das Ergebnis der GROUP BY-Abfrage;
• Füge sie zusammen.

Hier ist der Codeausschnitt mit Laravel und der Carbon-Bibliothek:

$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);

Wenn Sie daran interessiert sind, weitere Beiträge zum Thema Datenbank zu lesen, können Sie sich die folgenden Artikel ansehen:

• So skalieren Sie eine SQL-Datenbank
• So duplizieren Sie eine MySQL-Tabelle
• Gelöst – Verletzung der Integritätsbeschränkung
• Sparen Sie 1,2 Millionen Abfragen pro Tag mit Laravel Eager Loading
• So beschleunigen Sie die Anwendungsleistung mit intelligenten SQL-Abfragen
• Machen Sie Ihre Anwendung skalierbar und optimieren Sie die ORM-Leistung
• Behoben – Wartezeitüberschreitung bei MySQL-Sperre bei Verwendung von Laravel-Warteschlangen und -Jobs

Überwachen Sie Ihre PHP-Anwendung kostenlos

Inspector ist ein Tool zur Codeausführungsüberwachung, das speziell für Softwareentwickler entwickelt wurde. Sie müssen nichts in der Cloud-Infrastruktur installieren, installieren Sie einfach das Laravel-Paket und schon kann es losgehen.

Inspector ist super einfach zu bedienen und erfordert keine Konfigurationen.

Wenn Sie auf der Suche nach HTTP-Überwachung, Abfrageeinblicken und der Möglichkeit sind, Warnungen und Benachrichtigungen an Ihre bevorzugte Messaging-Umgebung weiterzuleiten, testen Sie Inspector kostenlos. [Registrieren Sie Ihr Konto+(https://app.inspector.dev/register).

Oder erfahren Sie mehr auf der Website: https://inspector.dev

Das obige ist der detaillierte Inhalt vonErstellen Sie Histogrammdiagramme mit MySQL – Tutorial. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!