SQL-Fensterfunktionen vs. GROUP BY: Was ist der Unterschied?

Ein weit verbreiteter Irrglaube unter SQL-Benutzern ist, dass es keinen großen Unterschied zwischen SQL-Fensterfunktionen und Aggregatfunktionen oder der GROUP BY -Klausel gibt. Die Unterschiede sind jedoch sehr groß.

Die einzige Gemeinsamkeit zwischen GROUP BY und Fensterfunktionen besteht vielleicht darin, dass beide die Ausführung einer Funktion (wie AVG, MAX, MIN oder COUNT) auf eine Gruppe von Datensätzen ermöglichen. Ich würde sagen, dass die besondere Stärke der Fensterfunktionen darin besteht, dass sie es uns ermöglichen, Ergebnisse zu erzielen, die sonst fast unmöglich zu erreichen wären.

In diesem Artikel gehen wir auf die Verwendung von Fensterfunktionen im Vergleich zu GROUP BY und Fensterfunktionen im Vergleich zu Aggregatfunktionen ein.

Kurzer Überblick über GROUP BY

Die GROUP BY Klausel ermöglicht es, eine Gruppe von Datensätzen nach bestimmten Kriterien zu gruppieren und eine Funktion (z. B. AVG oder MAX) auf jede Gruppe anzuwenden, um ein Ergebnis für jede Gruppe von Datensätzen zu erhalten. Sehen wir uns ein Beispiel an.

Wir haben eine Tabelle namens employee mit insgesamt fünf Mitarbeitern und drei Abteilungen:

Abb. 1: Die Tabelle der Mitarbeiter

Angenommen, wir möchten das Durchschnittsgehalt pro Abteilung und das höchste Gehalt für jede Abteilung erhalten. Wir sollten die folgende Abfrage verwenden:

SELECT Department, 
       avg(salary) as average,
       max(salary) as top_salary
FROM employee
GROUP BY department

Die folgende Abbildung zeigt das Ergebnis:

GROUP BY vs Fensterfunktionen

Beim Vergleich von Fensterfunktionen und GROUP BY ist zu beachten, dass GROUP BY die einzelnen Datensätze zu Gruppen zusammenfasst; nach der Verwendung von GROUP BY können Sie sich auf kein einzelnes Feld mehr beziehen, da es zusammengeklappt ist. Auf dieses Thema werden wir später noch näher eingehen. Für den Moment wollen wir nur erwähnen, dass Fensterfunktionen einzelne Datensätze nicht einklappen.

Wenn Sie also einen Bericht mit dem Namen des Mitarbeiters, dem Gehalt und dem höchsten Gehalt der Abteilung des Mitarbeiters erstellen möchten, können Sie dies nicht mit GROUP BY tun. Die einzelnen Datensätze jedes Mitarbeiters werden durch die GROUP BY department Klausel ausgeblendet. Für diese Art von Bericht müssen Sie die Fensterfunktionen verwenden, die Thema des nächsten Abschnitts sind.

Wenn Sie tiefer in die Feinheiten von SQL GROUP BY und Berichten einsteigen wollen, empfehlen wir Ihnen unseren interaktiven Kurs Erstellen einfacher SQL-Berichte.

Die Leistungsfähigkeit von Fensterfunktionen

Fensterfunktionen sind eine leistungsstarke Funktion von SQL. Sie ermöglichen es, Funktionen wie AVG, COUNT, MAX und MIN auf eine Gruppe von Datensätzen anzuwenden, während die einzelnen Datensätze weiterhin zugänglich bleiben. Da die einzelnen Datensätze nicht eingeklappt werden, können wir Abfragen erstellen, die Daten aus dem einzelnen Datensatz zusammen mit dem Ergebnis der Fensterfunktion anzeigen. Das macht die Fensterfunktionen so leistungsfähig.

Nehmen wir an, wir möchten eine Liste von Mitarbeiternamen, Gehältern und dem höchsten Gehalt in ihren Abteilungen erhalten.

SELECT 	employee_name,
department,
salary,
max(salary) OVER (PARTITION BY department) as top_salary
FROM		employee

Das nächste Bild zeigt das Ergebnis:

In der vorherigen Abfrage haben wir eine Fensterfunktion verwendet:

max(salary) OVER (PARTITION BY department) as top_salary

Die Fensterfunktion ist MAX() und wir haben sie auf die Menge der Datensätze angewendet, die durch die Klausel OVER (PARTITION BY department)definiert ist, d. h. auf die Datensätze mit demselben Wert im Feld Abteilung. Zum Schluss haben wir die Spalte top_salary umbenannt. Das Abfrageergebnis enthält Zeilen für einzelne Mitarbeiter. Wenn wir GROUP BY anstelle von Fensterfunktionen verwenden würden, hätten wir Zeilen für jede Abteilung.

Fensterfunktionen haben eine recht ausführliche Syntax; wenn Sie sich näher damit befassen möchten, empfehle ich Ihnen den Kurs "Fensterfunktionen", der Sie anhand von Beispielen und Übungen Schritt für Schritt durch die SQL-Fensterfunktionen führt.

Fensterfunktionen vs. Aggregatfunktionen

Wenn wir Fensterfunktionen und Aggregatfunktionen vergleichen, fällt uns ein besonders leistungsfähiges Merkmal auf der Seite der Fensterfunktionen auf: die Positionsfunktionen. Sie ermöglichen es uns, einen Spaltenwert aus anderen Datensätzen im selben Fenster zu erhalten. Dies ist eine wirklich erstaunliche Fähigkeit, die es SQL-Benutzern ermöglicht, komplexe Berichte in nur wenigen Zeilen zu erstellen. Lassen Sie uns kurz auf zwei dieser Funktionen eingehen: LEAD() und LAG().

Die Funktion LAG() gibt den Spaltenwert des vorherigen Datensatzes im Fenster zurück, während LEAD() den Spaltenwert des nächsten Datensatzes im Fenster zurückgibt. Es ist sehr wichtig, dass das Fenster nach der rechten Spalte geordnet ist, wenn Sie diese Funktionen verwenden wollen.

Sehen wir uns ein Beispiel an, wie wir diese Funktionen verwenden können. Nehmen wir an, wir haben eine Tabelle, in der Unternehmensaktien mit ihren Marktwerten zu einem bestimmten Zeitpunkt gespeichert sind. Die Tabelle könnte wie folgt aussehen:

Abb. 2: Die Aktientabelle

Angenommen, wir möchten einen Bericht erstellen, in dem der Wert jeder Aktie mit dem vorherigen Wert und der prozentualen Veränderung zum vorherigen Wert angezeigt wird. Dazu können wir die Funktion LEAD() verwenden, um den vorherigen Wert der Aktie zu ermitteln. Beachten Sie, dass wir ORDER BY timestamp verwenden, wenn wir die Partition (d. h. das Fenster der Datensätze) definieren. Wir werden später auf diesen Punkt zurückkommen.

SELECT 	
  share_symbol,
  timestamp,
  value,
  LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, 
  TRUNC(((value - (LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation
FROM share

Beachten Sie, dass die Spalten previous_value und percentage_variation berechnete Spalten sind. Sie verwenden Werte aus verschiedenen Datensätzen in derselben Tabelle.

Bei der Verwendung von Positionsfunktionen ist es sehr wichtig, eine ORDER BY -Klausel zusammen mit der PARTITION -Klausel einzufügen (wie in der vorherigen Abfrage). Wenn wir nicht die richtige ORDER BY verwenden, können die Ergebnisse falsch sein. Und warum? Weil Positionsfunktionen auf der Grundlage der Reihenfolge der Datensätze im Fenster arbeiten.

Schauen wir uns das ein wenig genauer an. Die Funktion FIRST_VALUE() gibt einen Spaltenwert aus dem ersten Datensatz im Fenster zurück. LAG(), gibt bekanntlich den Spaltenwert des vorherigen Datensatzes im Fenster zurück. Die richtige Reihenfolge im Fenster ist entscheidend; stellen Sie sich vor, was Sie sonst mit diesen Funktionen erhalten würden! In unserem Beispiel wollen wir den vorherigen chronologischen Marktwert für eine bestimmte Aktie. Daher haben wir ORDER BY timestamp verwendet. Wenn wir das ORDER BY weglassen oder nach einer anderen Spalte ordnen, wäre das Ergebnis falsch.

In einigen speziellen Fällen können Positionsfunktionen falsche Werte liefern, weil das Fenster nicht vollständig ausgefüllt ist. Und es gibt noch weitere Fensterfunktionen, wie RANK(), NTH_VALUE() und LAST_VALUE(). Wir können hier nicht auf alles eingehen, aber ich schlage vor, dass Sie sich diesen Artikel über Fensterfunktionen und diese Beispiele für Fensterfunktionen ansehen, um mehr zu erfahren.

Schließen des Fensters

In diesem Artikel haben wir uns mit den Unterschieden zwischen Fensterfunktionen und GROUP BY beschäftigt. Wir haben uns Beispiele mit verschiedenen Aggregat- und Fensterfunktionen angesehen. Wir haben auch über eine wichtige Einschränkung der GROUP BY Klausel gesprochen, nämlich das "Zusammenfallen von Datensätzen". Diese Einschränkung gibt es bei Fensterfunktionen nicht, so dass SQL-Entwickler Daten auf Datensatzebene mit den Ergebnissen von Fensterfunktionen in derselben Abfrage kombinieren können.

Ein weiterer Vorteil von Fensterfunktionen ist ihre Fähigkeit, Abfragewerte aus verschiedenen Datensätzen (aus demselben Fenster) in derselben Zeile der Ergebnismenge zu kombinieren.

Wenn Sie mehr über Fensterfunktionen erfahren möchten, empfehle ich Ihnen den Fensterfunktionen Kurs, in dem Sie die SQL-Fensterfunktionen anhand von interaktiven Übungen und ausführlichen Erklärungen erlernen können.