Klasseneinteilung mit GROUP BY

Lektion 4: Gruppierung mit GROUP BY

Mit der group by-Klausel kannst Du Datensätze in Gruppen einteilen. Dabei wird jeder Datensatz genau einer Gruppe zugeordnet.

Im Bild stellt die Gesamtwolke die Menge aller Datensätze dar. Jeder Punkt symbolisiert einen Datensatz. Diese Menge ist mittels group by farbe in fünf Gruppen eingeteilt, z. B. haben wir links unten die Gruppe der "grünen" Datensätze.

Hat man die Datensätze mittels group by in Gruppen eingeteilt, so kann man nur noch Angaben über die Gruppen machen, nicht mehr über einzelne Datensätze. Man kann also Informationen über die Gruppe der gelben Datensätze ausgeben, aber keine Detailangabe über den großen gelben Datensatz.

Wir können die Datensätze der cia-Datenbank nach den Regionen gruppieren und dann die Regionen ausgeben.

select Region from cia group by Region

top

Das obige Beispiel ist untypisch, weil man das Ergebnis einfacher auch so erhält: select distinct Region from cia

Der Mehrwert der group by-Klausel ergibt sich daraus, dass man mit den Aggregatfunktionen statistische Auswertungen der Gruppen vornehmen kann.

Wir wollen die Einwohnerzahl der Regionen und deren durchschnittliches Bruttosozialprodukt ausgeben:

select Region, sum(Einwohner), avg(BIP) from cia group by Region

Tipp: Um die Ergebnistabelle nach den Einwohnerzahlen zu sortieren, ergänzen wir einfach order by 2, geben also die Nummer der Spalte an, nach der sortiert werden soll.

top

Der typische Fehler im Zusammenhang mit der Gruppierung besteht darin, dass nach der Gruppenbildung noch versucht wird, Informationen von einzelnen Datensätzen auszugeben. Zum Beispiel:

select Region, Name, sum(Einwohner), AVG(BIP) from cia group by Region

Man kann zwar für jede Gruppe die Region und die Summe der Einwohner angeben, aber nicht den Namen, denn der ist für jedes Land einer Region anders.

Die meisten Datenbanksysteme weisen diese select-Anweisung mit einer Fehlermeldung zurück. Sinngemäß heißt es meist, dass das Attribut Name nicht Bestandteil der group by-Klausel ist. MySQL bildet eine Ausnahme, akzeptiert die select-Anweisung und liefert folgendes Ergebnis:

Dass das vom Datenbanksystem MySQL gelieferte Ergebnis tatsächlich fragwürdig ist, zeigt sich gleich beim ersten Datensatz. Für die Region Afrika wird als Name Algerien angegeben. Hier wird also zur Gruppe Afrika das Einzeldatum Name ausgegeben. (vgl. im Eingangsbild den großen gelben Datensatz).

Wir halten also fest, dass bei einer select-Anweisung mit group by in der select-Klausel nur die Gruppierungsattribute und die Aggregatfunktionen vorkommen dürfen.

top

Wenn man nicht an allen Gruppen interessiert ist, so kann man mit der having-Klausel die in Frage kommenden Gruppen auswählen. Bildlich bedeutet das, dass man einige der entstandenen Gruppen von der weiteren Betrachtung ausschließt.

Aber nach welchen Kriterien kann man Gruppen auswählen? Das geht nur mit einer Gruppeneigenschaft, also entweder danach wie die Gruppen gebildet wurden oder mit einer Aggregatfunktion, die eine Gruppeneigenschaft bestimmt. Deshalb wird in einer having-Klausel in aller Regel eine Aggregatfunktion benutzt, während in einer where-Klausel keine Aggregatfunktion möglich ist.

Es sollen die Regionen angezeigt werden, die mehr als 100 Millionen Einwohner haben.

select Region, sum(Einwohner) from cia group by Region having sum(Einwohner) > 1E08

top

Auch eine select-Anweisung mit group by-Klausel kann eine where-Bedingung enthalten. Die where-Bedingung wählt aus der gesamten Datenmenge die Datensätze aus, die anschließend gruppiert werden.

Im Bild hat die where-Bedingung die farbigen Datensätze ausgewählt, das anschließende group by hat die farbigen Datensätze nach der Farbe gruppiert.

Es sollen die Einwohnerzahlen der Regionen angezeigt werden, wobei nur Länder mit mehr als 100 Millionen Einwohner berücksichtigt werden sollen.

select Region, sum(Einwohner) from cia where Einwohner > 1E08 group by Region

Um das Zustandskommen der Werte besser verstehen zu können, wandeln wir die select-Anweisung so ab, dass wir die Einwohnerzahlen der beteiligten Länder ausgeben. Dazu ergänzen wir in der group by-Klausel und in der select-Klausel das Attribut Name. Die Gruppierung wird dann über die Kombination der Attribute Region und Name durchgeführt.

In der Region Nordamerika gibt es die zwei Länder Mexiko und die Vereinigte Staaten von Amerika mit mehr als 100 Millionen Einwohner. Die Summe ihrer Einwohnerzahlen wurde in der vorherigen Abfrage ermittelt.

top

Kommen in einer Abfrage where, group by und having vor, so wird in dieser Reihenfolge das Ergebnis bestimmt. Zuerst werden die zu betrachtenden Datensätze mittels where selektiert, dann werden diese mit group by gruppiert und zum Schluss werden mit having die gewünschten Gruppen ausgewählt.

Die where-Klausel wählt also die farbigen Datensätze aus, die group by-Klausel gruppiert nach Farben und die having-Klausel wählt die rote, grüne und gelbe Gruppe aus.

Wir gruppieren wieder die Länder mit mehr als 100 Millionen Einwohner, nehmen davon aber nur die Regionen mit mehr als 250 Millionen Einwohnern.

select Region, sum(Einwohner) from cia where Einwohner > 1E08 group by Region having sum(Einwohner) > 250000000

top

Die select-Anweisung hat folgende grammatische Struktur, wobei optionale Anteile in [Klammern] gesetzt sind und der senkrechte Strich in geschweiften {Klammern} Alternativen beschreibt.

SELECT [DISTINCT] select_ausdruck, ...
[FROM Tabellen]
[WHERE where-Bedingung]
[GROUP BY {positive_ganzzahl | spalten_name} [ASC | DESC], ...]]
[HAVING where-Bedingung]
[ORDER BY {positive_ganzzahl | spalten_name} [ASC | DESC], ...]
[LIMIT [offset,] zeilen]

SQL wertet diese Klauseln in der Reihenfolge FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT aus. Dabei funktionieren die Klauseln im Sinne einer Pipeline, das heißt die Ausgabe einer Klausel ist die Eingabe für die nächste. In funktionaler Notation wird die Reihenfolge der Auswertung wie folgt dargestellt:

LIMIT(ORDER BY(SELECT(HAVING(GROUP BY(WHERE(FROM...))))))

Genug der grauen Theorie - jetzt wird es wieder praktisch.
Hier geht es zu den Übungen der Lektion 4.

top