23rd Jun 2022 8 Leseminuten Was ist die MySQL OVER-Klausel? Ignacio L. Bisso SQL SQL lernen Fensterfunktionen Inhaltsverzeichnis Fensterfunktionen: Eine sehr gefragte Funktion Wie ein Schiebefensterrahmen funktioniert Lernen der MySQL OVER-Klausel anhand eines Beispiels Positionalfunktionen für geordnete Fenster verwenden Mehr über MySQL OVER und Fensterfunktionen 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: farmer_nameorange_varietycrop_yearnumber_of_treeskilos_producedyear_rainkilo_price PierreGolden20152400825004001.21 PierreGolden20162400510001801.35 OlekGolden20174000780002501.42 SimonSuperSun20173500750002501.05 PierreGolden20172400625002501.42 OlekGolden20184100690001501.48 SimonSuperSun20183500740001501.07 PierreGolden20182450645002001.43 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: farmer_namekilos_producedtotal_produced Olek78000215500 Simon75000215500 Pierre62500215500 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: farmer_nameorange_varietycrop_yearnumber_of_treeskilos_producedyear_rainkilo_ price PierreGolden20152400825004001.21 PierreGolden20162400510001801.35 OlekGolden20174000780002501.42 PierreGolden20172400625002501.42 OlekGolden20184100690001501.48 PierreGolden20182450645002001.43 SimonSuperSun20173500750002501.05 SimonSuperSun20183500740001501.07 Jetzt können Sie das Ergebnis der Abfrage sehen: farmer_nameorange_varietycrop_yearkilos_producedtotal_same_variety PierreGolden201582500407500 PierreGolden201651000407500 OlekGolden201778000407500 PierreGolden201762500407500 OlekGolden201869000407500 PierreGolden201864500407500 SimonSuperSun201775000149000 SimonSuperSun201874000149000 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: farmer_nameorange_varietycrop_yearnumber_of_treeskilos_producedyear_rainkilo_price PierreGolden20152400825004001.21 PierreGolden20162400510001801.35 OlekGolden20174000780002501.42 PierreGolden20172400625002501.42 OlekGolden20184100690001501.48 PierreGolden20182450645002001.43 SimonSuperSun20173500750002501.05 SimonSuperSun20183500740001501.07 Und das sind die Abfrageergebnisse: farmer_nameorange_varietycrop_yearkilos_producedtotal_same_variety_year PierreGolden20158250082500 PierreGolden20165100051000 OlekGolden201778000140500 PierreGolden201762500140500 OlekGolden201869000133500 PierreGolden201864500133500 SimonSuperSun20177500075000 SimonSuperSun20187400074000 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: farmer_namecrop_yearkilos_producedcumulative_previous_years Pierre20158250082500 Pierre201651000133500 Pierre201762500196000 Pierre201864500260500 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. farmer_nameorange_varietycrop_yearnumber_of_treeskilos_ producedyear_rainkilo_price PierreGolden20152400825004001.21 PierreGolden20162400510001801.35 PierreGolden20172400625002501.42 PierreGolden20182450645002001.43 SimonSuperSun20173500750002501.05 SimonSuperSun20183500740001501.07 OlekGolden20174000780002501.42 OlekGolden20184100690001501.48 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: farmer_ namecrop_ yearkilos_ producedcumulative_ previous_years Olek20177800078000 Olek201869000147000 Pierre20158250082500 Pierre201651000133500 Pierre201762500196000 Pierre201864500260500 Simon20177500075000 Simon201874000149000 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: farmer_namecrop_yearkilos_producedprevious_year_production Olek201778000NULL Olek20186900078000 Pierre201582500NULL Pierre20165100082500 Pierre20176250051000 Pierre20186450062500 Simon201775000NULL Simon20187400075000 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: farmer_namecrop_yearkilos_producedproduction_ delta Olek201778000NULL Olek201869000-9000 Pierre201582500NULL Pierre201651000-31500 Pierre20176250011500 Pierre2018645002000 Simon201775000NULL Simon201874000-1000 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! Tags: SQL SQL lernen Fensterfunktionen