Analyse des kumulativen Aggregationsprinzips von MySQL und Anwendungsbeispiele

Dieser Artikel veranschaulicht anhand von Beispielen die Prinzipien und die Verwendung der kumulativen MySQL-Aggregation. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Bei der kumulativen Aggregation werden Daten vom ersten Element in der Sequenz bis zum aktuellen Element aggregiert. Beispielsweise werden für jeden Mitarbeiter die kumulierte Anzahl von Bestellungen und die durchschnittliche Anzahl von Bestellungen vom Beginn jedes Monats bis heute zurückgegeben.

Für das Zeilennummernproblem gibt es zwei Lösungen: eine besteht in der Verwendung einer Unterabfrage und die andere in der Verwendung eines Join. Die Unterabfragemethode ist normalerweise intuitiver und lesbarer. Wenn jedoch eine Aggregation erforderlich ist, muss die Unterabfrage die Daten für jede Aggregation einmal scannen, während die Verbindungsmethode normalerweise nur einmal scannen muss, um das Ergebnis zu erhalten. Die folgende Abfrage verwendet einen Join, um das Ergebnis zu erhalten

WÄHLEN
 a.empid,
 a.Bestellmonat, a.Menge AS dieser Monat,
 SUM(b.qty) AS Gesamtsumme,
 CAST(AVG(Menge) AS DECIMAL(5,2)) AS Durchschnitt
VON emordert ein
INNER JOIN emorders b
  EIN a.empid=b.empid
  UND b.Bestellmonat <= a.Bestellmonat
GRUPPE NACH a.empid,a.ordermonth,a.qty
BESTELLEN NACH a.empid,a.ordermonth

Wenn Sie nur die kumulierten Bestellungen im Jahr 2015 abfragen möchten, können Sie die Where-Bedingung hinzufügen

WO DATE_FORMAT(a.Bestellmonat,'%Y')='2015' UND DATE_FORMAT(b.Bestellmonat,'%Y')='2015'

Die Ergebnisse sind wie folgt

Möglicherweise möchten Sie die Daten auch filtern, um beispielsweise nur die monatlichen Bestellungen jedes Mitarbeiters anzuzeigen, bis ein bestimmtes Ziel erreicht ist. Dabei gehen wir davon aus, dass die Gesamtzahl der Bestellungen jedes Mitarbeiters gezählt wird, bevor sie 1.000 erreicht.

Hier können wir den HAVING-Filter verwenden, um die Abfrage zu vervollständigen

WÄHLEN
 a.empid,
 a.Bestellmonat,a.Menge AS dieser Monat,
 SUM(b.qty) AS Gesamtsumme,
 CAST(AVG(Menge) AS DECIMAL(5,2)) AS Durchschnitt
VON emordert ein
INNER JOIN emorders b
  EIN a.empid=b.empid
  UND b.Bestellmonat <= a.Bestellmonat
WO DATE_FORMAT(a.Bestellmonat,'%Y')='2015' UND DATE_FORMAT(b.Bestellmonat,'%Y')='2015'
GRUPPE NACH a.empid,a.ordermonth,a.qty
HABEN insgesamt < 1000
BESTELLEN NACH a.empid,a.ordermonth

Die Situation in dem Monat, in dem die Zahl 1000 erreicht, wird hier nicht berücksichtigt. Wenn wir Statistiken erstellen möchten, wird die Situation etwas komplizierter. Wenn „Total <= 1000“ angegeben ist, werden Statistiken nur dann erstellt, wenn die Anzahl der Bestellungen für diesen Monat genau 1000 beträgt. Andernfalls werden für diesen Monat keine Statistiken erstellt. Daher kann die Filterung dieses Problems aus einem anderen Blickwinkel betrachtet werden. Wenn die kumulierte Bestellmenge weniger als 1000 beträgt, ist die Differenz zwischen der kumulierten Bestellmenge und den Bestellungen des Vormonats kleiner als 1000. Dabei kann auch der erste Monat mit einer Bestellmenge über 1000 gezählt werden. Daher lautet die SQL-Anweisung für diese Lösung wie folgt

WÄHLEN
 a.empid,
 a.Bestellmonat,a.Menge AS dieser Monat,
 SUM(b.qty) AS Gesamtsumme,
 CAST(AVG(Menge) AS DECIMAL(5,2)) AS Durchschnitt
VON emordert ein
INNER JOIN emorders b
  EIN a.empid=b.empid
  UND b.Bestellmonat <= a.Bestellmonat
WO DATE_FORMAT(a.Bestellmonat,'%Y')='2015' UND DATE_FORMAT(b.Bestellmonat,'%Y')='2015'
GRUPPE NACH a.empid,a.ordermonth,a.qty
HABEN Gesamtmenge < 1000
BESTELLEN NACH a.empid,a.ordermonth

Die Ergebnisse sind wie folgt

Wenn Sie nur die Daten für den Monat mit einer kumulierten Bestellzahl von 1000 und nicht für die vorherigen Monate zurückgeben möchten, können Sie die obige SQL-Anweisung ändern.

Filtern Sie weiter und fügen Sie die Bedingung hinzu, dass die kumulierte Bestellmenge größer oder gleich 1000 ist. Die SQL-Anweisung für dieses Problem lautet wie folgt:

WÄHLEN
 a.empid,
 a.Bestellmonat,a.Menge AS dieser Monat,
 SUM(b.qty) AS Gesamtsumme,
 CAST(AVG(Menge) AS DECIMAL(5,2)) AS Durchschnitt
VON emordert ein
INNER JOIN emorders b
  EIN a.empid=b.empid
  UND b.Bestellmonat <= a.Bestellmonat
WO DATE_FORMAT(a.Bestellmonat,'%Y')='2015' UND DATE_FORMAT(b.Bestellmonat,'%Y')='2015'
GRUPPE NACH a.empid,a.ordermonth,a.qty
HABEN total-a.qty < 1000 UND total >= 1000
BESTELLEN NACH a.empid,a.ordermonth

Die Ergebnisse sind wie folgt

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „MySQL-Protokolloperationskenntnisse“, „Zusammenfassung der MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“ und „Zusammenfassung der MySQL-Datenbanksperrenkenntnisse“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.