Wie man die PARTITION BY-Klausel in SQL verwendet

Wir werden uns heute mit den Fensterfunktionen beschäftigen. Insbesondere werden wir uns auf die PARTITION BY Klausel konzentrieren und erklären, was sie bewirkt.

PARTITION BY ist eine der Klauseln, die in Fensterfunktionen verwendet werden. In SQL werden Fensterfunktionen verwendet, um Daten in Gruppen zu organisieren und Statistiken für sie zu berechnen. Das klingt sehr vertraut, nicht wahr? Auch wenn sie ähnlich klingen, sind Fensterfunktionen und GROUP BY nicht dasselbe; Fensterfunktionen sind eher wie GROUP BY auf Steroiden. Und warum? Weil Fensterfunktionen die Details einzelner Zeilen behalten, während sie Statistiken für die Zeilengruppen berechnen. GROUP BY kann das nicht!

PARTITION BY ist für diese Unterscheidung entscheidend; dies ist die Klausel, die ein Fensterfunktionsergebnis in Datenuntergruppen oder Partitionen unterteilt. In gewisser Weise ist es GROUP BY für Fensterfunktionen. Sie werden bald lernen, wie das funktioniert.

Es gibt eine viel umfassendere (und interaktive) Version dieses Artikels - unseren Fensterfunktionen Kurs. Er deckt alles ab, worüber wir sprechen, und noch viel mehr. Durch die interaktiven Übungen lernen Sie alles, was Sie über Fensterfunktionen wissen müssen. Sie werden die Klauseln OVER(), PARTITION BY und ORDER BY durchgehen und lernen, wie man Ranking- und Analyse-Fensterfunktionen verwendet. Der Kurs bietet Ihnen außerdem 47 Übungen zum Üben und ein abschließendes Quiz. Wenn Sie noch unentschlossen sind, sollten Sie die Fensterfunktionen lernen.

Lassen Sie uns nun über PARTITION BY sprechen!

PARTITION BY Syntax

Die Syntax für die PARTITION BY Klausel lautet:

SELECT column_name,
  	 window_function (expression) OVER (PARTITION BY column name)
FROM table;

Im window_function Teil geben Sie die spezifische Fensterfunktion an.

Die OVER() Klausel ist eine obligatorische Klausel, die dafür sorgt, dass die Fensterfunktion funktioniert. Sie definiert praktisch die Fensterfunktion.

Nach der Subklausel PARTITION BY folgt/folgen die Spaltenbezeichnung(en). Die Spalte(n), die Sie in dieser Klausel angeben, sind die Partitionen/Gruppen, in die die Ergebnisse der Fensterfunktion gruppiert werden.

Die folgenden Beispiele sollen dies verdeutlichen. Wir wissen, dass Sie sich nicht alles auf Anhieb merken können, also halten Sie unser SQL Fensterfunktionen Cheat Sheet in der Nähe, während wir die Beispiele durchgehen. Es ist eine praktische Erinnerung an die verschiedenen Fensterfunktionen und ihre Syntax.

PARTITION BY Beispiele

Der Beispieldatensatz besteht aus einer Tabelle, den Mitarbeitern. Hier sind die Spalten:

• id - Die ID des Mitarbeiters.
• first_name - Der Vorname des Mitarbeiters.
• last_name - Der Nachname des Angestellten.
• job_title - Die Berufsbezeichnung des Mitarbeiters.
• department - Die Abteilung des Mitarbeiters.
• date_of_employment - Das Datum, an dem die Beschäftigung des Mitarbeiters begann.
• salary - Das Gehalt des Angestellten.

Werfen Sie einen Blick auf die Tabellendaten, bevor wir mit dem Schreiben des Codes beginnen:

Wenn Sie Ihre eigenen SQL-Abfragen schreiben möchten, finden Sie hier den Code zur Erstellung dieses Datensatzes.

Verwendung von OVER (PARTITION BY)

Jetzt ist es an der Zeit, dass wir Ihnen anhand von zwei Beispielen zeigen, wie PARTITION BY funktioniert.

Beispiel 1

Im ersten Beispiel geht es darum, die Gehälter der Mitarbeiter und das Durchschnittsgehalt für jede Abteilung anzuzeigen. Wenn Sie gut aufgepasst haben, wissen Sie bereits, wie PARTITION BY uns hier helfen kann:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 AVG(salary) OVER (PARTITION BY department) AS average_salary_by_department
FROM employees;

Um den Durchschnitt zu berechnen, müssen Sie die Aggregatfunktion AVG() verwenden. Schreiben Sie die Spalte salary in die Klammern. Dies ist zunächst eine gewöhnliche Aggregatfunktion. Um sie in eine Fensteraggregatfunktion umzuwandeln, schreiben Sie die Klausel OVER().

Denken Sie daran, dass wir nicht den Gesamtdurchschnitt (d. h. für das gesamte Unternehmen) benötigen, sondern den Durchschnitt nach Abteilung. Um diese Metrik zu erhalten, fügen Sie die Spalte Abteilung in die PARTITION BY Klausel ein.

Ergibt dies die gewünschte Ausgabe? Schauen wir mal!

Sie können sehen, dass die Ausgabe alle Mitarbeiter und ihre Gehälter auflistet. Für die IT-Abteilung beträgt das Durchschnittsgehalt 7.636,59. Dieser Wert wird für alle IT-Mitarbeiter wiederholt.

Wenn wir zu den Mitarbeitern einer anderen Abteilung kommen, ändert sich der Durchschnittswert. In diesem Fall sind es 6.418,12 im Bereich Marketing. Nach dieser Logik liegt das Durchschnittsgehalt im Risikomanagement bei 6.760,01.

Wie Sie sehen können, hat PARTITION BY die Fensterfunktion angewiesen, den Abteilungsdurchschnitt zu berechnen.

Was ist der Unterschied zu GROUP BY? Schauen wir uns an, was passiert, wenn wir das Durchschnittsgehalt nach Abteilung mit GROUP BY berechnen.

Wie Sie sehen können, erhalten Sie alle dieselben Durchschnittsgehälter nach Abteilungen. Ein großer Unterschied ist jedoch, dass Sie nicht das Gehalt des einzelnen Mitarbeiters erhalten. Sie können diesen Unterschied näher erläutern, indem Sie einen Artikel über den Unterschied zwischen PARTITION BY und GROUP BY lesen.

Beispiel #2

Jetzt möchten wir die Gehälter aller Mitarbeiter zusammen mit dem höchsten Gehalt nach Stellenbezeichnung anzeigen.

Die Abfrage ist der vorherigen Abfrage sehr ähnlich. Die einzigen beiden Änderungen sind die Aggregatfunktion und die Spalte in PARTITION BY.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 MAX(salary) OVER (PARTITION BY job_title) AS max_salary_by_job_title
FROM employees;

Dieses Mal verwenden wir die Aggregatfunktion MAX() und partitionieren die Ausgabe nach Stellenbezeichnung.

Hier ist das Ergebnis:

Werfen Sie einen Blick auf die ersten beiden Zeilen. Bob Mendelsohn und Frances Jackson sind Datenanalysten, die im Risikomanagement bzw. im Marketing arbeiten. Die Tabelle zeigt ihre Gehälter und das höchste Gehalt für diese Position. Es ist 5.412,47, das Gehalt von Bob Mendelsohn.

Die gleiche Logik gilt auch für die übrigen Ergebnisse. Wenn es nur eine einzige Stellenbezeichnung gibt, sind das Gehalt des Mitarbeiters und das höchste Gehalt für diese Stellenbezeichnung natürlich identisch. Das ist der Fall für den Dateningenieur und den Systemanalytiker.

Dieses Beispiel kann auch die Grenzen von GROUP BY aufzeigen.

Der folgende Code zeigt das höchste Gehalt für die Stellenbezeichnung an:

SELECT job_title,
	 MAX(salary) AS max_salary_by_job_title
FROM employees
GROUP BY job_title;

Und hier ist die Ausgabe:

Ja, die Gehälter sind dieselben wie bei PARTITION BY. Aber mit diesem Ergebnis haben Sie keine Ahnung, wie hoch das Gehalt der einzelnen Mitarbeiter ist und wer das höchste Gehalt hat.

Verwendung von OVER (ORDER BY)

Die Klausel ORDER BY ist eine weitere Unterklausel der Fensterfunktion. Sie ordnet die Daten innerhalb einer Partition oder, wenn die Partition nicht definiert ist, den gesamten Datensatz.

Wenn wir von Ordnung sprechen, meinen wir nicht die Ausgabe. Wenn sie mit Fensterfunktionen verwendet wird, definiert die ORDER BY Klausel die Reihenfolge, in der eine Fensterfunktion ihre Berechnungen durchführt.

ORDER BY kann mit oder ohne PARTITION BY verwendet werden.

Schauen wir uns zunächst an, wie es ohne PARTITION BY funktioniert. Wir werden sie verwenden, um die Daten der Mitarbeiter anzuzeigen und sie nach ihrem Beschäftigungsdatum zu ordnen. Die Rangfolge wird vom frühesten zum spätesten Datum erstellt.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 date_of_employment,
	 RANK() OVER (ORDER BY date_of_employment ASC) AS employment_date_rank
FROM employees;

Die Fensterfunktion, die wir jetzt verwenden, ist RANK(). Sie ist eine der Funktionen, die für die Rangfolge der Daten verwendet werden. Auch hier ist die Klausel OVER() obligatorisch.

Die Klausel ORDER BY weist die Ranking-Funktion an, Ränge nach dem Datum der Beschäftigung in absteigender Reihenfolge zu vergeben.

Führen Sie die Abfrage aus und Sie erhalten diese Ausgabe:

Alle Arbeitnehmer werden nach ihrem Beschäftigungsdatum eingestuft. Der erste Beschäftigte steht an erster Stelle und der letzte an zehnter Stelle.

Verwendung von OVER (PARTITION BY ORDER BY)

Wie wir bereits erwähnt haben, können PARTITION BY und ORDER BY auch gleichzeitig verwendet werden. Schauen wir uns ein paar Beispiele an.

Beispiel 1

Stellen Sie sich vor, Sie müssen die Mitarbeiter in jeder Abteilung nach ihrem Gehalt ordnen. Wie würden Sie das machen?

Hier ist die Lösung:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Wir verwenden wieder die Funktion RANK() window. In der Klausel OVER() müssen die Daten nach Abteilungen aufgeteilt werden. Um die Mitarbeiter zu sortieren, verwenden Sie die Spalte Gehalt in ORDER BY und sortieren die Datensätze in absteigender Reihenfolge.

Schauen wir uns an, was diese Abfrage bewirkt:

In der IT-Abteilung hat Carolina Oliveira das höchste Gehalt. Danach folgen Ines Owen und Walter Tyson, und der letzte ist Sean Rice. Sie sind alle in der entsprechenden Reihenfolge aufgeführt.

Wenn die Fensterfunktion zur nächsten Abteilung kommt, setzt sie die Rangfolge zurück und beginnt von vorne. So wird Franck Monteblanc am höchsten bezahlt, während Simone Hill und Frances Jackson an zweiter bzw. dritter Stelle stehen.

Das Gleiche geschieht mit den Mitarbeitern des Risikomanagements.

Beispiel #2

Lassen Sie uns dies an einem etwas anderen Beispiel üben. Wir wollen immer noch eine Rangfolge der Mitarbeiter nach Gehalt erstellen. Diesmal aber nicht nach der Abteilung, sondern nach der Stellenbezeichnung.

So wird es gemacht.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY job_title ORDER BY salary DESC) AS salary_rank
FROM employees;

Die Daten sind nun nach Stellenbezeichnung aufgeteilt. Die ORDER BY Klausel bleibt gleich: Sie sortiert immer noch in absteigender Reihenfolge nach Gehalt.

Dies ist die Ausgabe der Abfrage:

Die Logik ist dieselbe wie im vorherigen Beispiel. In diesem Beispiel gibt es maximal zwei Mitarbeiter mit der gleichen Berufsbezeichnung, so dass die Rangfolge nicht weiter geht.

Bob Mendelsohn ist der höchstbezahlte der beiden Datenanalysten. Dann gibt es nur Rang 1 für den Dateningenieur, weil es nur einen Mitarbeiter mit dieser Berufsbezeichnung gibt. Der Rest der Daten wird nach der gleichen Logik sortiert.

Weitere Beispiele finden Sie in diesem Artikel über Fensterfunktionen in SQL. Und wenn Sie durch die Kenntnis der Fensterfunktionen Lust auf eine bessere Karriere bekommen, werden Sie sich freuen, dass wir die 10 wichtigsten Interviewfragen zu SQL-Fensterfunktionen für Sie beantwortet haben.

Wann sollte man PARTITION BY verwenden?

Wir haben das 'Wie' beantwortet. Die zweite wichtige Frage, die beantwortet werden muss, ist, wann Sie PARTITION BY verwenden sollten.

Es gibt zwei Hauptverwendungen. Die erste Verwendung ist, wenn Sie Daten gruppieren und einige Metriken berechnen möchten, aber auch die einzelnen Zeilen mit ihren Werten behalten wollen.

Die zweite Verwendung von PARTITION BY ist, wenn Sie Daten in zwei oder mehr Gruppen zusammenfassen und Statistiken für diese Gruppen berechnen wollen.

PARTITION BY muss Ihre Neugierde geweckt haben

PARTITION BY ist eine wunderbare Klausel, mit der man vertraut sein sollte. Sie bedeutet nicht nur, dass Sie die Fensterfunktionen kennen, sondern erweitert auch Ihre Möglichkeiten zur Berechnung von Metriken, indem sie über die in den Fensterfunktionen verwendeten obligatorischen Klauseln hinausgeht.

Wollen Sie Ihre Neugierde befriedigen, was Fensterfunktionen und PARTITION BY sonst noch alles können? Der Fensterfunktionen Kurs wartet auf Sie!