SQL-Fensterfunktionen vs. SQL-Aggregatfunktionen: Gemeinsamkeiten und Unterschiede

Wenn Sie mit den SQL-Fensterfunktionen nicht vertraut sind, fragen Sie sich vielleicht, wie sie sich von Aggregatfunktionen unterscheiden. Wann sollten Sie Fensterfunktionen verwenden? In diesem Artikel werden wir die Fensterfunktionen und die Aggregatfunktionen betrachten, ihre Ähnlichkeiten und Unterschiede untersuchen und herausfinden, welche Funktion Sie je nach Bedarf wählen sollten.

Nachdem Sie sich mit den Grundlagen von SQL vertraut gemacht haben, werden Sie sich wahrscheinlich mit einigen der fortgeschritteneren Funktionen beschäftigen wollen. Das ist auch gut so, denn diese Funktionen erleichtern die Berichterstellung und Analyse.

Schon bald werden Sie jedoch auf zwei mysteriöse Funktionsgruppen stoßen: Fensterfunktionen und Aggregatfunktionen. Was bewirken sie? Wie unterscheiden sie sich?

Das werden Sie gleich herausfinden.

Was sind SQL-Aggregatfunktionen?

Aggregatfunktionen arbeiten mit einer Reihe von Werten, um einen einzigen Einzelwert zurückzugeben. Dies sind die SQL-Aggregatfunktionen:

• AVG() gibt den Durchschnitt der angegebenen Werte zurück.
• SUM() Berechnet die Summe aller Werte in der Menge.
• MAX() und MIN() geben den maximalen bzw. minimalen Wert zurück.
• COUNT() gibt die Gesamtzahl der Werte in der Menge zurück.

Mit der GROUP BY Klausel können Sie einen Gesamtwert für mehrere Gruppen in einer Abfrage berechnen.

Nehmen wir zum Beispiel an, wir haben Transaktionsdaten von zwei Städten, San Francisco und New York:

Sie können SQL-Aggregatfunktionen verwenden, um den durchschnittlichen täglichen Transaktionsbetrag für jede Stadt zu berechnen. Sie müssen die Daten sowohl nach Datum als auch nach Stadt gruppieren:

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city
FROM transactions
GROUP BY date, city;

Hier ist das Ergebnis dieser Abfrage:

Durch die Verwendung der Aggregatfunktion AVG() und GROUP BY erhalten wir Ergebnisse, die nach Datum und Stadt gruppiert sind. Wir hatten zwei Transaktionen in New York am 2. November und zwei Transaktionen in San Francisco am 3. November, aber die Ergebnismenge enthält diese einzelnen Transaktionen nicht; Aggregatfunktionen fassen die einzelnen Zeilen zusammen und präsentieren den aggregierten Wert (hier den Durchschnitt) für alle Zeilen in der Gruppe.

Was sind SQL Fensterfunktionen?

In SQL arbeiten Fensterfunktionen mit einem Satz von Zeilen, der als Fensterrahmen bezeichnet wird. Sie geben für jede Zeile der zugrunde liegenden Abfrage einen einzelnen Wert zurück.

Der Fensterrahmen (oder einfach Fenster) wird mit der Klausel OVER() definiert. Diese Klausel ermöglicht auch die Definition eines Fensters auf der Grundlage einer bestimmten Spalte (ähnlich wie bei GROUP BY).

Um die zurückgegebenen Werte zu berechnen, können Window-Funktionen Aggregatfunktionen verwenden, die jedoch mit der Klausel OVER() verwendet werden.

Kehren wir zu unseren Daten für San Francisco und New York zurück. Hier ist wieder die Tabelle:

Wir möchten dieser Tabelle eine weitere Spalte mit dem durchschnittlichen täglichen Transaktionswert für jede Stadt hinzufügen. Die folgende SQL-Abfrage verwendet eine Fensterfunktion, um das benötigte Ergebnis zu erhalten:

SELECT id, date, city, amount,
       AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city
FROM transactions
ORDER BY id;

Hier ist das Ergebnis:

Beachten Sie, dass die Zeilen nicht kollabiert sind; wir haben immer noch eine Zeile für jede unserer Transaktionen. Alle berechneten Durchschnittswerte werden in der Spalte avg_daily_transaction_amount_for_city angezeigt.

Mehr über Fensterfunktionen erfahren Sie in diesem ausführlichen Leitfaden. Er enthält mehrere Beispiele, darunter einfache und fortgeschrittene Anwendungen. Außerdem hat das Team von LearnSQL.de ein tolles SQL Fensterfunktionen Cheat Sheet vorbereitet. Drucken Sie es aus und kleben Sie es auf Ihren Schreibtisch, vor allem, wenn Sie mit Fensterfunktionen noch nicht vertraut sind.

Gemeinsamkeiten und Unterschiede zwischen Fenster- und Aggregatfunktionen

Nachdem wir nun beide Arten von Funktionen gesehen haben, können wir die Ähnlichkeiten und Unterschiede zwischen ihnen zusammenfassen.

Sowohl Fensterfunktionen als auch Aggregatfunktionen:

• Operieren mit einer Gruppe von Werten (Zeilen).
• Sie können Aggregatbeträge (z. B. AVG(), SUM(), MAX(), MIN() oder COUNT()) über die Menge berechnen.
• Kann Daten nach einer oder mehreren Spalten gruppieren oder partitionieren.

Aggregatfunktionen mit GROUP BY unterscheiden sich von Fensterfunktionen dadurch, dass sie:

• GROUP BY() verwenden, um einen Satz von Zeilen für die Aggregation zu definieren.
• Zeilen auf der Grundlage von Spaltenwerten gruppieren.
• Zeilen auf der Grundlage der definierten Gruppen komprimieren.

Fensterfunktionen unterscheiden sich von den mit GROUP BY verwendeten Aggregatfunktionen durch folgende Merkmale

• OVER() anstelle von GROUP BY() verwenden, um eine Gruppe von Zeilen zu definieren.
• viele andere Funktionen als Aggregate verwenden können (z. B. RANK(), LAG() oder LEAD()).
• gruppiert Zeilen nach dem Rang, dem Perzentil usw. der Zeile sowie nach dem Spaltenwert.
• Zeilen nicht kollabieren.
• Kann einen gleitenden Fensterrahmen verwenden (der von der aktuellen Zeile abhängt).

Lassen Sie uns den letzten Unterschied anhand eines weiteren Beispiels demonstrieren. In dieser Übung wollen wir für jedes Datum den durchschnittlichen Umsatz für den vorangegangenen und den aktuellen Tag berechnen (d. h. einen gleitenden 2-Tages-Durchschnitt).

Ich schlage vor, mit einem gemeinsamen Tabellenausdruck (CTE) zu beginnen, um die Tabelle zu definieren daily_sales Tabelle, in der wir die Gesamtverkäufe für jeden Tag haben. Dann verwenden wir eine Fensterfunktion mit einem gleitenden Fensterrahmen, um den Durchschnitt der Gesamtverkäufe für den aktuellen und den vorangegangenen Tag zu berechnen. Die Abfrage lautet wie folgt:

WITH daily_sales AS (
    SELECT date, SUM(amount) AS sales_per_day
    FROM transactions
    GROUP BY date)
SELECT date, 
   AVG(sales_per_day) OVER (ORDER BY date ROWS 1 PRECEDING) 
AS avg_2days_sales
FROM daily_sales
ORDER BY date; 

Hier ist die Ergebnismenge:

In der ersten Zeile zeigt die Tabelle die Gesamtverkäufe für den 1. November an, da es für dieses Datum keine vorangegangene Zeile gibt. In der zweiten Zeile stehen die durchschnittlichen Umsätze für den 1. und 2. November, in der dritten Zeile die durchschnittlichen Umsätze für den 2. und 3. November und so weiter.

Fensterfunktionen eignen sich hervorragend für die Berechnung von gleitenden Durchschnitten - etwas, das Sie mit Aggregatfunktionen und GROUP BY() nicht tun können.

Üben wir Fensterfunktionen!

Die folgende Tabelle fasst alle Gemeinsamkeiten und Unterschiede zwischen den Aggregatfunktionen von SQL und den Fensterfunktionen zusammen:

Auch wenn die SQL-Fensterfunktionen ein fortgeschrittenes Thema sind, können Sie sie selbst üben. Dieser spezielle Leitfaden zum Üben von SQL-Fensterfunktionen gibt einige nützliche Tipps zum Erlernen der Syntax von Fensterfunktionen und zum Schreiben der entsprechenden Abfragen.

LearnSQL hat einen umfassenden Kurs über Fensterfunktionen vorbereitet , in dem Sie mit 218 interaktiven Übungen die Erstellung anspruchsvoller Fensterrahmen üben können. Sie lernen, wie Sie Fensterfunktionen nutzen können, um laufende Summen und gleitende Durchschnitte zu berechnen, Ranglisten zu erstellen, die besten und schlechtesten Ergebnisse zu finden und Trends im Zeitverlauf zu untersuchen. Mehr über diesen Kurs erfahren Sie in unserem Interview mit LearnSQL.de Chief Content Officer Agnieszka Kozubek-Krycuń.

Vielen Dank fürs Lesen und viel Spaß beim Lernen!