Hilfe
Diese Seite zu deinem Buch hinzufügen
Sammlung zeigen (0 Wikiseiten)
Seiten vorschlagenEinführung in SQL: Gruppierungen
Aus Wikibooks
Abfragen werden sehr häufig gruppiert, weil nicht nur einzelne Informationen, sondern auch Zusammenfassungen gewünscht werden. Durch die GROUP BY-Klausel im SELECT-Befehl werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst.
Dies macht in der Regel nur dann Sinn, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.
Inhaltsverzeichnis |
[Bearbeiten]
Syntax von GROUP BY
Die GROUP BY-Klausel hat folgenden allgemeinen Aufbau:
GROUP BY <Spaltenliste>
Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Bei jeder Spalte kann eine eigene Sortierung (siehe Datentypen) angegeben werden:
<Spaltenname> -- oder <Spaltenname> COLLATE <Collation-Name>
Die Spalten in der Spaltenliste können meistens wahlweise mit dem Spaltennamen der Tabelle, mit dem Alias-Namen aus der Select-Liste oder mit Spaltennummer gemäß der Select-Liste (ab 1 gezählt) angegeben werden.
In der Regel enthält die Abfrage eine der Aggregatfunktionen und wird durch ORDER BY nach den gleichen Spalten sortiert.
[Bearbeiten] Gruppierung bei einer Tabelle
Im einfachsten Fall werden Daten nach einer Spalte gruppiert und gezählt.
Im folgenden Beispiel wird die Anzahl der Abteilungen für jeden Ort aufgeführt.
SQL-Quelltext:
SELECT Ort, COUNT(*) AS Anzahl FROM Abteilung GROUP BY Ort ORDER BY Ort;
SQL-Ausgabe:
Bochum 3 Dortmund 4 Essen 4 Herne 1
Die folgende Abfrage listet auf, wie viele Mitarbeiter es in den Abteilungen und Raumnummern gibt:
SQL-Quelltext:
SELECT Abteilung_ID AS Abt, Raum, COUNT(*) AS Anzahl FROM Mitarbeiter GROUP BY Abt, Raum ORDER BY Abt, Raum;
SQL-Ausgabe:
ABT RAUM ANZAHL 1 112 1 1 113 1 2 212 2 3 312 1 3 316 1 4 412 2 // usw.
Am folgenden Beispiel wird die Gruppierung besonders deutlich.
Berechne die mittlere Schadenshöhe für die Schadensfälle mit und ohne Personenschäden.
SQL-Quelltext:
SELECT Verletzte, AVG(Schadenshoehe) AS Mittlere_Schadenshoehe FROM Schadensfall GROUP BY Verletzte;
SQL-Ausgabe:
VERLETZTE MITTLERE_SCHADENSHOEHE
J 3.903,87
N 1.517,45
Die Spalte Verletzte enthält entweder 'J' oder 'N' und ist verpflichtend, kann also keine NULL-Werte enthalten. Deshalb werden durch die GROUP BY-Anweisung eine oder zwei Gruppen gebildet. Für jede Gruppe wird der Mittelwert gesondert berechnet aus den Werten, die in der Gruppe vorkomen. In diesem Fall liefert die Funktion AVG also ein oder zwei Ergebnisse, abhängig davon, welche Werte in der Spalte Verletzte überhaupt vorkommen.
Zeilen, bei denen einer der Werte zur Gruppierung fehlt, oder Zeilen mit NULL-Werten werden als eigene Gruppe gezählt.
[Bearbeiten] Gruppierung über mehrere Tabellen
Eine Gruppierung kann auch Felder aus verschiedenen Tabellen auswerten. Dafür sind zunächst die Voraussetzungen für die Verknüpfung mehrerer Tabellen zu beachten. Bitte beachten Sie das folgende Beispiel.
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SQL-Quelltext:
SELECT fh.ID AS Hersteller_ID, fh.Name AS Hersteller_Name, EXTRACT(YEAR FROM sf.Datum) AS Jahr, SUM(sf.Schadenshoehe) AS Schadenssumme FROM Schadensfall sf JOIN Zuordnung_SF_FZ zu ON sf.ID = zu.Schadensfall_ID JOIN Fahrzeug fz ON fz.ID = zu.Fahrzeug_ID JOIN Fahrzeugtyp ft ON ft.ID = fz.Fahrzeugtyp_ID JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID GROUP BY Hersteller_ID, Hersteller_Name, Jahr ORDER BY Jahr, Hersteller_ID;
SQL-Ausgabe:
HERSTELLER_ID HERSTELLER_NAME JAHR SCHADENSSUMME
9 Volvo 2007 2.066,00
10 Renault 2007 5.781,60
11 Seat 2007 1.234,50
2 Opel 2008 1.438,75
11 Seat 2008 1.983,00
9 Volvo 2009 4.092,15
10 Renault 2009 865,00
Ausgangspunkt ist die Tabelle Schadensfall, weil aus deren Einträgen die Summe gebildet werden soll. Durch JOIN werden nacheinander die verknüpften Tabellen herangezogen, und zwar jeweils durch die ID auf die Verknüpfung: Schadensfall >> Zuordnung >> Fahrzeug >> Fahrzeugtyp >> Hersteller. Dann stehen ID und Name aus der Tabelle Fahrzeughersteller zur Verfügung, die für die Gruppierung gewünscht werden.
Zur Gruppierung genügt eigentlich die Verwendung von Hersteller_ID. Aber man möchte sich natürlich den Herstellennamen anzeigen lassen. Allerdings gibt es einen Fehler, wenn man den Namen nur in der SELECT-Liste benutzt und in der GROUP BY-Liste streicht:
Ungültiger Ausdruck in der Select-Liste (fehlt entweder in einer Aggregatfunktion oder in der GROUP BY-Klausel).
SQL-Quelltext:
SELECT fh.ID AS Hersteller_ID, fh.Name AS Hersteller_Name, EXTRACT(YEAR FROM sf.Datum) AS Jahr, SUM(sf.Schadenshoehe) AS Schadenssumme FROM Schadensfall sf JOIN ... (wie oben) GROUP BY Hersteller_ID, Jahr ORDER BY Jahr, Hersteller_ID
Siehe dazu die Erläuterungen unter Einschränkungen.
[Bearbeiten] Einschränkungen
Wie das letzte Beispiel zeigt, muss die GROUP BY-Klausel gewisse Bedingungen erfüllen. Auch dafür gilt: Jedes DBMS weicht an manchen Punkten vom Standard ab.
- Jeder Spaltenname der SELECT-Auswahl, der nicht zu einer Aggregatfunktion gehört, muss auch in der GROUP BY-Klausel benutzt werden.
Diese Bedingung wird im letzten Beispiel verletzt: Hersteller_Name steht in der SELECT-Liste, aber nicht in der GROUP BY-Klausel. In diesem Fall ist eine Änderung einfach, weil ID und Name des Herstellers gleichwertig sind. Übrigens erlaubt MySql auch die Auswahl von Feldern, die in der GROUP BY-Klausel nicht genannt sind.
Umgekehrt ist es in der Regel möglich, eine Spalte per GROUP BY zu gruppieren, ohne die Spalte selbst in der SELECT-Liste zu verwenden.
- GROUP BY kann Spalten der Tabellen, der abgeleiteten Tabellen oder der VIEWs in der FROM-Klausel oder der JOIN-Klausel enthalten. Sie kann keiner Spalte entsprechen, die das Ergebnis einer Funktion (genauer: einer numerischen Berechnung, einer Aggregatfunktion oder einer benutzerdefinierten Funktion) sind.
Dies entspricht der gleichen Einschränkung, die unter Berechnete Spalten / Ergebnis von Berechnungen genannt ist.
Mit der Beispieldatenbank sind keine passenden Beispiele möglich; ich muss mich deshalb auf fiktive Tabellen und Spalten beschränken:
Folgende Abfragen sind nicht zulässig:
SQL-Quelltext:
SELECT Spalte1, Spalte2 FROM T GROUP BY Spalte1 + Spalte2; SELECT Spalte1 + constant + Spalte2 FROM T GROUP BY Spalte1 + Spalte2;
Folgende Abfragen sind zulässig:
SQL-Quelltext:
SELECT Spalte1, Spalte2 FROM T GROUP BY Spalte1, Spalte2; SELECT Spalte1 + Spalte2 FROM T GROUP BY Spalte1, Spalte2; SELECT Spalte1 + Spalte2 FROM T GROUP BY Spalte1+ Spalte2; SELECT Spalte1 + Spalte2 + constant FROM T GROUP BY Spalte1, Spalte2;
- GROUP BY kann nicht benutzt werden in einem SELECT-Befehl mit folgenden Bedingungen:
- Er enthält eine INTO-Klausel (d.h. er wird benutzt, um einzelne Zeilen zu verarbeiten - wird in dieser Einführung nicht erläutert).
- Er enthält eine Unterabfrage oder eine VIEW, die selbst mit einer GROUP BY- oder HAVING-Klausel arbeitet.
- Jeder SELECT-Befehl darf einschließlich aller Unterabfragen höchstens eine GROUP BY-Klausel enthalten.
Zu dieser Bedingung sind mir keine vernünftigen Beispiele eingefallen. Eines, das ich konstruiert hatte, wurde trotz klarer Verletzung ausgeführt, wahrscheinlich weil die anderen Bedingungen dazu führten, dass es sich sowieso nur um jeweils eine Zeile handelte und keine Gruppierung erforderlich war.
[Bearbeiten] Erweiterungen
[Bearbeiten] Zwischensummen mit CUBE
Diese Erweiterung steht nur in manchen DBMS zur Verfügung. Ich beschränke mich deshalb auf einen kurzen Hinweis.
GROUP BY CUBE (<spaltenliste>)
Mit diesem Befehl werden zusätzlich zu den normalerweise von GROUP BY erstellten Zeilen auch Zwischensummen in das Ergebnis aufgenommen. Für jede mögliche Kombination von Spalten in der <spaltenliste> wird eine eigene GROUP BY-Summenzeile zurückgegeben.
Erläuterungen und Beispiele sind zu finden z.B. unter (Microsoft) GROUP BY und weiteren Links.
[Bearbeiten] Gesamtsummen mit ROLLUP
Auch diese Erweiterung steht nur in manchen DBMS zur Verfügung. Ich beschränke mich wiederum auf einen kurzen Hinweis.
GROUP BY <spaltenliste> WITH ROLLUP
Mit diesem Befehl werden zusätzlich zu den von GROUP BY erstellten Zeilen auch Gesamtsummen in das Ergebnis aufgenommen.
Erläuterungen und Beispiele sind zu finden z.B. unter (MySql) GROUP BY.
[Bearbeiten] Das Ergebnis wird eingeschränkt mit HAVING
Diese Erweiterung ist eine selbständige Klausel des SELECT-Befehls und hat eigentlich nichts mit der GROUP BY-Klausel zu tun. In der Praxis wird sie aber überwiegend als Ergänzung verwendet und folgt ggf. direkt danach.
GROUP BY <spaltenliste> HAVING <bedingungen>
Dieser Befehl dient dazu, nicht alle Gruppierungen in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen.
Im folgenden Beispiel (siehe oben unter "Gruppierung bei einer Tabelle") wird festgestellt, an welchen Orten sich genau eine Abteilung befindet.
SQL-Quelltext:
SELECT Ort, COUNT(*) AS Anzahl FROM Abteilung GROUP BY Ort HAVING COUNT(*) = 1 ORDER BY Ort;
SQL-Ausgabe:
Herne 1
Bitte beachten Sie, dass der Alias-Name nicht verwendet werden kann, sondern die Aggregatfunktion erneut aufgeführt werden muss.
Hinweis: Die HAVING-Klausel wird als letzter Teil des SELECT-Befehls ausgeführt. Es ist deshalb zu empfehlen, alle möglichen Einschränkungen vorher zu regeln, z.B. als Teil von WHERE-Bedingungen. Nur wenn – wie bei Aggregatfunktionen – diese Einschränkung erst am Schluss geprüft werden kann, ist HAVING zu benutzen.
Möglich, aber nicht günstig:
SQL-Quelltext:
SELECT Abteilung_ID, COUNT(*) FROM MITARBEITER GROUP BY Abteilung_ID HAVING Abteilung_ID < 3;
Besseres Verfahren, weil weniger Datensätze geprüft werden müssen:
SQL-Quelltext:
SELECT Abteilung_ID, COUNT(*) FROM MITARBEITER GROUP BY Abteilung_ID WHERE Abteilung_ID < 3;
[Bearbeiten] Zusammenfassung
In diesem Kapitel lernten wir Einzelheiten über die Gruppierung von Abfrageergebnissen.
- Dies wird meistens gleichzeitig mit ORDER BY und in Verbindung mit Aggregatfunktionen verwendet.
- Die Gruppierung ist auch über mehrere Tabellen hinweg möglich.
- Einige wichtige Einschränkungen sind zu beachten; vor allem sind die Felder aus der Spaltenliste in der Regel auch unter GROUP BY aufzuführen.
Die HAVING-Klausel kann das Abfrageergebnis einschränken, sollte aber zurückhaltend benutzt werden.
