Die Rolle der neuen Feature-Window-Funktionen von MySQL 8

Zu den neuen Funktionen in MySQL 8.0 gehören:

• Vollständige, sofort einsatzbereite Unterstützung für Unicode 9.0
• Unterstützt Fensterfunktionen und rekursive SQL-Syntax, was in der Vergangenheit beim Schreiben solcher Abfragen unmöglich oder schwierig war
• Verbesserte Unterstützung für native JSON-Daten und Dokumentspeicherfunktionen
• Bei der Veröffentlichung von MySQL 8.0 wurden mehrere Versionsnummern übersprungen (beginnend mit 5.5). Da 6.0 geändert wurde und 7.0 verwendet wurde, um die Cluster-Version von MySQL beizubehalten, wurde die Versionsnummer 8.0 verwendet.

1. Problem

MySQL 8.0.2 fügt eine neue wichtige Funktion hinzu - die Fensterfunktion

Welches konkrete Problem löst diese Funktion? Sehen wir uns zunächst ein SQL-Abfrageszenario an, um zu sehen, wie wir es normalerweise tun, und dann, wie wir Fensterfunktionen verwenden können, um es bequemer zu lösen.

(1) Testtabellen und Daten vorbereiten

Erstellen Sie eine einfache Filminformationstabelle mit den folgenden Feldern:

AUSWEIS
Erscheinungsjahr
category_id (Kategorie-ID)
Bewertung
TABELLE ERSTELLEN Filme (
 Ich würde int(11),
 Erscheinungsjahr int(11),
 Kategorie_ID int(11),
 Bewertung Dezimalzahl (3,2)
)

Testdaten einfügen

in Filme2-Werte einfügen
(1,2015,1,8.00),
(2,2015,2,8.50),
(3,2015,3,9.00),
(4,2016,2,8.20),
(5,2016,1,8.40),
(6,2017,2,7.00);

Die Gesamtform ist wie folgt

(2) Abfragevoraussetzungen

Abfrage der durchschnittlichen Punktzahl für jedes Jahr und Anforderung, dass die durchschnittliche Punktzahl für dieses Jahr nach jedem Datensatz angezeigt wird

Beispielsweise gibt es im Jahr 2015 drei Datensätze mit Punktzahlen von 8,00, 8,50 und 9,00 und die Durchschnittspunktzahl beträgt 8,5. Im Jahr 2016 gibt es zwei Datensätze mit einer Durchschnittspunktzahl von 8,3. Im Jahr 2017 gibt es einen Datensatz mit einer Durchschnittspunktzahl von 7,00.

Das Endergebnis sieht wie folgt aus:

Wir können eine Unterabfrage verwenden, um die Durchschnittspunktzahl für jedes Jahr zu berechnen und dann „Join“ verwenden, um die Ergebnisse wieder miteinander zu verknüpfen.

WÄHLEN
 f.id, f.Veröffentlichungsjahr, 
 f.rating, Jahre.Jahr_Durchschnitt
VON Filme f
LINKS VERBINDEN (
 Wählen Sie f.Veröffentlichungsjahr aus, 
  AVG(Bewertung) AS year_avg
 VON Filme f
 GROUP BY f.Veröffentlichungsjahr
) Jahre 
ON f.Releasejahr = Jahre.Releasejahr

Ist das nicht ein bisschen kompliziert? Schauen wir uns an, wie Fensterfunktionen gehandhabt werden.

2. Lösung mit Fensterfunktionen

Was sind Fensterfunktionen?

Fensterfunktionen dienen zur Berechnung eines Datensatzes. Im Gegensatz zu „group by“ geben sie keine einzelne Ergebniszeile aus, sondern sind jedem Datensatz zugeordnet.

Syntaxbeispiel:

WÄHLEN
Funktionsname OVER ( Fensterdefinition )
AUS (...)

Window_definition ist die zu berechnende Datensatzmenge, wie ein kleines Fenster, das einen Teil der gesamten Datenmenge anzeigt.

function_name gibt an, welche Berechnung für den Datensatz im Fenster durchgeführt werden soll

Wenn wir auf die obige Abfrage zurückblicken, müssen wir die durchschnittliche Bewertung aller Filme in jedem Jahr berechnen. Dazu verwenden wir eine Fensterfunktion.

WÄHLEN
 f.id, f.Veröffentlichungsjahr, 
 f.Kategorie_ID, f.Bewertung,
 AVG(Bewertung) ÜBER 
 (PARTITION NACH Release-Jahr) AS year_avg
VON Filme f

Der Teil window_definition verwendet die Klausel PARTITION BY, die die Datenbank anweist, den resultierenden Datensatz in kleinere Teile aufzuteilen und dabei dasselbe release_year zusammenzusetzen. Die Funktion AVG(rating) wird für alle Fensterdaten berechnet und das Ergebnis dann in jede Zeile eingetragen.

Abfragebeispiel 1

Berechnen Sie die Bewertungsrangliste jedes Films in seinem Jahr

Abfrageanweisung

WÄHLEN
 f.id, f.Veröffentlichungsjahr, 
 f.Kategorie_ID, f.Bewertung,
 RANK() ÜBER (PARTITION NACH Release_Jahr 
  ORDER BY Bewertung DESC) AS year_rank
VON Filme f

Der Teil „window_definition“ verwendet PARTITION BY, um das Fenster nach Release-Jahr zu partitionieren, und verwendet ORDER BY, um innerhalb des Fensters zu sortieren.

Die Funktion RANK() gibt die Position einer Datenzeile in diesem Fenster zurück.

Abfrageergebnisse

Abfragebeispiel 2

Sehen Sie, welchen Rang jeder Film in der Gesamtwertung einnimmt

Abfrageanweisung

WÄHLEN
 f.id, f.Veröffentlichungsjahr, 
 f.Kategorie_ID, f.Bewertung,
 RANK() ÜBER (ORDER BY Bewertung DESC) 
  AS general_rank
VON Filmen f nach ID sortieren

Die Order By-Klausel in der Hauptanweisung stellt sicher, dass der gesamte Datensatz sortiert wird.

Wenn PARTITION BY in window_definition nicht verwendet wird, wird der gesamte Ergebnisset als Fenster behandelt und ORDER BY sortiert die Daten im Fenster in absteigender Reihenfolge nach Bewertung, wobei die Daten mit der höchsten Bewertung an den Anfang gestellt werden.

Die Funktion RANK() ermittelt die Position jedes Datensatzes im Fenster

Abfrageergebnisse

3. Zusammenfassung

Fensterfunktionen sind eine erweiterte Funktion in MySQL 8.0.2, mit der sich problemlos aggregierte Berechnungen durchführen lassen, ohne den Ergebnisset tatsächlich zu aggregieren. Dies erhöht die Flexibilität und Lesbarkeit erheblich und vereinfacht die Wartung.