Wie man zwei Aggregatfunktionen in SQL kombiniert

Haben Sie Probleme, zwei Aggregatfunktionen in einer Abfrage zu verwenden? In diesem Artikel erfahren Sie, wie Sie es richtig machen - und zwar auf die zwei richtigen Arten.

Bei der Datenanalyse und Berichterstellung müssen wir oft die Anzahl der Datensätze zählen oder sie summieren und dann den Durchschnitt dieser Zählung oder Summe berechnen.

Übertragen auf die SQL-Logik ist dies die Aggregation von aggregierten Daten oder die Aggregation auf mehreren Ebenen. Für Aggregationszwecke gibt es die SQL-Aggregatfunktionen. Und für die mehrstufige Aggregation verwenden Sie (mindestens) zwei Aggregatfunktionen gleichzeitig.

Wenn Sie an qualitativ hochwertigen Berichten interessiert sind, brauchen Sie viel mehr als die Aggregatfunktionen von SQL. Sie sind jedoch sicherlich die Grundlage für gute Berichte. Die beste Möglichkeit, auf dieser Grundlage aufzubauen, ist unser interaktiver Erstellen einfacher SQL-Berichte Kurs. Er bietet einen ganzen Abschnitt, der ausschließlich der Berechnung von mehrstufigen Aggregationen in SQL und der Zusammenfassung von Daten mithilfe von Aggregatfunktionen gewidmet ist. Er behandelt auch die Klassifizierung von Daten mit Hilfe von CASE WHEN und GROUP BY, die Berechnung mehrerer Metriken in einem Bericht und den Vergleich von Gruppen innerhalb desselben Berichts. Mit 97 interaktiven Übungen hilft Ihnen dieser Kurs, das Reporting in SQL zu beherrschen!

Um es noch einfacher zu machen, zeige ich Ihnen hier, wie Sie zwei Aggregatfunktionen in SQL kombinieren können. Wenn Ihr Wissen über Aggregatfunktionen eingerostet ist, kann Ihnen dieser Leitfaden zu SQL-Aggregatfunktionen helfen, diesem Artikel zu folgen. Sie können auch an diesen Beispielen für Aggregatfunktionen üben.

Beispielhafte Daten

Wir haben eine Tabelle namens new_users. Sie sammelt Daten über die neuen Nutzer einer App auf dem südamerikanischen Markt. Die Spalten sind:

• id - Die ID für jeden einzelnen Datensatz.
• date - Das Datum, an dem die Benutzer beigetreten sind.
• number_of_new_users - Die Anzahl der neuen Benutzer nach Datum.
• city - Die Stadt der Benutzer.
• country - Das Land, in dem sich die Benutzer befinden.

Wie Sie unten sehen, gibt es insgesamt zwei Daten, wobei jedes Datum eine unterschiedliche Anzahl von Benutzern aus zwei Ländern und zwei Städten in jedem Land aufweist.

Wie würden Sie die Funktionen AVG() und SUM() verwenden, um die durchschnittliche tägliche Anzahl der neuen Nutzer nach Land zu berechnen?

Die naive Lösung

Die Logik, die hinter dem naiven Ansatz steht, ist eigentlich recht solide. Schade ist nur, dass diese naive Lösung gar keine Lösung ist. Sie werden bald erkennen, warum.

Wenn Sie nur an den mathematischen Ansatz denken, ist er ganz einfach: Sie müssen zunächst die Anzahl der neuen Nutzer pro Land und Tag summieren und dann den Durchschnitt dieser Summe berechnen.

Übertragen auf SQL-Funktionen erscheint es logisch, so etwas zu schreiben:

SELECT country,
 	 AVG(SUM(number_of_new_users)) AS average_new_daily_users
FROM new_users
GROUP BY country;

Warum nicht, oder? Es gibt einen sehr einfachen Grund, warum nicht: SQL lässt die Verschachtelung von Aggregatfunktionen nicht zu. Mit anderen Worten, Sie können eine Aggregatfunktion nicht innerhalb einer Aggregatfunktion verwenden. Nun, Sie können es, aber die Abfrage wird einen Fehler zurückgeben, der genau das sagt, was ich gerade gesagt habe:

Wie kann man diese Abfrage korrigieren, um zwei Aggregatfunktionen in SQL zu kombinieren?

Es gibt zwei Möglichkeiten: mit einer Unterabfrage oder mit Common Table Expressions (CTEs).

Echte Lösung 1: Unterabfrage

Die erste Möglichkeit besteht darin, zwei Aggregatfunktionen mithilfe einer Subquery zu kombinieren. Die Unterabfrage ist eine Abfrage innerhalb der Hauptabfrage. Bei der Erstellung von Berichten findet man sie normalerweise in den Klauseln SELECT, FROM oder WHERE.

In diesem Beispiel füge ich die Unterabfrage in die FROM Klausel ein.

SELECT country,
	 AVG(ds.sum_new_users) AS average_daily_new_users
FROM (SELECT date,
	  	 country,
	 	 SUM(number_of_new_users) AS sum_new_users
	  FROM new_users
	  GROUP BY date, country) AS ds
GROUP BY country;

Das Prinzip bei der Kombination von zwei Aggregatfunktionen besteht darin, die Unterabfrage für die Berechnung der "inneren" Statistik zu verwenden. Dann wird das Ergebnis in den Aggregatfunktionen der äußeren Abfrage verwendet.

Der obige Code wählt das Datum und das Land aus und berechnet die Summe der Spalte number_of_new_users. Das Ergebnis ist die tägliche Gesamtzahl der neuen Benutzer. Ich habe der Unterabfrage den Alias ds gegeben, was die Abkürzung für "daily sum" ist.

Sobald ich die tägliche Summe erhalten habe, habe ich diese in der äußeren Abfrage referenziert, indem ich den Durchschnitt der Spalte ds.sum_new_users berechnet habe - d. h. den Durchschnitt der täglichen neuen Benutzer. Ich möchte, dass dieser Durchschnitt nach Land berechnet wird; deshalb habe ich die Daten nach der Länderspalte gruppiert.

Echte Lösung 2: CTE

Die andere Möglichkeit, Aggregatfunktionen in SQL zu kombinieren, ist die Verwendung einer CTE anstelle einer Subquery. Eine CTE ist eine aufgeräumtere und "näher an der mathematischen Logik" liegende Version einer Unterabfrage. Es handelt sich um einen Ausdruck, mit dem Sie ein temporäres Ergebnis erstellen können, auf das Sie in einer anderen SELECT Anweisung Bezug nehmen können. Sie können das Ergebnis einer CTE wie jede andere Tabelle verwenden. Der Unterschied besteht darin, dass das CTE-Ergebnis nur existiert, wenn ein CTE zusammen mit der Abfrage, die das CTE verwendet, ausgeführt wird. Eine genauere Erläuterung der CTEs finden Sie im Artikel Was ist eine CTE?

Die folgende Abfrage liefert das gleiche Ergebnis wie die Subquery-Lösung:

WITH ds AS (
  SELECT date, 
   country,
         SUM(number_of_new_users) AS sum_new_users
  FROM new_users
  GROUP BY date, country)

SELECT country,
       AVG(ds.sum_new_users) AS average_daily_new_users
FROM ds
GROUP BY country;

Jedes CTE wird mit dem Schlüsselwort WITH eingeleitet. Das gilt auch für meinen CTE namens ds. Nach dem Schlüsselwort AS folgt die CTE-Definition. In diesem Fall handelt es sich um die gleiche SELECT -Anweisung wie in der Unterabfrage aus dem vorherigen Beispiel. Hier kommt die sum(): sie wird wieder die Summe der neuen Benutzer nach Land und Datum zurückgeben.

Die zweite SELECT-Anweisung verweist auf CTE und berechnet den Durchschnitt der von CTE zurückgegebenen Summe.

Im Gegensatz zur Verschachtelung der Unterabfrage können Sie bei der Verwendung des CTE die Aggregatfunktionen in der logischen Reihenfolge verwenden: zuerst SUM(), dann AVG(). Die "innere" Funktion wird in der CTE verwendet, während die zweite Anweisung SELECT für die "äußere" Aggregatfunktion bestimmt ist.

Das Ergebnis ist dasselbe wie bei der Unterabfrage; ich brauche es nicht noch einmal zu zeigen. Es ist jedoch immer gut, mehr über die Unterschiede zwischen einer Unterabfrage und einem CTE zu erfahren und dann die Verwendung von CTEs an realen Beispielen zu üben.

Werden Sie ein Meister der Datenaggregation und Berichterstellung

Das Problem der Verwendung von zwei Aggregatfunktionen in SQL ist allgegenwärtig. Je komplexer die Berichterstattung wird, desto häufiger werden Sie mehrstufige Aggregationen verwenden. Sie wissen jetzt, dass Unterabfragen und CTEs die beiden Lösungen dafür sind.

Alle anderen Feinheiten des Berichtswesens finden Sie in unserem Erstellen einfacher SQL-Berichte Kurs. CTEs können Ihre Berichterstattung einfacher und ausgefeilter machen, daher ist die Teilnahme an diesem Rekursive Abfragen Kurs eine kluge Idee.