Was ist die MySQL OVER-Klausel?

Wenn Sie Fensterfunktionen in MySQL lernen wollen, müssen Sie die OVER-Klausel verstehen. In diesem Artikel erklären wir anhand von Beispielen aus der Praxis, was die OVER-Klausel in MySQL ist, wie sie funktioniert und warum sie so toll ist.

Im Jahr 2018 hat MySQL eine neue Funktion eingeführt: Fensterfunktionen, auf die über die OVER -Klausel zugegriffen wird. Fensterfunktionen sind eine äußerst leistungsfähige Ressource, die in fast allen SQL-Datenbanken verfügbar ist. Sie führen eine bestimmte Berechnung (z. B. Summe, Anzahl, Durchschnitt usw.) an einer Reihe von Zeilen durch; diese Reihe von Zeilen wird als "Fenster" bezeichnet und durch die MySQL-Klausel OVER definiert.

In diesem Artikel wird erläutert, wie die OVER-Klausel von MySQL in verschiedenen Szenarien verwendet werden kann. Außerdem lernen Sie mehrere Fensterfunktionen kennen. Ich hoffe, dass Sie nach der Lektüre dieses Artikels davon überzeugt sind, dass es sich lohnt, Window-Funktionen zu lernen. Sie werden auch die Grundlagen kennen, wie Sie sie in Ihren Abfragen anwenden können!

Fensterfunktionen: Eine sehr gefragte Funktion

Fensterfunktionen gibt es in den meisten großen Datenbanken schon seit geraumer Zeit, aber bis 2018 waren sie in MySQL nicht verfügbar. Um MySQL auf dem neuesten Stand zu halten, wurden die Fensterfunktionen in MySQL 8.02 eingeführt. Wenn Sie vorhaben, mit MySQL 8 zu arbeiten, lohnt es sich, Window-Funktionen und die OVER-Klausel zu lernen, da sie sehr leistungsfähig sind.

Wann würden Sie Fensterfunktionen verwenden? Es gibt viele Fälle, in denen sie nützlich sind, z.B. bei der Berechnung des Durchschnittsgehalts für eine bestimmte Gruppe von Mitarbeitern. In diesem Fall ist die Definition der Gruppe der zentrale Punkt; Sie wollen nicht den Durchschnitt aller Angestelltengehälter, und wenn Sie die Gruppe falsch wählen, wird das Ergebnis falsch sein. Die Definition einer Gruppe von Datensätzen ist der Grund für die OVER-Klausel: Sie gibt vor, wo die Fensterfunktion arbeiten soll.

Im Laufe dieses Artikels werden wir einige Abfragebeispiele erstellen, die auf einer Datenbank mit Datensätzen für eine Gruppe von Landwirten basieren, die Orangen produzieren. Die Landwirte teilen ihre Produktionsdaten, die in der orange_production Tabelle, die Sie unten sehen:

Der einfachste Anwendungsfall ist eine leere OVER -Klausel; das bedeutet, dass das Datensatzfenster die gesamte von der Abfrage zurückgegebene Datensatzmenge ist. Wenn unsere Landwirte zum Beispiel einen Bericht über jeden Datensatz eines Landwirts zusammen mit der Gesamtzahl der Orangenproduktion im Jahr 2017 haben möchten, würden wir diese Abfrage schreiben:

SELECT farmer_name, 
       kilos_produced, 
       SUM(kilos_produced) OVER() total_produced
 FROM  orange_production 
WHERE  crop_year = 2017

Hier konstruiert die OVER -Klausel ein Fenster, das alle von der Abfrage zurückgegebenen Datensätze enthält - mit anderen Worten: alle Datensätze für das Jahr 2017. Das Ergebnis ist:

Wie ein Schiebefensterrahmen funktioniert

Das war ein sehr einfaches Beispiel für die MySQL-Klausel OVER. Das Fenster der Datensätze war statisch (das Fenster war für alle von der Abfrage zurückgegebenen Zeilen gleich). Eine der Stärken der OVER Klausel ist jedoch die Möglichkeit, ein dynamisches Fenster von Datensätzen zu erstellen (auch gleitender Fensterrahmen genannt). Gleitende oder dynamische Fensterrahmen bedeuten, dass das Fenster der Datensätze für jede von der Abfrage zurückgegebene Zeile unterschiedlich sein kann. Außerdem wird das Fenster auf der Grundlage der aktuellen Zeile in der Abfrage erstellt, so dass sich die Zeilen im Fenster ändern können, wenn sich die aktuelle Zeile ändert.

Schauen wir uns ein Beispiel für ein gleitendes Fenster an. Angenommen, unsere Landwirte möchten ihre eigene Produktion zusammen mit der Gesamtproduktion derselben Orangensorte sehen.

SELECT farmer_name, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

Die Klausel OVER(PARTITION BY orange_variety) erstellt Fenster, indem sie alle Datensätze mit demselben Wert in der Spalte orange_variety gruppiert. So erhalten wir zwei Fenster: "Golden" und "SuperSun". In der Tabelle unten ist jedes Fenster in einer anderen Farbe dargestellt:

Jetzt können Sie das Ergebnis der Abfrage sehen:

Beachten Sie, dass die Spalte Total Same Variety (ganz rechts) die Produktion für alle Jahre enthält. Vielleicht zieht es jeder Landwirt vor, seine Produktion mit der Gesamtproduktion für dieselbe Sorte im selben Jahr zu vergleichen. Dieser Vergleich ermöglicht es ihm, seinen Anteil an der Produktionsrate zu sehen. Zu diesem Zweck müssen wir die Spalte crop_year zur Klausel PARTITION BY hinzufügen. Die Abfrage wird wie folgt aussehen:

SELECT farmer, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
   FROM orange_production 

Die Klausel OVER(PARTITION BY orange_variety, crop_year) erstellt Fenster, indem sie alle Datensätze mit demselben Wert in den Spalten orange_variety und crop_year gruppiert. Unten haben wir wieder verschiedene Farben verwendet, um die Fenster der Zeilen anzuzeigen, die durch diese OVER Klausel erstellt wurden:

Und das sind die Abfrageergebnisse:

Wenn Sie sich eingehender mit den MySQL-Fensterfunktionen befassen möchten, empfehle ich Ihnen den Kurs LearnSQL.de's Fensterfunktionen, wo Sie eine vollständige Beschreibung dieses Themas und mehrere Beispiele finden können. Wenn Sie Fensterfunktionen häufig benutzen werden, ist dieser Spickzettel - eine super-komplette Kurzanleitung zu Fensterfunktionen - sehr hilfreich.

Aber lassen Sie uns zunächst mit unserer eigenen Erkundung von OVER und Fensterfunktionen in MySQL fortfahren.

Lernen der MySQL OVER-Klausel anhand eines Beispiels

In diesem Abschnitt werden wir mehrere Beispielabfragen untersuchen, die verschiedene Verwendungsmöglichkeiten der OVER -Klausel in MySQL aufzeigen.

Zunächst verwenden wir die Unterklausel ORDER BY in der Klausel OVER. ORDER BY erzeugt ein Fenster mit den Datensätzen, die nach einem bestimmten Kriterium geordnet sind. Einige Funktionen (wie SUM(), LAG(), LEAD() und NTH_VALUE()) können je nach der Reihenfolge der Zeilen im Fenster unterschiedliche Ergebnisse liefern. Nehmen wir an, dass Bauer Pierre seine kumulierte Produktion über die Jahre hinweg wissen möchte:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
 FROM  orange_production
WHERE  farmer = ‘Pierre’

Die Fensterfunktion SUM(kilos_produced) OVER(ORDER BY crop_year) arbeitet mit einem geordneten Fenster. Und sie berücksichtigt nur die aktuelle und die vorherige Zeile (d. h. die aktuellen und die vorherigen Werte von crop_year ). Das Ergebnis dieser kumulativen SUM() können wir in der Ergebnistabelle sehen:

Im nächsten Beispiel werden wir zwei Unterklauseln (PARTITION BY und ORDER BY) in der Klausel OVER kombinieren. Nehmen wir an, die Landwirte wollen einen Bericht, der die Gesamterzeugung jedes Landwirts in jedem Jahr und die Gesamterzeugung der Vorjahre zeigt. Dann müssen wir nach der Spalte farmer partitionieren und nach crop_year ordnen:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
   FROM orange_production

Die folgende Abbildung zeigt die nach farmer_name partitionierten Fenster in verschiedenen Farben; beachten Sie, dass die Zeilen in jedem Fenster nach crop_year geordnet sind.

Der Ausdruck SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) ordnet die Zeilen in der Partition (die auf dem Wert farmer basiert) anhand der Werte von crop_year. Sie können dies in der Ergebnistabelle sehen:

Weitere Informationen zur Kombination der Klauseln PARTITION BY und ORDER BY finden Sie in dem Artikel SQL Fensterfunktionen nach Erläuterung.

Positionalfunktionen für geordnete Fenster verwenden

Sie sehen also, wie wichtig es ist, Zeilen innerhalb eines Fensters anordnen zu können. Tatsächlich funktionieren einige MySQL-Fensterfunktionen nur bei geordneten Fenstern. Die Funktion LAG() erlaubt es uns zum Beispiel, einen Spaltenwert aus der vorherigen Zeile (bezogen auf die aktuelle Zeile) in einem geordneten Fenster zu erhalten.

Angenommen, wir wollen einen Bericht erstellen, der die Produktion des Vorjahres mit der des laufenden Jahres vergleicht. Dazu verwenden wir die Funktion LAG() für ein nach crop_year geordnetes Fenster:

SELECT farmer, 
       crop_year,
       kilos_produced AS current_year_production, 
       LAG(kilos_produced) OVER(PARTITION BY farmer 
ORDER BY crop_year)AS previous_year_production
  FROM orange_production

Die Funktion LAG() kann jeden beliebigen Spaltenwert aus der Zeile vor der aktuellen Zeile zurückgeben, wie das folgende Abfrageergebnis zeigt:

Die Funktion LEAD() gibt einen beliebigen Spaltenwert aus der Zeile nach der aktuellen Zeile zurück. Außerdem gibt die Funktion NTH_VALUE() die Zeile an einer beliebigen Position in einem geordneten Fenster zurück (z.B. erste, zweite, letzte). Mit diesen MySQL-Fensterfunktionen können Sie leicht komplexe Berichte erstellen.

Lassen Sie uns eine mathematische Operation mit Hilfe der MySQL-Fensterfunktionen ausprobieren. Nehmen wir an, die Landwirte möchten einen Bericht mit dem Produktionsdelta in Bezug auf das vergangene Jahr. Wir können die Funktion LAG() verwenden, um die Differenz zwischen der Produktion des Vorjahres und des laufenden Jahres zu berechnen:

SELECT farmer, 
       crop_year,
       kilos_produced current_year_production, 
       kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer 
                         ORDER BY crop_year) AS production_delta
   FROM orange_production

Der Ausdruck kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) berechnet das Produktionsdelta zwischen dem Vorjahr und dem aktuellen Jahr, wie wir in den Ergebnissen sehen können:

Für diejenigen Leser, die sich eingehender mit Positionsfunktionen beschäftigen wollen, empfehle ich den Artikel Common SQL Fensterfunktionen: Positional Functions, der mehrere Beispiele für diese interessante Art von Fensterfunktionen enthält.

Mehr über MySQL OVER und Fensterfunktionen

In diesem Artikel haben wir mehrere Beispiele für die Verwendung der MySQL-Klausel OVER behandelt, von sehr einfachen bis zu ziemlich komplexen Anwendungen. Für die Verwendung von Fensterfunktionen ist es wichtig zu wissen, wie OVER funktioniert und welche Zeilen es in das Fenster einschließt.

Es gibt viele MySQL-Fensterfunktionen, mit denen Sie experimentieren können: AVG(), MIN(), MAX(), LAG(), LEAD() und NTH_VALUE(). Sie alle verwenden die OVER-Klausel auf die gleiche Weise, wie wir es gerade erklärt haben.

Schließlich empfehle ich den Lesern, die mehr über MySQL-Fensterfunktionen lernen wollen, den interaktiven LearnSQL-Kurs Fensterfunktionen. Sie können mehr darüber in unserem Beitrag SQL-Kurs des Monats - Fensterfunktionen lesen . In diesem Kurs finden Sie ausführliche Erklärungen und viele Beispiele für die Verwendung verschiedener Fensterfunktionen. Und denken Sie daran: Wenn Sie Ihre MySQL-Kenntnisse verbessern, erhöhen Sie Ihr Vermögen!