Aggregation

• Überblick
• Zählen der Temperaturmesswerte
• Lösung
• Mittelwertbildung mit NULL
• Lösung
• Was bewirkt diese Abfrage?
• Lösung
• Ordnen beim Verketten
• Schlüsselpunkte

Überblick

Unterricht: 10 min
Übungen: 10 min

Fragen

• Wie kann ich Summen, Mittelwerte und andere zusammenfassende Werte berechnen?

Ziele

• Definieren Sie die Aggregation und geben Sie Beispiele für ihre Anwendung.

• Schreiben Sie Abfragen, die aggregierte Werte berechnen.

• Verfolgen Sie die Ausführung einer Abfrage, die eine Aggregation durchführt.

• Erläutern Sie, wie fehlende Daten bei der Aggregation behandelt werden.

Wir wollen nun Bereiche und Durchschnittswerte für unsere Daten berechnen.Wir wissen, wie wir alle Daten aus der Tabelle Visited auswählen können:

SELECT dated FROM Visited;

-null-

aber um sie zu kombinieren, müssen wir eine Aggregationsfunktion wie min oder max verwenden.Jede dieser Funktionen nimmt einen Satz von Datensätzen als Eingabe und erzeugt einen einzigen Datensatz als Ausgabe:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min und max sind nur zwei der in SQL integrierten Aggregationsfunktionen.Drei weitere sind avg,countund sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

SELECT count(reading) FROM Survey WHERE quant = 'sal';

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

Wir haben hier count(reading) verwendet,aber wir hätten genauso gut quant oder ein beliebiges anderes Feld in der Tabelle oder sogar count(*) verwenden können, da sich die Funktion nicht um die Werte selbst kümmert, sondern nur darum, wie viele Werte es gibt.

SQL ermöglicht es uns, mehrere Aggregationen auf einmal durchzuführen.Wir können zum Beispiel den Bereich der sensiblen Salzgehaltsmessungen finden:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Wir können auch aggregierte Ergebnisse mit Rohergebnissen kombinieren, obwohl das Ergebnis Sie überraschen könnte:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

Warum erscheint der Name von Lake und nicht der von Roerich oder Dyer?Die Antwort ist, dass der Datenbankmanager, wenn er ein Feld aggregieren muss, aber nicht weiß, wie er es tun soll, einen aktuellen Wert aus der Eingabemenge auswählt.Er kann den ersten, den letzten oder einen ganz anderen Wert verwenden.

Eine weitere wichtige Tatsache ist, dass, wenn es keine Werte zum Aggregieren gibt – zum Beispiel, wenn es keine Zeilen gibt, die die WHERE-Klausel erfüllen – das Ergebnis der Aggregation „weiß nicht“ ist und nicht Null oder ein anderer beliebiger Wert:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

Ein letztes wichtiges Merkmal von Aggregationsfunktionen ist, dass sie in einer sehr nützlichen Weise mit dem Rest von SQL inkonsistent sind.Wenn wir zwei Werte addieren und einer davon ist null, ist das Ergebnis null. Wenn wir sum verwenden, um alle Werte in einer Menge zu addieren, und einer dieser Werte ist null, sollte das Ergebnis auch null sein.Es ist jedoch viel nützlicher, wenn Aggregationsfunktionen null Werte ignorieren und nur diejenigen kombinieren, die nicht null sind.Dieses Verhalten ermöglicht es uns, unsere Abfragen wie folgt zu schreiben:

SELECT min(dated) FROM Visited;

anstatt immer explizit filtern zu müssen:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

Es ist nicht immer sinnvoll, alle Datensätze auf einmal zu aggregieren.Nehmen wir zum Beispiel an, dass wir eine systematische Verzerrung in unseren Daten vermuten und dass die Strahlungswerte einiger Wissenschaftler höher sind als die der anderen.Wir wissen, dass das nicht funktioniert:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

, weil der Datenbankverwalter eine einzige beliebige Wissenschaftlernummer auswählt, anstatt für jeden Wissenschaftler einzeln zu aggregieren.Da es nur fünf Wissenschaftler gibt, könnten wir fünf Abfragen in der Form:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

Aber das wäre mühsam, und wenn wir jemals einen Datensatz mit fünfzig oder fünfhundert Wissenschaftlern hätten, wäre die Chance, dass wir alle diese Abfragen richtig hinbekommen, gering.

Was wir tun müssen, ist, den Datenbankmanager anzuweisen, die Stunden für jeden Wissenschaftler separat mit einer GROUP BY-Klausel zu aggregieren:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

GROUP BY tut genau das, was der Name sagt: gruppiert alle Datensätze mit dem gleichen Wert für das angegebene Feld zusammen, so dass die Aggregation jeden Stapel einzeln verarbeiten kann.Da alle Datensätze in jedem Stapel denselben Wert für person haben, spielt es keine Rolle mehr, dass der Datenbankmanager einen beliebigen Wert auswählt, der neben den aggregierten reading-Werten angezeigt wird.

So wie wir nach mehreren Kriterien gleichzeitig sortieren können, können wir auch nach mehreren Kriterien gruppieren.Um zum Beispiel den Durchschnittswert nach Wissenschaftler und gemessener Menge zu erhalten, fügen wir der GROUP BY-Klausel einfach ein weiteres Feld hinzu:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

Beachten Sie, dass wir quant zur Liste der angezeigten Felder hinzugefügt haben, da die Ergebnisse sonst nicht viel Sinn ergeben würden.

Gehen wir noch einen Schritt weiter und entfernen alle Einträge, bei denen wir nicht wissen, wer die Messung vorgenommen hat:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

Wenn man genauer hinsieht, diese Abfrage:

1. wählte Datensätze aus der Tabelle Surveyaus, bei denen das Feld person nicht Null war;

2. gruppierte diese Datensätze in Untergruppen, so dass die Werte person und quant in jeder Untergruppe gleich waren;

3. ordnete diese Untergruppen zunächst nach person und dann innerhalb jeder Untergruppe nach quant; und

4. zählte die Anzahl der Datensätze in jeder Untergruppe, berechnete den Durchschnittswert reading in jeder Gruppe und wählte einen person– und einen quant-Wert aus jeder Gruppe aus (es spielt keine Rolle, welche, da sie alle gleich sind).

Zählen der Temperaturmesswerte

Wie viele Temperaturmesswerte hat Frank Pabodie aufgezeichnet, und was war ihr Durchschnittswert?

Lösung

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

Zahl(Messwert)	Durchschnitt(Messwert)
2	-20.0

Mittelwertbildung mit NULL

Der Durchschnitt einer Menge von Werten ist die Summe der Werte geteilt durch die Anzahl der Werte.Bedeutet dies, dass die Funktion avg bei den Werten 1,0, null und 5,0 2,0 oder 3,0 liefert?

Lösung

Die Antwort lautet 3,0.NULL ist kein Wert, sondern das Fehlen eines Wertes.Als solcher wird er nicht in die Berechnung einbezogen.

Sie können dies bestätigen, indem Sie diesen Code ausführen:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Was bewirkt diese Abfrage?

Wir möchten die Differenz zwischen jedem einzelnen Strahlungsmesswert und dem Durchschnitt aller Strahlungsmesswerte berechnen.Wir schreiben die Abfrage:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Was ergibt diese Abfrage, und können Sie sich vorstellen, warum?

Lösung

Die Abfrage erzeugt nur eine Zeile von Ergebnissen, obwohl wir eigentlich ein Ergebnis für jeden der Messwerte haben wollen.avg()Die Funktion avg() erzeugt nur einen einzigen Wert, und weil sie zuerst ausgeführt wird, wird die Tabelle auf eine einzige Zeile reduziert.Der Wert reading ist einfach ein beliebiger Wert.

Um das zu erreichen, was wir wollten, müssten wir zwei Abfragen durchführen:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Dies ergibt den Durchschnittswert (6.5625), den wir dann in eine zweite Abfrage einfügen können:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Dies ergibt das Gewünschte, aber wir können dies mit Hilfe von Unterabfragen in einer einzigen Abfrage kombinieren.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

Auf diese Weise müssen wir nicht zwei Abfragen ausführen.

Zusammenfassend lässt sich sagen, dass wir in der ursprünglichen Abfrage avg(reading) durch (SELECT avg(reading) FROM Survey WHERE quant='rad') ersetzt haben.

Ordnen beim Verketten

Die Funktion group_concat(field, separator)verkettet alle Werte in einem Feld unter Verwendung des angegebenen Trennzeichens (oder ‚,‘, wenn das Trennzeichen nicht angegeben ist).Verwenden Sie diese Funktion, um eine einzeilige Liste der Namen von Wissenschaftlern zu erstellen, z. B.:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Können Sie eine Möglichkeit finden, die Liste nach Nachnamen zu ordnen?

Schlüsselpunkte

• Verwenden Sie Aggregationsfunktionen, um mehrere Werte zu kombinieren.

• Aggregationsfunktionen ignorieren null Werte.

• Die Aggregation erfolgt nach der Filterung.

• Verwenden Sie GROUP BY, um Teilmengen separat zu kombinieren.

• Wenn für ein Feld keine Aggregationsfunktion angegeben wird, kann die Abfrage einen beliebigen Wert für dieses Feld zurückgeben.