MySQL Bedingte Aggregation: Anwendungsfallerklärung zur Implementierung des Zustands und der Zählung von Feldern

In diesem Artikel wird eingehalten, wie man Fallanweisungen verwendet, um eine bedingte Aggregation in MySQL durchzuführen, um eine bedingte Summierung und Zählung bestimmter Felder zu erreichen. In einem praktischen Abonnement -System -Fall zeigt es, wie die Gesamtdauer und Anzahl der Ereignisse dynamisch auf der Grundlage des Datensatzstatus (z. B. "Ende" und "Abbrechen") berechnet werden kann, wodurch die Einschränkungen herkömmlicher Summenfunktionen überwunden werden, die den Anforderungen der komplexen bedingten Aggregation nicht erfüllen können. Das Tutorial analysiert die Anwendung von Fallanweisungen in Summenfunktionen im Detail und betont die Bedeutung von Koaleszen, wenn es sich um die möglichen Nullwerte des linken Join befasst.

Beherrschen der bedingten Aggregation in MySQL: Die Kombination aus Summe und Fallanweisungen

In Datenbankabfragen müssen wir häufig Daten basierend auf bestimmten Bedingungen zusammenfassen, z. B. die Berechnung der Summe oder der Anzahl der Datensätze, die einer bestimmten Bedingung erfüllen. Die Standard -Summe () oder count () kann nur alle Datensätze zusammenfassen, die den Bedingungen der WHERE -Klausel erfüllen. Wenn wir jedoch eine Mehrfachaggregation auf der Grundlage unterschiedlicher Bedingungen in derselben Abfrage durchführen müssen oder nur numerische Werte einbeziehen, die während der Aggregation bestimmte Bedingungen erfüllen. Dies erfordert eine fortgeschrittenere Fähigkeit - bedingte Aggregation. In MySQL ist die Kombination von Fallanweisungen und aggregierten Funktionen ein leistungsstarkes Instrument, um dieses Ziel zu erreichen.

Szenario Beispiel: Statistik der Arbeitnehmerbuchungszeit

Angenommen, wir haben ein Buchungssystem, das zwei Tabellen enthält: Mitarbeiter und Buchung.

Staff Table Struktur:

Personal	First_Name	Last_name
1	John	Damhirschkuh
2	Maria	Damhirschkuh

Struktur der Buchungstabelle:

Buchung	Personal	Status	Dauer
1	1	abgesagt	20
2	1	beendet	20
3	1	beendet	10
4	2	abgesagt	30
5	1	bestätigt	40

Unsere Ziele sind:

1. Berechnen Sie die Gesamtbuchungsdauer für den Status "Ended" jedes Mitarbeiters.
2. Zählen Sie gleichzeitig die Anzahl der Buchungen für jeden Mitarbeiter mit dem Status "Storniert".

Einschränkungen traditioneller Methoden

Wenn wir nur eine einfache Summe (Buchung) verwenden, erhalten wir in allen Zuständen die Gesamtdauer und können nicht zwischen bestimmten Zuständen wie "Ende" oder "Abbrechen" unterscheiden. Beispielsweise berechnet die folgende Abfrage die Gesamtdauer aller Zustände:

 WÄHLEN
    s.Staffid,
    s.First_name,
    s.last_name,
    Summe (b.duration) als Gesamtdauer
AUS
    Mitarbeiter s
Links Join
    Buchung B auf s.Staffid = B.Staffid
Gruppe von
    s.staffid, s.first_name, s.last_name;

Dadurch wird die Gesamtdauer von John Doe auf (20 20 10 10 40) = 90 anstelle von (20 10) = 30 nur im "Ende".

Anwendungsfallerklärung zur Implementierung der bedingten Aggregation

Die Fallanweisung ermöglicht es uns, die bedingte Logik in der Funktion SUM () zu definieren. Wenn die Bedingung erfüllt ist, schließen wir den entsprechenden Wert ein; Andernfalls liefern wir einen Wert, der die Summe nicht beeinflusst (normalerweise 0).

Lösung SQL Abfrage:

 WÄHLEN
    s.Staffid,
    s.First_name,
    s.last_name,
    - Berechnen Sie die Gesamtabonnementdauer der Status-Summe "End" (Fall
        Wenn b.Status = 'endete', dann b.duration
        Sonst 0
    Ende) wie beendet, dass bookingsduration,
    - Statistiken Die Anzahl der Buchungen in der "stornierten" Statuskoales (sum
AUS
    Mitarbeiter s
Links Join
    Buchung B auf s.Staffid = B.Staffid
Gruppe von
    s.staffid, s.first_name, s.last_name
Bestellen von
    s.Staffid;

Abfrageergebnis Beispiel:

Personal	First_Name	Last_name	Beendete bookingsduration	CancelledbookingsCount
1	John	Damhirschkuh	30	1
2	Maria	Damhirschkuh	0	1

Detaillierte Lösung

1. Klausel auswählen :

   • s.staffid, s.first_name, s.last_name: Grundlegende Informationen zur Auswahl von Mitarbeitern.
   • Summe (Fall, wenn b.Status = 'beendet', dann b.duration sonst 0 Ende) wie endenBookingsDuration: Dies ist der Schlüssel zur Implementierung der bedingten Summierung.
     • Fall, wenn b.Status = 'beendet', dann b.duration sonst 0 Ende: Für jeden Buchungssatz wird sein Status "beendet", dann wird sein Dauerwert genommen. Ansonsten wird 0 genommen.
     • Summe (...): Fasst alle von der Fallerklärung zurückgegebenen Werte zusammen. Auf diese Weise wird nur die Dauer im "End" -Zustand angesammelt, und die Dauer in anderen Zuständen wird durch 0 ersetzt, was die Summe nicht beeinträchtigt.
   • Koalesce (Summe (b.Status = 'storniert'), 0) als CancelledBookingsCount: Dies ist eine Fähigkeit, um eine bedingte Zählung zu implementieren.
     • b.Status = 'storniert': In MySQL wird ein boolescher Ausdruck in einem numerischen Kontext als 1 (wahr) oder 0 (falsch) behandelt. Wenn also der Status "abgebrochen" wird, ist das Ausdrucksergebnis 1; sonst ist es 0.
     • Summe (...): Fassen Sie diese 1s und 0s zusammen, und das Ergebnis ist die Anzahl der Datensätze im Status "Storniert".
     • Koalesce (..., 0): Die linke Verbindung kann dazu führen, dass einige Mitarbeiter in der Buchungstabelle keine übereinstimmenden Datensätze haben. In diesem Fall gibt die Funktion sum () null zurück. Die Koalesce -Funktion wird verwendet, um den Nullwert durch 0 zu ersetzen, um die Genauigkeit und Lesbarkeit des Ergebnisses zu gewährleisten.

2. Von und links Join Clausses :

   • Die Mitarbeiter sind links bei der Buchung B auf s.StAffid = b.Staffid: Verwenden Sie links, um sicherzustellen, dass einige Mitarbeiter weiterhin in den Ergebnissen erscheinen, selbst wenn sie keine Buchungsunterlagen haben. Wenn Sie innere Verbindung verwenden, werden nur Mitarbeiter mit Buchungsunterlagen angezeigt.

3. Gruppe nach Klausel :

   • Gruppe von s.staffid, s.first_name, s.last_name: Gruppen nach Mitarbeiter -ID und Name, um unabhängige aggregierte Ergebnisse für jeden Mitarbeiter zu berechnen.

Notizen und Best Practices

• Flexibilität von Fallanweisungen : Die Fallanweisung ist sehr flexibel und kann mehrere enthalten, wenn ... dann Verzweigungen und ein optionaler anderer Zweig für eine komplexere bedingtere Logik.
• Wichtigkeit von anderer Klausel : In Summe (Fall ...) ist sonst 0 Standardpraxis, da sie die Summe nicht beeinflusst. Wenn die else -Klausel weggelassen wird, werden Datensätze, die die Bedingungen nicht erfüllen, Null zurückgegeben, und die Funktion sum () ignoriert den Nullwert, was zu unerwarteten Ergebnissen führen kann (z. B. wenn alle Datensätze die Bedingungen erfüllen, kann die Sumn anstelle von 0 null sein).
• Koalesce Processing NULL : Bei der Aggregation mit dem linken Join kann die Aggregationsfunktion (z. B. Summe, Anzahl) Null zurückgeben, wenn die Datensätze in der linken Tabelle in der rechten Tabelle keine Übereinstimmungen aufweisen. Diese Nullwerte können unter Verwendung von Koalesce (Aggregate_Function_Result, 0) in 0 konvertiert werden, um das Ergebnis wie erwartet mehr zu gestalten.
• Leistungsüberlegungen : Fallanweisungen werden in der Aggregatfunktion ausgeführt und sind in der Regel effizienter. Für sehr große Datensätze ist es jedoch wichtig sicherzustellen, dass die Verbindungsbedingung und die Klausel (falls vorhanden) den Index effektiv nutzen kann.

Zusammenfassen

Durch Einbettung von Fallanweisungen in Aggregatfunktionen wie SUM () können wir leistungsstarke bedingte Aggregationsfunktionen implementieren, um mehrere Statistiken zu berechnen, die unterschiedliche Bedingungen gleichzeitig in einer Abfrage erfüllen. Diese Methode verbessert nicht nur die Effizienz der Abfrage, sondern macht den SQL -Code auch prägnanter und einfacher. Wenn Sie diese Technik beherrschen, verbessert es Ihre Fähigkeit, komplexe Datenanalyseaufgaben in MySQL zu erledigen, erheblich.

Das obige ist der detaillierte Inhalt vonMySQL Bedingte Aggregation: Anwendungsfallerklärung zur Implementierung des Zustands und der Zählung von Feldern. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!