Ein Überblick über die MySQL-Fensterfunktionen

MySQL-Fensterfunktionen sind sehr hilfreich, wenn Sie aussagekräftige Berichte mit SQL erstellen wollen. In diesem Artikel zeigen wir Ihnen die gebräuchlichsten Fensterfunktionen in MySQL und erklären, wie Sie sie verwenden können.

Mit MySQL 8.0 wurde ein neues Feature eingeführt: Fensterfunktionen. Diese Funktionen sind für Datenanalysten und alle, die Berichte mit MySQL erstellen, sehr nützlich. Mit ihnen können Sie auf einfache Weise gleitende Durchschnitte, kumulative Summen und andere Berechnungen über bestimmte Teilmengen Ihrer Daten berechnen. Und das ohne komplexe SQL-Abfragen oder temporäre Tabellen zu erstellen.

Als regelmäßiger SQL-Benutzer sind Sie bei Ihrer Arbeit vielleicht schon auf Fensterfunktionen gestoßen. Während viele Datenbanken bereits seit einiger Zeit über diese Funktionen verfügen, hinkte MySQL bis 2018 hinterher. Mit MySQL 8 ist die Plattform nun auf der Höhe der Zeit!

In diesem Artikel werden wir die Vorteile von Fensterfunktionen in MySQL untersuchen und wie sie Ihre Datenanalyse verbessern können. Wenn Sie den Einsatz von Fensterfunktionen praktisch üben möchten, sollten Sie unseren interaktiven Kurs Fensterfunktionen in MySQL 8 besuchen. Er bietet über 200 interaktive Übungen zu MySQL-Fensterfunktionen.

Was sind Fensterfunktionen in MySQL?

Eine SQL-Fensterfunktion führt Berechnungen über einen Satz von Tabellenzeilen durch, die mit der aktuellen Zeile in Beziehung stehen. Dieser Satz von Zeilen wird als Fenster oder Fensterrahmen bezeichnet - daher kommt auch der Begriff "Fensterfunktionen".

Lassen Sie uns mit einem einfachen Beispiel beginnen. Stellen Sie sich vor, Sie möchten die Summe der Werte in allen Zeilen berechnen, das Ergebnis aber in jeder Zeile anzeigen lassen. Diese Information benötigen Sie vielleicht, um bei der Datenanalyse einzelne Werte mit der Gesamtsumme zu vergleichen.

Dies ist ein Kinderspiel, wenn Sie wissen, wie man Fensterfunktionen verwendet! Das Ergebnis Ihrer Abfrage würde dann so aussehen:

Sie haben vielleicht bemerkt, dass Fensterfunktionen den Aggregatfunktionen ähnlich sind. Sie berechnen beide einen Gesamtwert für eine bestimmte Gruppe von Zeilen. Im Gegensatz zur GROUP BY Klausel klappen Fensterfunktionen in SQL jedoch keine Zeilen zusammen. Stattdessen zeigt die resultierende Tabelle sowohl einzelne als auch aggregierte Werte an. Dies kann in Berichten praktisch sein, wenn Sie gleichzeitig mit den aggregierten und nicht aggregierten Werten arbeiten müssen.

So definieren Sie eine Fensterfunktion: Die OVER()-Klausel

Fensterfunktionen werden mit der Klausel OVER() definiert:

SELECT …,
   <window_function> OVER(...),
   …
FROM …

Die OVER() Klausel weist die Datenbank an, eine Fensterfunktion zu verwenden. Die einfachste Form des Fensterrahmens ist, wenn die Klammern leer gelassen werden, wie hier: OVER(). Dies bedeutet, dass das Fenster aus allen Zeilen der Tabelle besteht.

Zusätzliche Klauseln können in die OVER() Klausel aufgenommen werden, um das Fenster weiter zu definieren. In diesem Artikel werden wir uns auf die Klauseln PARTITION BY und ORDER BY konzentrieren. Es gibt noch weitere Klauseln, die innerhalb von OVER() verwendet werden können, aber wir werden sie in diesem Artikel nicht behandeln. Wenn Sie noch tiefer eintauchen möchten, besuchen Sie unseren Kurs Fensterfunktionen in MySQL 8. Sie können sich auch diesen Artikel über MySQL-Fensterfunktionen ansehen, der einige großartige Beispiele für die Verwendung dieser Funktionen in Ihren Abfragen enthält.

Beispiele für Abfragen mit Fensterfunktionen in MySQL

Lassen Sie uns einige Beispielabfragen durchgehen, um besser zu verstehen, wo und wie Sie Fensterfunktionen einsetzen können.

In unserem Beispielszenario haben wir eine Website, auf der Benutzer an Quizspielen teilnehmen können. Es gibt verschiedene Quiz-Kategorien und die maximale Punktzahl, die die Teilnehmer erreichen können, beträgt 100.

Um die Punktzahlen der Teilnehmer zu speichern, verwendet die Website die participant Tabelle. Sie hat die folgenden Spalten:

• id - Die ID des Teilnehmers, die auch der Primärschlüssel (PK) der Tabelle ist.
• name - Der Name des Teilnehmers.
• quiz_score - Die Punktzahl des Teilnehmers.
• quiz_date - Das Datum, an dem das Quiz versucht wurde.
• quiz_category - Die Kategorie des Quiz.

Hier sehen Sie eine Handvoll Zeilen aus der Tabelle:

Jetzt, da Sie mit den Daten vertraut sind, können Sie die Fensterfunktionen verwenden! Vielleicht finden Sie dieses SQL Fensterfunktionen Cheat Sheet als schnelle Referenz, während wir die Beispiele durchgehen.

Beispiel 1: Leere OVER()-Klausel - Berechnen einer Statistik für alle Zeilen

Angenommen, wir möchten die Punktzahl jedes Teilnehmers, die Kategorie des Quiz, an dem er teilgenommen hat, und die höchste Punktzahl, die jemals in allen Quiz erreicht wurde, zurückgeben. Wir können dies mit einer leeren OVER() Klausel tun. Auf diese Weise wird unser Fenster alle Zeilen der Abfrage enthalten.

Hier ist die Abfrage, die wir ausführen würden:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER() AS max_score
FROM participant;

Das Ergebnis wird wie folgt aussehen:

Die Funktion MAX() wurde auf alle Zeilen in unserer Abfrage angewendet. Sie können sehen, dass die höchste Punktzahl 100 war, und sie wird für alle Zeilen zusammen mit den individuellen Punktzahlen angezeigt.

Sie können die leere OVER() -Klausel mit anderen Funktionen wie COUNT(), SUM(), AVG(), und anderen verwenden. Damit können Sie eine Gesamtstatistik für alle Zeilen in der Abfrage berechnen; Sie können diese Gesamtstatistik mit dem Wert in jeder einzelnen Zeile vergleichen.

Mehr über die Verwendung der OVER()-Klausel in MySQL erfahren Sie in unserem Artikel Was ist die MySQL OVER-Klausel?

Beispiel 2: OVER() mit ORDER BY - Berechnen einer Rangliste

Bei Verwendung innerhalb der OVER() -Klausel bestimmt ORDER BY die Reihenfolge, in der die Zeilen im Fensterrahmen angeordnet werden. Lassen Sie uns ein Beispiel sehen:

Wir können diese Abfrage verwenden, um eine Rangliste der Quiz-Ergebnisse zu erstellen:

SELECT 
  name, 
  quiz_score,
  quiz_category,
  RANK() OVER(ORDER BY quiz_score DESC) AS rank
FROM participant;

Die Fensterfunktion RANK() weist jeder Zeile innerhalb einer Partition einen Rang zu; dieser Rang basiert auf dem Wert eines angegebenen Ausdrucks. Die erste Zeile erhält den Rang 1, die zweite Zeile den Rang 2, usw.

Genauer gesagt, weist die Funktion RANK() jedem unterschiedlichen Wert des Ausdrucks innerhalb der Partition einen eindeutigen Rang zu. Zeilen mit demselben Wert erhalten denselben Rang, und der nächste Rang wird übersprungen. Wenn beispielsweise zwei Zeilen denselben Wert haben und den Rang 1 erhalten, wird der nächste zugewiesene Rang 3 sein und Rang 2 übersprungen. In unserem Blog erfahren Sie mehr über Ranking-Fensterfunktionen in SQL.

Hier verwenden wir die Funktion RANK(), um die Rangfolge der Quiz-Punkte der einzelnen Teilnehmer zu berechnen. Die OVER() Klausel mit der ORDER BY Klausel bestimmt die Reihenfolge, in der die RANK() Funktion angewendet wird. In diesem Fall ist die ORDER BY -Klausel auf quiz_score DESC gesetzt, was bedeutet, dass die Quiz-Punkte in absteigender Reihenfolge (vom höchsten zum niedrigsten) geordnet werden, bevor die Rangfolge berechnet wird. Die erste Zeile (mit dem höchsten Wert) erhält den Rang 1, die zweite Zeile den Rang 2, usw.

Das ist das Ergebnis des Codes:

Gut gemacht! Es ist uns gelungen, jedem Teilnehmer einen Rang zuzuweisen.

Verwenden Sie die OVER (ORDER BY) Klausel in MySQL, wenn Sie eine Funktion auf die Zeilen in einer bestimmten Reihenfolge anwenden wollen. Dies kann bei der Berechnung von laufenden Summen, gleitenden Durchschnitten und der Erstellung verschiedener Ranglisten nützlich sein.

Beispiel 3: OVER() mit PARTITION BY - Berechnen einer Statistik für jede Unterteilung

Machen wir mehr Gebrauch von der Kategorie-Spalte. Erinnern Sie sich an die erste Beispielabfrage, die wir durchgegangen sind? Für jeden Teilnehmer haben wir seine Punktzahl, die Kategorie des Quiz, an dem er teilgenommen hat, und die höchste Punktzahl, die er in allen Quiz erreicht hat, angezeigt. Dieses Mal möchten wir etwas Ähnliches machen. Anstatt jedoch die höchste Punktzahl anzuzeigen, die jemals in allen Quiz erreicht wurde, wollen wir die höchste Punktzahl anzeigen, die jemals in der Kategorie des Quiz erreicht wurde.

Hierfür benötigen wir die OVER() Klausel mit PARTITION BY. Die Partitionierung von Daten in SQL bedeutet, dass eine Reihe von Zeilen auf der Grundlage einer bestimmten Spalte oder mehrerer Spalten in kleinere Gruppen unterteilt wird. Es ist ein bisschen ähnlich wie die GROUP BY Klausel, aber die Fensterfunktionen klappen die Zeilen nicht zusammen.

Wir können diese Abfrage verwenden:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER(PARTITION BY quiz_category) 
    AS max_score_in_category
FROM participant;

Die PARTITION BY -Klausel mit der OVER() -Klausel bestimmt die Datenpartition, auf die die MAX() -Funktion angewendet wird. In diesem Fall ist die PARTITION BY -Klausel auf quiz_category gesetzt, was bedeutet, dass die Daten auf der Grundlage der Kategorie des Quiz in Partitionen aufgeteilt werden. Das ist genau das, was wir wollten!

Hier ist das Ergebnis:

Für jeden Teilnehmer wird sowohl seine individuelle Punktzahl als auch die höchste Punktzahl in seiner Kategorie angezeigt. Das klingt fair, denn das Musik-Quiz könnte schwieriger gewesen sein als das Wissenschafts-Quiz!

OVER (PARTITION BY) in MySQL ist nützlich, um kumulative Summen oder Durchschnittswerte zu berechnen, Ranglisten innerhalb von Gruppen zu erstellen, die besten oder schlechtesten Leistungen zu ermitteln und vieles mehr. Diese Klausel bietet Flexibilität und erweiterte Funktionen für SQL-Abfragen, die eine leistungsstarke Datenanalyse und -manipulation innerhalb von Teilmengen von Daten ermöglichen. Mehr über die Verwendung von SQL PARTITION BY mit OVER erfahren Sie an anderer Stelle in unserem Blog.

Das war einfach, oder? Versuchen wir mal etwas Komplizierteres!

Beispiel 4: OVER() mit PARTITION BY und ORDER BY - Berechnen einer Statistik für jede Partition in einer bestimmten Reihenfolge

Können wir sowohl PARTITION BY als auch ORDER BY gleichzeitig verwenden? Natürlich können wir das! Diese Kombination ist in vielen Situationen nützlich.

In MySQL ermöglicht die Verwendung der OVER() -Klausel zusammen mit PARTITION BY und ORDER BY die Durchführung von Berechnungen und Analysen für bestimmte Datenpartitionen, wobei die Reihenfolge, in der die Berechnungen innerhalb jeder Partition durchgeführt werden, gesteuert wird.

Die PARTITION BY -Klausel unterteilt die Ergebnismenge in verschiedene Partitionen auf der Grundlage von angegebenen Spalten oder Ausdrücken. Jede Partition wird für die Berechnung oder Analyse separat behandelt.

Die Klausel ORDER BY bestimmt bei Verwendung mit OVER() die Reihenfolge, in der die Daten in jeder Partition verarbeitet werden. Sie gibt die Spalte oder den Ausdruck an, nach dem die Daten sortiert werden sollen.

Schauen wir uns dieses Duo in Aktion an. In diesem Beispiel berechnen wir den kumulativen Durchschnitt der Quiz-Ergebnisse nach Kategorie. Ein kumulativer Durchschnitt ist der Durchschnitt einer Reihe von Werten bis zu einem bestimmten Punkt.

Dies ist die Abfrage, die wir verwenden werden:

SELECT
  name,
  quiz_date,
  quiz_score,
  quiz_category,
  ROUND(
    AVG(quiz_score) 
OVER(PARTITION BY quiz_category ORDER BY quiz_date)
  ) AS cumulative_avg
FROM participant;

Wir möchten den kumulativen Durchschnitt der Quiz-Ergebnisse nach Kategorie sehen. Um dies zu erreichen, verwenden wir PARTITION BY quiz_category, genau wie beim letzten Mal. Außerdem ist es sinnvoll, dass der kumulative Durchschnitt vom ältesten bis zum neuesten Datum berechnet wird, daher haben wir ORDER BY quiz_date verwendet. Das bedeutet, dass die Daten innerhalb jeder Partition nach dem Abfragedatum in aufsteigender Reihenfolge (vom größten zum kleinsten) sortiert werden, bevor die Funktion AVG() angewendet wird.

So sieht die Ausgabe der Abfrage aus:

Durch die gemeinsame Verwendung von PARTITION BY und ORDER BY innerhalb der Klausel OVER() wird die Durchschnittsberechnung für jede Quiz-Kategorie separat durchgeführt. Innerhalb jeder Kategorie wird sie in der Reihenfolge der Abfragedaten berechnet. Das bedeutet, dass die Durchschnittsberechnung für jede Zeile in der Ergebnismenge nur die Zeilen innerhalb derselben Testkategorie berücksichtigt und sie nach Datum ordnet. Die Spalte cumulative_avg gibt die durchschnittliche Punktzahl bis zur aktuellen Zeile für jede Quizkategorie wieder, wobei die Reihenfolge der Quizdaten berücksichtigt wird.

Sind Sie bereit, MySQL Fensterfunktionen zu üben?

Wie Sie sehen, sind die Fensterfunktionen in MySQL ein sehr mächtiges Werkzeug, mit dem Sie komplexe Berichte erstellen können. Sie können Fensterfunktionen in MySQL verwenden, um Ranglisten zu erstellen und Jahreskennzahlen, gleitende Durchschnitte und vieles mehr zu berechnen!

In diesem Artikel haben wir nur an der Oberfläche all der Möglichkeiten gekratzt, die Sie mit Fensterfunktionen nutzen können. Wenn Sie tiefer eintauchen und weitere Beispiele für ihre Verwendung kennenlernen möchten, lesen Sie diese Artikel über SQL-Fensterfunktionen und die MySQL OVER()-Klausel.

Und für weitere praktische Übungen sollten Sie sich unseren Kurs Fensterfunktionen in MySQL 8 ansehen!