MariaDB-Kurs / Daten mit GROUP BY gruppieren und mit HAVING filtern

GROUP BY fasst Zeilen mit demselben Gruppierungsschlüssel zusammen; HAVING filtert die berechneten Gruppen. WHERE filtert dagegen einzelne Eingabezeilen vor der Gruppierung. Eine Bedingung zwischen WHERE und HAVING zu verschieben, kann deshalb die berücksichtigte Datenmenge und das Ergebnis verändern.

Die Bedeutung jeder Ergebniszeile festlegen

Eine Produktabfrage liefert eine Zeile pro Produkt, ein Kategorienbericht eine Zeile pro Kategorie. Jeder nicht aggregierte Ausgabewert benötigt auf dieser Ebene eine eindeutige Bedeutung. Wer nur nach Kategorie gruppiert und daneben einen Produktnamen auswählt, legt damit nicht fest, welches Produkt die Kategorie vertreten soll.

Klausel Wirkt auf Beispiel
WHERE Einzelne Eingabezeilen stock > 0
GROUP BY Zeilen mit gleichem Schlüssel category
HAVING Berechnete Gruppen COUNT(*) >= 2
ORDER BY Reihenfolge des Ergebnisses category ASC

Die Anleitung zu Aggregatfunktionen erklärt COUNT, SUM sowie NULL und leere Eingaben. Die offizielle Referenz zu GROUP BY beschreibt Gruppierung und zulässige Ausdrücke.

Kategorien, Mengen und Preise vorbereiten

Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt das Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei. Führen Sie SQL im MariaDB-Client aus.

Prüfen Sie vor dem Erstellen von products_23_lab mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Untersuchen Sie eine vorhandene Tabelle und deren Daten, bevor Sie über eine Wiederholung entscheiden. Verwenden Sie ein entbehrliches Übungsobjekt und erhalten Sie Anwendungstabellen.

USE shopcourse;
CREATE TABLE products_23_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p23_price CHECK (price >= 0),
  CONSTRAINT ck_p23_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_23_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Die koreanischen Originaltexte erhalten die reproduzierbare UTF-8-Übung. Die Produkte 1 bis 6 stehen für Notizbuch, Kugelschreiber, Becher, Lampe, Ordner und Kabel; die Kategorien bedeuten Schreibwaren, Haushalt und Elektronik. Kopieren Sie die Zeichenfolgen unverändert und vergleichen Sie anhand der Kennungen.

Es gibt drei Schreibwarenprodukte, zwei Haushaltsprodukte und ein Elektronikprodukt. Die erste Abfrage berücksichtigt auch Produkte ohne Bestand. Die Anleitung zu SELECT-Ausdrücken und Aliasnamen hilft, die Bedeutung der Ausgabespalten festzulegen.

Eine Ergebniszeile pro Kategorie berechnen

SELECT category,COUNT(*) AS product_count,SUM(stock) AS total_stock,
 SUM(price*stock) AS inventory_value
FROM products_23_lab GROUP BY category ORDER BY category;
Bedeutung der Kategorie Produkte Gesamtbestand Bestandswert
Schreibwaren 3 70 145000.00
Haushalt 2 17 221000.00
Elektronik 1 0 0.00

COUNT und SUM werden innerhalb jeder Kategorie getrennt berechnet. Die Tabelle erläutert die Kategorien auf Deutsch; die Abfrage liefert die koreanischen Originaltexte. Geben Sie die gewünschte Reihenfolge mit ORDER BY ausdrücklich an. Die Kollation kann beeinflussen, welche Textschlüssel als gleich gelten.

Berechnete Gruppen mit HAVING filtern

SELECT category,COUNT(*) AS product_count
FROM products_23_lab
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

Schreibwaren mit drei Produkten und Haushalt mit zwei Produkten bleiben erhalten. COUNT(*) beschreibt die Gruppe, deshalb gehört diese Bedingung in HAVING. Die Gruppenanzahl ist kein schon in jeder Eingabezeile vorhandener Einzelwert, den WHERE prüfen könnte. Der ausgeschriebene Aggregatausdruck verdeutlicht seine Aufgabe; in HAVING kann auch ein passender Ausgabealias verwendet werden.

Gruppierung nach einem WHERE-Filter vergleichen

SELECT category,COUNT(*) AS in_stock_products
FROM products_23_lab WHERE stock > 0
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

WHERE stock > 0 entfernt zuerst Kugelschreiber und Kabel. Es bleiben je zwei Schreibwaren- und Haushaltsprodukte; eine Elektronikgruppe entsteht nicht. HAVING stellt vorher entfernte Eingabezeilen nicht wieder her. Gesamtzahl der Produkte und Anzahl der Produkte mit Bestand sind verschiedene Messgrößen.

Eine Abfrage mit category, einem nicht gruppierten product_name und SUM(stock) wird bei aktivem ONLY_FULL_GROUP_BY abgewiesen. Entscheiden Sie, ob Sie auch nach dem Namen gruppieren und damit die Berichtsebene ändern müssen oder nur eindeutig für die Kategorie definierte Werte ausgeben. Deaktivieren Sie den Modus nicht als Ersatz für diese Entscheidung. Vergleichen Sie @@SESSION.sql_mode bei unterschiedlichen Verbindungen.

NULL-Gruppen und durch JOIN vervielfachte Zeilen prüfen

NULL-Schlüssel werden gemeinsam einer Gruppe zugeordnet. Übertragen Sie das Verhalten eines gewöhnlichen Gleichheitsvergleichs mit NULL in WHERE nicht auf die Gruppierung: Für diese beiden Aufgaben gelten unterschiedliche Regeln.

Prüfen Sie vor einer Aggregation nach JOIN, ob die Verknüpfung Eingabezeilen vervielfacht hat. Lesen Sie bei einer kleinen Übung die Zeilen jeder Kategorie einzeln und gleichen Sie sie mit der Zusammenfassung ab. Hier entsprechen 70 + 17 + 0 den insgesamt 87 Einheiten und 145000.00 + 221000.00 + 0.00 dem Bestandswert 366000.00.

Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, SHOW CREATE TABLE shopcourse.products_23_lab; und gespeicherte Daten. Sichern Sie benötigte Inhalte und bestätigen Sie die entbehrliche Übungstabelle. DROP TABLE entfernt Struktur und Zeilen und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.

DROP TABLE shopcourse.products_23_lab;