Was ist der Unterschied zwischen einem GROUP BY und einem PARTITION BY?

Fensterfunktionen sind eine großartige Ergänzung zu SQL, und sie können Ihnen das Leben sehr erleichtern, wenn Sie wissen, wie man sie richtig einsetzt. Heute werden wir uns mit den Unterschieden zwischen GROUP BY und PARTITION BY beschäftigen. Wir beginnen mit den Grundlagen und führen Sie langsam an einen Punkt heran, an dem Sie selbständig weiterforschen können.

PARTITION BY vs. GROUP BY

Die Klauseln PARTITION BY und GROUP BY werden in SQL häufig verwendet, wenn Sie einen komplexen Bericht erstellen müssen. Während die Rückgabe der Daten selbst in vielen Fällen nützlich (und sogar notwendig) ist, sind oft komplexere Berechnungen erforderlich. An dieser Stelle kommen GROUP BY und PARTITION BY ins Spiel. Obwohl sie sich insofern sehr ähnlich sind, als sie beide eine Gruppierung vornehmen, gibt es doch wesentliche Unterschiede. Wir werden diese Unterschiede in diesem Artikel analysieren.

GROUP BY

Die GROUP BY Klausel wird in SQL-Abfragen verwendet, um Gruppen auf der Grundlage bestimmter Kriterien zu definieren. Diese Kriterien werden normalerweise als Kategorien in Berichten verwendet. Beispiele für Kriterien zur Gruppierung sind:

• alle Angestellten nach ihrem Jahresgehalt gruppieren
• Gruppierung aller Züge nach ihrem ersten Bahnhof
• Gruppierung der Einnahmen und Ausgaben nach Monaten
• Gruppierung von Schülern nach der Klasse, in der sie eingeschrieben sind

Mit der Klausel GROUP BY werden die Daten in eine neue Ergebnismenge umgewandelt, in der die ursprünglichen Datensätze anhand der von uns angegebenen Kriterien in verschiedene Gruppen eingeordnet werden. Weitere Einzelheiten zur GROUP BY Klausel finden Sie in diesem Artikel.

Mit diesen Gruppen können wir einige zusätzliche Aktionen oder Berechnungen durchführen, von denen die meisten eng mit Aggregatfunktionen verbunden sind. Um es kurz zu machen: Aggregatfunktionen werden verwendet, um unsere Daten zu aggregieren, und dabei gehen die ursprünglichen Details im Abfrageergebnis verloren. Es gibt viele Aggregatfunktionen, aber die am häufigsten verwendeten sind COUNT, SUM, AVG, MIN und MAX.

Wenn Sie die Verwendung der GROUP BY-Klausel üben möchten, empfehlen wir Ihnen unseren interaktiven Kurs Erstellen von Berichten in SQL. Aggregatfunktionen und die GROUP BY-Klausel sind für die Erstellung von Berichten in SQL unerlässlich.

Betrachten wir das folgende Beispiel. Hier haben wir die train Tabelle mit den Informationen über die Züge, die journey Tabelle mit den Informationen über die von den Zügen zurückgelegten Fahrten und die route Tabelle mit den Informationen über die Strecken für die Fahrten. Siehe unten - sehen Sie sich die Daten an und wie die Tabellen miteinander verbunden sind:

Führen wir die folgende Abfrage aus, die die Informationen über Züge und die zugehörigen Fahrten zurückgibt, indem wir die train und die journey Tabellen.

SELECT
        train.id,
        train.model,
        journey.*
FROM train
INNER JOIN journey ON journey.train_id = train.id
ORDER BY
        train.id ASC;

Hier ist das Ergebnis:

Sie können sehen, dass der Zug mit id = 1 5 verschiedene Zeilen hat, der Zug mit id = 2 hat 4 verschiedene Zeilen, usw.

Führen wir nun eine Abfrage mit denselben beiden Tabellen unter Verwendung von GROUP BY durch.

SELECT
  	train.id,
	train.model,
	COUNT(*) AS routes
FROM train
INNER JOIN journey ON journey.train_id = train.id
GROUP BY
  	train.id,
	train.model
ORDER BY
  	train.id ASC;

Das Ergebnis ist das folgende:

Anhand des Abfrageergebnisses können Sie sehen, dass wir Informationen aggregiert haben, die uns die Anzahl der Strecken für jeden Zug anzeigen. Dabei haben wir die Details auf Zeilenebene aus der Tabelle journey Tabelle.

Sie können diese Ergebnismenge mit der vorherigen vergleichen und prüfen, ob die Anzahl der Zeilen, die von der ersten Abfrage zurückgegeben wurden (Anzahl der Strecken), mit der Summe der Zahlen in der aggregierten Spalte (routes) des Ergebnisses der zweiten Abfrage übereinstimmt.

Obwohl Sie Aggregatfunktionen in einer Abfrage ohne eine GROUP BY Klausel verwenden können, ist dies in den meisten Fällen notwendig. Aggregatfunktionen funktionieren folgendermaßen:

1. Sie erzeugen Gruppen mit einer GROUP BY Anweisung, indem Sie eine oder mehrere Spalten angeben, die innerhalb jeder Gruppe den gleichen Wert haben.
2. Die Aggregatfunktion errechnet das Ergebnis.
3. Die ursprünglichen Zeilen werden "zusammengeklappt". Sie können auf die Spalten in der Anweisung GROUP BY und auf die von den Aggregatfunktionen erzeugten Werte zugreifen, aber die ursprünglichen Details auf Zeilenebene sind nicht mehr vorhanden.

Das "Kollabieren" der Zeilen ist in den meisten Fällen in Ordnung. Manchmal müssen Sie jedoch die ursprünglichen Details auf Zeilenebene mit den Werten kombinieren, die von den Aggregatfunktionen zurückgegeben werden. Dies kann mit Unterabfragen geschehen, indem man die Zeilen in der Originaltabelle mit der Ergebnismenge der Abfrage unter Verwendung von Aggregatfunktionen verknüpft. Sie können aber auch einen anderen Ansatz wählen, den wir als nächstes sehen werden.

PARTITION BY

Je nachdem, was Sie tun müssen, können Sie PARTITION BY in unseren Abfragen verwenden, um aggregierte Werte für die definierten Gruppen zu berechnen. PARTITION BY wird mit OVER() und Windows-Funktionen kombiniert, um aggregierte Werte zu berechnen. Dies ist GROUP BY und den Aggregatfunktionen sehr ähnlich, jedoch mit einem wichtigen Unterschied: Wenn Sie PARTITION BY verwenden, bleiben die Details auf Zeilenebene erhalten und werden nicht komprimiert. Das heißt, Sie haben sowohl die ursprünglichen Details auf Zeilenebene als auch die aggregierten Werte zur Verfügung. Alle Aggregatfunktionen können als Fensterfunktionen verwendet werden.

Betrachten wir nun die folgende Abfrage. Zusätzlich zu train und journeybeziehen wir nun auch die Routentabelle mit ein.

SELECT
  	train.id,
	train.model,
	route.name,
	route.from_city,
	route.to_city,
	COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes,
	COUNT(*) OVER () AS routes_total
FROM train
INNER JOIN journey ON journey.train_id = train.id
INNER JOIN route ON journey.route_id = route.id;

Hier ist das Ergebnis der Abfrage:

Anhand der Ergebnismenge können wir mehrere wichtige Punkte erkennen:

• Wir haben kein GROUP BY verwendet, aber trotzdem aggregierte Werte erhalten (routes und routes_total).
• Wir haben die gleichen Spalten (id und model) wie in der vorherigen Abfrage GROUP BY, aber die ursprünglichen Details auf Zeilenebene wurden beibehalten. Die aggregierten Werte werden in allen Zeilen mit denselben Werten für id und model wiederholt. Dies ist zu erwarten; als Beispiel haben wir 5 Reisedatensätze für id = 1, die alle identische Werte für diese Spalten haben.
• Wir haben auch Werte in den Spalten name, from_city und to_city, die sich innerhalb eines bestimmten Werts von id unterscheiden. Hätten wir ein GROUP BY für die Spalten id und model verwendet, wären diese Details auf Zeilenebene verloren gegangen.
• COUNT(*) OVER () AS routes_total ergab die gleiche Gesamtzahl, nämlich 30, wie COUNT und GROUP BY es tun würden. In dieser Ergebnismenge ist dieser Wert jedoch in jeder Zeile enthalten.
• Der Teil COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes ist sehr interessant. Mit der Klausel PARTITION BY haben wir die Gruppe definiert, für die diese Fensterfunktion verwendet werden soll. Daher wird in der Spalte routes nur die Anzahl der Zeilen für diese Gruppe angezeigt. Fensterfunktionen werden angewandt, nachdem die Zeilen gefiltert wurden, so dass die Details auf Zeilenebene erhalten bleiben, während die Gruppen weiterhin durch PARTITION BY definiert werden.

Die Verwendung von Standardaggregatfunktionen als Fensterfunktionen mit dem Schlüsselwort OVER() ermöglicht es, aggregierte Werte zu kombinieren und die Werte der ursprünglichen Zeilen beizubehalten. Wir können dasselbe mit Aggregatfunktionen erreichen, aber das erfordert Unterabfragen für jede Gruppe oder Partition.

Es ist wichtig zu beachten, dass alle Standard-Aggregatfunktionen als Fensterfunktionen wie diese verwendet werden können.

Fensterfunktionen

Neben den Aggregatfunktionen gibt es noch einige andere wichtige Fensterfunktionen, wie z.B.:

• ROW_NUMBER(). Gibt die laufende Nummer der Zeile in der Ergebnismenge zurück.
• RANK(). Ähnlich wie ROW_NUMBER(), kann aber eine Spalte als Argument nehmen. Die Rangfolge wird über den Wert dieser Spalte bestimmt. Wenn zwei oder mehr Zeilen den gleichen Wert in dieser Spalte haben, erhalten diese Zeilen alle den gleichen Rang. Der nächste Rang wird von der entsprechenden Anzahl von Zeilen nach oben fortgesetzt; wenn zum Beispiel zwei Zeilen den gleichen Rang 10 haben, ist der nächste Rang 12.
• DENSE_RANK(). Sehr ähnlich zu RANK(), außer dass es keine "Lücken" gibt. Wenn im vorherigen Beispiel zwei Zeilen den Rang 10 haben, ist der nächste Rang 11.
• NTILE. Wird verwendet, um Quartile, Dezile oder andere Perzentile zu berechnen.
• LAG & LEAD. Wird verwendet, um Werte aus der vorherigen (LAG) oder der folgenden (LEAD) Zeile zu übernehmen.

Es gibt keine allgemeine Regel darüber, wann Sie Fensterfunktionen verwenden sollten, aber Sie können ein Gefühl für sie entwickeln. Ich empfehle Ihnen auf jeden Fall, den Kurs Fensterfunktionen zu besuchen; dort finden Sie alle Details, die Sie wissen wollen!

PARTITION BY und GROUP BY: Gemeinsamkeiten und Unterschiede

Obwohl wir in den meisten Fällen GROUP BY verwenden, gibt es zahlreiche Fälle, in denen PARTITION BY die bessere Wahl wäre. In einigen Fällen können Sie eine GROUP BY mit Unterabfragen verwenden, um eine PARTITION BY zu simulieren, aber dies kann zu sehr komplexen Abfragen führen.

Kommen wir zum Schluss zu den wichtigsten Ähnlichkeiten und Unterschieden:

• Gemeinsamkeiten: Beide werden verwendet, um aggregierte Werte zurückzugeben.
• Unterschied: Bei der Verwendung einer GROUP BY Klausel werden die ursprünglichen Zeilen zusammengezogen; aus diesem Grund können Sie später in der Abfrage nicht auf die ursprünglichen Werte zugreifen. Bei der Verwendung einer PARTITION BY -Klausel hingegen bleiben die Originalwerte erhalten, während wir gleichzeitig aggregierte Werte erzeugen können.
• Der Unterschied: PARTITION BY wird mit OVER() und den Windows-Funktionen kombiniert, um weitere Funktionalitäten hinzuzufügen.