MySQL-Gruppierungsabfragen und Aggregatfunktionen

Überblick

Ich glaube, dass wir häufig auf solche Szenarien stoßen: Wir möchten wissen, wie viel Leute durchschnittlich ausgeben, wenn sie auf Tmall auf Double Eleven Kosmetika kaufen (das kann dabei helfen, die Preisspanne der Waren zu bestimmen), oder wie hoch der Anteil des Kosmetikkonsums in verschiedenen Altersgruppen ist (das kann dabei helfen, den Warenbestand abzuschätzen).

Zu diesem Zeitpunkt ist eine Gruppenabfrage erforderlich. Der Zweck der Gruppenabfrage besteht darin, die Daten in mehrere logische Gruppen aufzuteilen (Personen, die Kosmetika kaufen, sind eine Gruppe, und Personen unterschiedlicher Altersgruppen, die Kosmetika kaufen, sind ebenfalls Gruppen) und für jede Gruppe Aggregationsberechnungen durchzuführen:.

Das Syntaxformat der Gruppenabfrage ist wie folgt:

 wähle cname, group_fun, ... aus tname [wobei Bedingung]
 Gruppieren nach Gruppenausdruck [mit Gruppenbedingung];

Zur Erklärung:

1. group_fun stellt eine Aggregatfunktion dar, die sich auf eine Funktion bezieht, die Aggregatberechnungen an gruppierten Daten durchführt.

2. group_expression stellt den Gruppierungsausdruck dar. Mehrere Ausdrücke sind zulässig und durch Kommas getrennt.

3. group_condition Nach der Gruppierung erfolgt der Prozess der bedingten Filterung der gruppierten Daten.

4. In der Gruppierungssyntax sind die Felder, die nach „select“ erscheinen, entweder die Felder nach „group by“ oder die Spalten der Aggregatfunktion. Andere Typen melden eine Ausnahme, die im Folgenden ausführlich erläutert wird.

Bevor wir über die Gruppierung sprechen, werfen wir einen Blick auf die Aggregatfunktionen, die ein wichtiger Teil des Syntaxformats für Gruppierungsabfragen sind. Wir müssen Daten häufig zusammenfassen, ohne sie tatsächlich abzurufen. Daher bietet MySQL spezielle Funktionen. Mit diesen Funktionen können wir die Daten berechnen, die wir für die Analyse und Berichterstellung benötigen.

Aggregatfunktionen

Es gibt mehrere Typen von Aggregatfunktionen.

AVG()-Funktion

AVG() ermittelt den Durchschnittswert einer bestimmten Spalte, indem es die Anzahl der Zeilen in einer Tabelle zählt und die Werte in dieser Spalte summiert. Mit AVG() kann der Durchschnitt aller Spalten oder einer bestimmten Spalte oder Zeile zurückgegeben werden.

Das folgende Beispiel gibt das Durchschnittsalter der Benutzer in der Benutzertabelle zurück:

mysql> wähle * von Benutzer2;
+----+--------+------+----------+-----+
| ID | Name | Alter | Adresse | Geschlecht |
+----+--------+------+----------+-----+
| 1 | Marke | 21 | Fuzhou | 1 |
| 2 | Helen | 20 | Quanzhou | 0 |
| 3 | Sol | 21 | Xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | und | 24 | shanghai | 1 |
| 8 | sonnig | NULL | Guizhou | 0 |
+----+--------+------+----------+-----+
8 Reihen im Set

mysql> wähle Durchschnitt (Alter) von Benutzer2;
+----------+
| Durchschnitt (Alter) |
+----------+
| 23,8571 |
+----------+
1 Reihe im Set

Notiz:

1. AVG() kann nur verwendet werden, um den Durchschnitt einer bestimmten numerischen Spalte zu bestimmen.
2. Die Funktion AVG() ignoriert Zeilen mit NULL-Spaltenwerten, daher werden die Alterswerte in der obigen Abbildung nach der Akkumulation durch 7 geteilt, nicht durch 8.

COUNT()-Funktion

Die Funktion COUNT() zählt. Mit COUNT() können Sie die Anzahl der Zeilen in einer Tabelle ermitteln, die die Kriterien erfüllen.

Es gibt drei Möglichkeiten, die Anzahl auszudrücken: count(*), count(spezifisches Feld) und count(Konstante). Im Folgenden wird die Verwendung von count(*) und count(cname) demonstriert.

mysql> wähle * von Benutzer2;
+----+--------+------+----------+-----+
| ID | Name | Alter | Adresse | Geschlecht |
+----+--------+------+----------+-----+
| 1 | Marke | 21 | Fuzhou | 1 |
| 2 | Helen | 20 | Quanzhou | 0 |
| 3 | Sol | 21 | Xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | und | 24 | shanghai | 1 |
| 8 | sonnig | NULL | Guizhou | 0 |
+----+--------+------+----------+-----+
8 Reihen im Set

mysql> wähle count(*) von Benutzer2, wobei Geschlecht=0 ist;
+----------+
| Anzahl(*) |
+----------+
| 5 |
+----------+
1 Reihe im Set

mysql> wähle Anzahl(Alter) von Benutzer2, wobei Geschlecht=0 ist;
+------------+
| Anzahl (Alter) |
+------------+
| 4 |
+------------+
1 Reihe im Set

Wie Sie sehen, ermitteln beide die Anzahl der weiblichen Benutzer. Count(*) hat eins mehr als count(age), da age einen Nullwert enthält.

Also: Wenn Sie einen Spaltennamen angeben, werden Zeilen, deren Wert in der angegebenen Spalte leer ist, von der Funktion COUNT() ignoriert. Wenn in der Funktion COUNT() jedoch ein Sternchen (*) verwendet wird, werden sie nicht ignoriert.

MAX()- und MIN()-Funktionen

MAX() gibt den Maximalwert in der angegebenen Spalte zurück und MIN() gibt den Minimalwert in der angegebenen Spalte zurück.

mysql> wähle * von Benutzer2;
+----+--------+------+----------+-----+
| ID | Name | Alter | Adresse | Geschlecht |
+----+--------+------+----------+-----+
| 1 | Marke | 21 | Fuzhou | 1 |
| 2 | Helen | 20 | Quanzhou | 0 |
| 3 | Sol | 21 | Xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | und | 24 | shanghai | 1 |
| 8 | sonnig | NULL | Guizhou | 0 |
+----+--------+------+----------+-----+
8 Reihen im Set

mysql> wähle max(Alter),min(Alter) von Benutzer2;
+----------+----------+
| max(Alter) | min(Alter) |
+----------+----------+
| 33 | 20 |
+----------+----------+
1 Reihe im Set

Hinweis: Ebenso ignorieren die Funktionen MAX() und MIN() Zeilen, deren Spaltenwert NULL ist.

SUM-Funktion

SUM() wird verwendet, um die Summe (Gesamtsumme) der Werte in der angegebenen Spalte zurückzugeben. Im Folgenden wird die Summe aller Altersangaben zurückgegeben. Auch hier werden Nullwerte ignoriert.

mysql> wähle * von Benutzer2;
+----+--------+------+----------+-----+
| ID | Name | Alter | Adresse | Geschlecht |
+----+--------+------+----------+-----+
| 1 | Marke | 21 | Fuzhou | 1 |
| 2 | Helen | 20 | Quanzhou | 0 |
| 3 | Sol | 21 | Xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | NULL | 0 |
| 6 | anny | 23 | shanghai | 0 |
| 7 | und | 24 | shanghai | 1 |
| 8 | sonnig | NULL | Guizhou | 0 |
+----+--------+------+----------+-----+
8 Reihen im Set

mysql> wähle Summe(Alter) von Benutzer2;
+----------+
| Summe(Alter) |
+----------+
| 167 |
+----------+
1 Reihe im Set

Gruppenabfrage

Datenaufbereitung, vorausgesetzt, wir haben eine Bestelltabelle wie folgt (mit Aufzeichnung der Bestellmenge und Bestellzeit des Benutzers):

mysql> wähle * aus t_order;
+---------+-----+-------+--------+---------------------+------+
| Bestell-ID | UID | Uname | Betrag | Uhrzeit | Jahr |
+---------+-----+-------+--------+---------------------+------+
| 20 | 1 | Marke | 91,23 | 20.08.2018 17:22:21 | 2018 |
| 21 | 1 | Marke | 87,54 | 16.07.2019 09:21:30 | 2019 |
| 22 | 1 | Marke | 166,88 | 04.04.2019 12:23:55 | 2019 |
| 23 | 2 | helyn | 93,73 | 15.09.2019 10:11:11 | 2019 |
| 24 | 2 | helyn | 102,32 | 08.01.2019 17:33:25 | 2019 |
| 25 | 2 | helyn | 106.06 | 24.12.2019 12:25:25 | 2019 |
| 26 | 2 | helyn | 73,42 | 03.04.2020 17:16:23 | 2020 |
| 27 | 3 | sol | 55,55 | 05.08.2019 19:16:23 | 2019 |
| 28 | 3 | sol | 69,96 | 16.09.2020 19:23:16 | 2020 |
| 29 | 4 | weng | 199,99 | 08.06.2020 19:55:06 | 2020 |
+---------+-----+-------+--------+---------------------+------+
10 Reihen im Set

Einzelne Feldgruppierung

Das heißt, gruppieren Sie ein bestimmtes Feld, z. B. Benutzer, und geben Sie deren Benutzer-ID, Bestellmenge und Gesamtbetrag aus:

mysql> wähle uid, Anzahl(uid), Summe(Betrag) aus t_order-Gruppe nach uid;
+-----+------------+----------+
| uid | Anzahl(uid) | Summe(Betrag) |
+-----+------------+----------+
| 1 | 3 | 345,65 |
| 2 | 4 | 375,53 |
| 3 | 2 | 125,51 |
| 4 | 1 | 199,99 |
+-----+------------+----------+
4 Reihen im Set

Gruppierung mehrerer Felder

Das heißt, gruppieren Sie mehrere Felder, z. B. Benutzer, und gruppieren Sie dann ihre Bestelldaten aus verschiedenen Jahren, und geben Sie die Bestellmenge und den Gesamtverbrauch aus:

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag, Jahr aus t_order, gruppiere nach uid, Jahr;
+-----+------+-------------+------+
| UID | Nums | Gesamtbetrag | Jahr |
+-----+------+-------------+------+
| 1 | 1 | 91,23 | 2018 |
| 1 | 2 | 254,42 | 2019 |
| 2 | 3 | 302.11 | 2019 |
| 2 | 1 | 73,42 | 2020 |
| 3 | 1 | 55,55 | 2019 |
| 3 | 1 | 69,96 | 2020 |
| 4 | 1 | 199,99 | 2020 |
+-----+------+-------------+------+
7 Reihen im Set

Bedingtes Filtern vor dem Gruppieren: wobei

Das ist ganz einfach. Vor dem Gruppieren (group by) verwenden wir das Schlüsselwort where, um die Bedingungen zu filtern und die benötigten Daten zu extrahieren. Angenommen, wir müssen nur die Daten nach August 2019 auflisten. Es gibt nur 6 qualifizierte Quelldaten, und zwei davon sind im selben Jahr gruppiert:

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag, Jahr aus t_order, wobei Zeit > „2019-08-01“ ist, gruppiere nach uid, Jahr;
+-----+------+-------------+------+
| UID | Nums | Gesamtbetrag | Jahr |
+-----+------+-------------+------+
| 2 | 2 | 199,79 | 2019 |
| 2 | 1 | 73,42 | 2020 |
| 3 | 1 | 55,55 | 2019 |
| 3 | 1 | 69,96 | 2020 |
| 4 | 1 | 199,99 | 2020 |
+-----+------+-------------+------+
5 Reihen im Set

Bedingtes Filtern nach Gruppieren:

Manchmal müssen wir die Daten nach der Gruppierung filtern. In diesem Fall müssen wir das Schlüsselwort „having“ verwenden, um die Daten zu filtern. Unter den oben genannten Bedingungen müssen wir die Daten abrufen, die mehr als einmal verwendet wurden:

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag, Jahr aus t_order, wobei Zeit > „2019-08-01“ ist, gruppiere nach uid, Jahr mit Nums > 1;
+-----+------+-------------+------+
| UID | Nums | Gesamtbetrag | Jahr |
+-----+------+-------------+------+
| 2 | 2 | 199,79 | 2019 |
+-----+------+-------------+------+
1 Reihe im Set

Hier muss zwischen „wo“ und „haben“ unterschieden werden:

Wobei „+“ dazu dient, die Datensätze vor der Gruppierung (Aggregation) zu filtern, während „+“ dazu dient, die Ergebnisse nach der Gruppierung zu filtern und schließlich die gefilterten Ergebnisse zurückzugeben.

Having kann als zweistufige Abfrage verstanden werden, d. h. die Abfrageoperation, die Having enthält, erhält zuerst die SQL-Abfrageergebnistabelle ohne die Having-Klausel, verwendet dann die Having-Bedingung für diese Ergebnistabelle, um die übereinstimmenden Datensätze herauszufiltern, und gibt schließlich diese Datensätze zurück. Daher kann auf Having eine Aggregatfunktion folgen, und diese Aggregatfunktion muss nicht dieselbe sein wie die Aggregatfunktion nach Select.

Sortieren nach Gruppierung

Die Sortierbedingung wird nach der Gruppierungsbedingung platziert. Dies bedeutet, dass nach der Berechnung des Gesamtverbrauchs und der Verbrauchshäufigkeit jedes Benutzers der Gesamtverbrauch des Benutzers in absteigender Reihenfolge sortiert wird.

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag aus t_order, Gruppierung nach uid;
+-----+------+-------------+
| UID | Nums | Gesamtbetrag |
+-----+------+-------------+
| 1 | 3 | 345,65 |
| 2 | 4 | 375,53 |
| 3 | 2 | 125,51 |
| 4 | 1 | 199,99 |
+-----+------+-------------+
4 Reihen im Set

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag aus t_order, gruppiere nach uid, sortiere nach Gesamtbetrag, desc;
+-----+------+-------------+
| UID | Nums | Gesamtbetrag |
+-----+------+-------------+
| 2 | 4 | 375,53 |
| 1 | 3 | 345,65 |
| 4 | 1 | 199,99 |
| 3 | 2 | 125,51 |
+-----+------+-------------+
4 Reihen im Set

Begrenzung nach Gruppierung

Das Schlüsselwort „limit“ wird normalerweise am Ende der Anweisung platziert. Basierend auf unserer obigen Suche beschränken wir beispielsweise 1, um nur das Element mit dem höchsten Verbrauchsbetrag abzurufen und die anderen zu überspringen.

mysql> wähle uid, Anzahl(uid) als Nums, Summe(Betrag) als Gesamtbetrag aus t_order, gruppiere nach uid, sortiere nach Gesamtbetrag, Desc-Limit 1;
+-----+------+-------------+
| UID | Nums | Gesamtbetrag |
+-----+------+-------------+
| 2 | 4 | 375,53 |
+-----+------+-------------+
1 Reihe im Set

Die Reihenfolge, in der Schlüsselwörter ausgeführt werden

Wir können sehen, dass wir oben die Schlüsselwörter where, group by, having, order by und limit verwendet haben. Wenn sie zusammen verwendet werden, haben sie eine bestimmte Reihenfolge. Die falsche Reihenfolge führt zu einer Ausnahme. Das Syntaxformat ist wie folgt:

 wähle cname aus tname
 wobei [ursprüngliche Tabellenabfragebedingung]
 gruppieren nach [Gruppierungsausdruck]
 mit [Gruppenfilterbedingung]
 Sortieren nach [Sortierbedingung]
 Grenzwert [Offset,] Anzahl;

mysql> wähle uid, Anzahl (uid) als Nums, Summe (Betrag) als Gesamtbetrag aus t_order, wobei Zeit > „01.08.2019“ ist, gruppiere nach uid mit Gesamtbetrag > 100, sortiere nach Gesamtbetrag, Abstiegslimit 1;
+-----+------+-------------+
| UID | Nums | Gesamtbetrag |
+-----+------+-------------+
| 2 | 3 | 273,21 |
+-----+------+-------------+
1 Reihe im Set

Zusammenfassen

1. In der Gruppierungssyntax sind die Felder, die nach select erscheinen, entweder die Felder nach group by oder die Spalten der Aggregatfunktion. Andere Typen melden eine Ausnahme: Sie können es selbst ausprobieren.

2. Die Ausführungsreihenfolge der Gruppierungsschlüsselwörter: where, group by, having, order by, limit. Die Reihenfolge kann nicht geändert werden, da sonst eine Ausnahme gemeldet wird: Sie können es selbst ausprobieren.

Oben sind die Details der MySQL-Gruppierungsabfrage und der Aggregatfunktion aufgeführt. Weitere Informationen zur MySQL-Gruppierungsabfrage und der Aggregatfunktion finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!