Einführung und Zusammenfassung der MySQL 8.0-Fensterfunktionen

Einführung und Zusammenfassung der MySQL 8.0-Fensterfunktionen

Vorwort

Vor MySQL 8.0 war es ziemlich mühsam, Datenranglistenstatistiken zu erstellen, da es keine Fensterfunktionen wie bei anderen Datenbanken wie Oracle, SQL SERVER, PostgreSQL usw. gab. Mit der Hinzufügung von Fensterfunktionen in MySQL 8.0 stellt diese Art von Statistiken jedoch kein Problem mehr dar. Dieser Artikel stellt MySQL-Fensterfunktionen anhand häufig verwendeter Sortierbeispiele vor.

1. Vorbereitung

Erstellen von Tabellen und Testdaten

mysql> testdb verwenden;
Datenbank geändert
/* Tabelle erstellen */
mysql> Tabelle erstellen tb_score(id int Primärschlüssel auto_increment,stu_no varchar(10),Kurs varchar(50),Score Dezimal(4,1),Schlüssel idx_stuNo_course(stu_no,Kurs));
Abfrage OK, 0 Zeilen betroffen (0,03 Sek.)

mysql> Tabellen anzeigen;
+------------------+
| Tabellen_in_testdb |
+------------------+
|tb_score|
+------------------+

/* Einen neuen Stapel Testdaten hinzufügen*/
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020001','mysql',90),('2020001','C++',85),('2020003','Englisch',100),('2020002','mysql',50),('2020002','C++',70),('2020002','Englisch',99);
Abfrage OK, 6 Zeilen betroffen (0,00 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020003','mysql',78),('2020003','C++',81),('2020003','Englisch',80),('2020004','mysql',80),('2020004','C++',60),('2020004','Englisch',100);
Abfrage OK, 6 Zeilen betroffen (0,01 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020005','mysql',98),('2020005','C++',96),('2020005','Englisch',70),('2020006','mysql',60),('2020006','C++',90),('2020006','Englisch',70);
Abfrage OK, 6 Zeilen betroffen (0,01 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020007','mysql',50),('2020007','C++',66),('2020007','Englisch',76),('2020008','mysql',90),('2020008','C++',69),('2020008','Englisch',86);
Abfrage OK, 6 Zeilen betroffen (0,01 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020009','mysql',70),('2020009','C++',66),('2020009','Englisch',86),('2020010','mysql',75),('2020010','C++',76),('2020010','Englisch',81);
Abfrage OK, 6 Zeilen betroffen (0,01 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0
mysql> einfügen in tb_score(stu_no,course,score)Werte('2020011','mysql',90),('2020012','C++',85),('2020011','Englisch',84),('2020012','Englisch',75),('2020013','C++',96),('2020013','Englisch',88);
Abfrage OK, 6 Zeilen betroffen (0,01 Sek.)
Datensätze: 6 Duplikate: 0 Warnungen: 0

2. Berechnen Sie das Ranking der einzelnen Kursergebnisse

Die Punktzahlen jedes Kurses werden von hoch nach niedrig eingestuft. Zu diesem Zeitpunkt tritt das Problem auf, wie mit den gleichen Punktzahlen umzugehen ist. Im Folgenden werden unterschiedliche Fensterfunktionen verwendet, um die Anforderungen verschiedener Szenarien zu erfüllen.

ROW_NUMBER

Aus den Ergebnissen ist ersichtlich, dass bei Punktegleichheit die Rangfolge nach der Anzahl der Studierenden ermittelt wird.

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
 -> von tb_score;
+---------+---------+-------+----+
| stu_no | Kurs | Ergebnis | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90,0 | 3 |
| 2020001 | C++ | 85,0 | 4 |
| 2020012 | C++ | 85,0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76,0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66,0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60,0 | 12 |
| 2020003 | Englisch | 100,0 | 1 |
| 2020004 | Englisch | 100,0 | 2 |
| 2020002 | Englisch | 99,0 | 3 |
| 2020013 | Englisch | 88,0 | 4 |
| 2020008 | Englisch | 86,0 | 5 |
| 2020009 | Englisch | 86,0 | 6 |
| 2020011 | Englisch | 84,0 | 7 |
| 2020010 | Englisch | 81.0 | 8 |
| 2020003 | Englisch | 80,0 | 9 |
| 2020007 | Englisch | 76,0 | 10 |
| 2020012 | Englisch | 75,0 | 11 |
| 2020005 | Englisch | 70,0 | 12 |
| 2020006 | Englisch | 70,0 | 13 |
| 2020005 | MySQL | 98,0 | 1 |
| 2020001 | MySQL | 90,0 | 2 |
| 2020008 | MySQL | 90,0 | 3 |
| 2020011 | MySQL | 90,0 | 4 |
| 2020004 | MySQL | 80,0 | 5 |
| 2020003 | MySQL | 78,0 | 6 |
| 2020010 | MySQL | 75,0 | 7 |
| 2020009 | MySQL | 70,0 | 8 |
| 2020006 | MySQL | 60,0 | 9 |
| 2020002 | MySQL | 50,0 | 10 |
| 2020007 | MySQL | 50,0 | 11 |
+---------+---------+-------+----+
36 Zeilen im Set (0,00 Sek.) mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
 -> von tb_score;
+---------+---------+-------+----+
| stu_no | Kurs | Ergebnis | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90,0 | 3 |
| 2020001 | C++ | 85,0 | 4 |
| 2020012 | C++ | 85,0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76,0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66,0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60,0 | 12 |
| 2020003 | Englisch | 100,0 | 1 |
| 2020004 | Englisch | 100,0 | 2 |
| 2020002 | Englisch | 99,0 | 3 |
| 2020013 | Englisch | 88,0 | 4 |
| 2020008 | Englisch | 86,0 | 5 |
| 2020009 | Englisch | 86,0 | 6 |
| 2020011 | Englisch | 84,0 | 7 |
| 2020010 | Englisch | 81.0 | 8 |
| 2020003 | Englisch | 80,0 | 9 |
| 2020007 | Englisch | 76,0 | 10 |
| 2020012 | Englisch | 75,0 | 11 |
| 2020005 | Englisch | 70,0 | 12 |
| 2020006 | Englisch | 70,0 | 13 |
| 2020005 | MySQL | 98,0 | 1 |
| 2020001 | MySQL | 90,0 | 2 |
| 2020008 | MySQL | 90,0 | 3 |
| 2020011 | MySQL | 90,0 | 4 |
| 2020004 | MySQL | 80,0 | 5 |
| 2020003 | MySQL | 78,0 | 6 |
| 2020010 | MySQL | 75,0 | 7 |
| 2020009 | MySQL | 70,0 | 8 |
| 2020006 | MySQL | 60,0 | 9 |
| 2020002 | MySQL | 50,0 | 10 |
| 2020007 | MySQL | 50,0 | 11 |
+---------+---------+-------+----+
36 Zeilen im Satz (0,00 Sek.)

DICHTER_RANG

Um bei gleichen Punktzahlen eine einheitliche Rangfolge zu erreichen, kann man die Funktion DENSE_RANK verwenden, das Ergebnis ist folgendes:

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn 
 -> von tb_score; 
+---------+---------+-------+----+
| stu_no | Kurs | Ergebnis | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96,0 | 1 |
| 2020006 | C++ | 90,0 | 2 |
| 2020001 | C++ | 85,0 | 3 |
| 2020012 | C++ | 85,0 | 3 |
| 2020003 | C++ | 81.0 | 4 |
| 2020010 | C++ | 76,0 | 5 |
| 2020002 | C++ | 70,0 | 6 |
| 2020008 | C++ | 69.0 | 7 |
| 2020007 | C++ | 66.0 | 8 |
| 2020009 | C++ | 66.0 | 8 |
| 2020004 | C++ | 60,0 | 9 |
| 2020003 | Englisch | 100,0 | 1 |
| 2020004 | Englisch | 100,0 | 1 |
| 2020002 | Englisch | 99,0 | 2 |
| 2020013 | Englisch | 88,0 | 3 |
| 2020008 | Englisch | 86,0 | 4 |
| 2020009 | Englisch | 86,0 | 4 |
| 2020011 | Englisch | 84,0 | 5 |
| 2020010 | Englisch | 81,0 | 6 |
| 2020003 | Englisch | 80,0 | 7 |
| 2020007 | Englisch | 76,0 | 8 |
| 2020012 | Englisch | 75,0 | 9 |
| 2020005 | Englisch | 70,0 | 10 |
| 2020006 | Englisch | 70,0 | 10 |
| 2020005 | MySQL | 98,0 | 1 |
| 2020001 | MySQL | 90,0 | 2 |
| 2020008 | MySQL | 90,0 | 2 |
| 2020011 | MySQL | 90,0 | 2 |
| 2020004 | MySQL | 80,0 | 3 |
| 2020003 | MySQL | 78,0 | 4 |
| 2020010 | MySQL | 75,0 | 5 |
| 2020009 | MySQL | 70,0 | 6 |
| 2020006 | MySQL | 60,0 | 7 |
| 2020002 | MySQL | 50,0 | 8 |
| 2020007 | MySQL | 50,0 | 8 |
+---------+---------+-------+----+
36 Zeilen im Satz (0,00 Sek.)

RANG

Das Ergebnis von DENSE_RANK ist, dass bei gleichen Punktzahlen die Rangfolge gleich ist, die nächste Rangfolge jedoch unmittelbar nach der vorherigen Rangfolge liegt. Wenn zwei Personen den ersten Platz teilen und der nächste, den ich möchte, der dritte ist, können Sie die RANK-Funktion verwenden, um Folgendes zu erreichen:

mysql> select stu_no,course,score, rank()over(partition by course order by score desc ) rn 
 -> von tb_score;
+---------+---------+-------+----+
| stu_no | Kurs | Ergebnis | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 1 |
| 2020006 | C++ | 90,0 | 3 |
| 2020001 | C++ | 85,0 | 4 |
| 2020012 | C++ | 85,0 | 4 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76,0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66,0 | 10 |
| 2020009 | C++ | 66.0 | 10 |
| 2020004 | C++ | 60,0 | 12 |
| 2020003 | Englisch | 100,0 | 1 |
| 2020004 | Englisch | 100,0 | 1 |
| 2020002 | Englisch | 99,0 | 3 |
| 2020013 | Englisch | 88,0 | 4 |
| 2020008 | Englisch | 86,0 | 5 |
| 2020009 | Englisch | 86,0 | 5 |
| 2020011 | Englisch | 84,0 | 7 |
| 2020010 | Englisch | 81.0 | 8 |
| 2020003 | Englisch | 80,0 | 9 |
| 2020007 | Englisch | 76,0 | 10 |
| 2020012 | Englisch | 75,0 | 11 |
| 2020005 | Englisch | 70,0 | 12 |
| 2020006 | Englisch | 70,0 | 12 |
| 2020005 | MySQL | 98,0 | 1 |
| 2020001 | MySQL | 90,0 | 2 |
| 2020008 | MySQL | 90,0 | 2 |
| 2020011 | MySQL | 90,0 | 2 |
| 2020004 | MySQL | 80,0 | 5 |
| 2020003 | MySQL | 78,0 | 6 |
| 2020010 | MySQL | 75,0 | 7 |
| 2020009 | MySQL | 70,0 | 8 |
| 2020006 | MySQL | 60,0 | 9 |
| 2020002 | MySQL | 50,0 | 10 |
| 2020007 | MySQL | 50,0 | 10 |
+---------+---------+-------+----+
36 Zeilen im Satz (0,01 Sek.)

Dadurch werden verschiedene Sortieranforderungen erreicht.

NTILE

Die Funktion der NTILE-Funktion besteht darin, jede Gruppe zu bewerten und dann die entsprechende Gruppe in N Gruppen aufzuteilen, zum Beispiel

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group from tb_score;
+---------+---------+-------+----+----------+
| stu_no | Kurs | Ergebnis | rn | rn_group |
+---------+---------+-------+----+----------+
| 2020005 | C++ | 96.0 | 1 | 1 |
| 2020013 | C++ | 96.0 | 1 | 1 |
| 2020006 | C++ | 90,0 | 3 | 1 |
| 2020001 | C++ | 85,0 | 4 | 1 |
| 2020012 | C++ | 85,0 | 4 | 1 |
| 2020003 | C++ | 81.0 | 6 | 1 |
| 2020010 | C++ | 76.0 | 7 | 2 |
| 2020002 | C++ | 70,0 | 8 | 2 |
| 2020008 | C++ | 69.0 | 9 | 2 |
| 2020007 | C++ | 66,0 | 10 | 2 |
| 2020009 | C++ | 66,0 | 10 | 2 |
| 2020004 | C++ | 60,0 | 12 | 2 |
| 2020003 | Englisch | 100,0 | 1 | 1 |
| 2020004 | Englisch | 100,0 | 1 | 1 |
| 2020002 | Englisch | 99,0 | 3 | 1 |
| 2020013 | Englisch | 88,0 | 4 | 1 |
| 2020008 | Englisch | 86,0 | 5 | 1 |
| 2020009 | Englisch | 86,0 | 5 | 1 |
| 2020011 | Englisch | 84,0 | 7 | 1 |
| 2020010 | Englisch | 81.0 | 8 | 2 |
| 2020003 | Englisch | 80,0 | 9 | 2 |
| 2020007 | Englisch | 76,0 | 10 | 2 |
| 2020012 | Englisch | 75,0 | 11 | 2 |
| 2020005 | Englisch | 70,0 | 12 | 2 |
| 2020006 | Englisch | 70,0 | 12 | 2 |
| 2020005 | MySQL | 98,0 | 1 | 1 |
| 2020001 | MySQL | 90,0 | 2 | 1 |
| 2020008 | MySQL | 90,0 | 2 | 1 |
| 2020011 | MySQL | 90,0 | 2 | 1 |
| 2020004 | MySQL | 80,0 | 5 | 1 |
| 2020003 | MySQL | 78,0 | 6 | 1 |
| 2020010 | MySQL | 75,0 | 7 | 2 |
| 2020009 | MySQL | 70,0 | 8 | 2 |
| 2020006 | MySQL | 60,0 | 9 | 2 |
| 2020002 | MySQL | 50,0 | 10 | 2 |
| 2020007 | MySQL | 50,0 | 10 | 2 |
+---------+---------+-------+----+----------+
36 Zeilen im Satz (0,01 Sek.)

3. Zusammenfassung der Fensterfunktionen

Es gibt noch viele weitere Fensterfunktionen in MySQL. Dieser Artikel listet einige davon auf und Sie können sie selbst testen.

Kategorie Funktion veranschaulichen
Sortierung ROW_NUMBER Weisen Sie jeder Zeile in der Tabelle eine Sequenznummer zu und geben Sie die Gruppierungs- (oder Nicht-) und Sortierfelder an
DICHTER_RANG Weisen Sie jeder Zeile in jeder Gruppe basierend auf dem Sortierfeld eine Sequenznummer zu. Wenn die Rangfolgewerte gleich sind, sind die Seriennummern gleich und es gibt keine Lücken in den Seriennummern (z. B. 1,1,2,3).
RANG Weisen Sie jeder Zeile in jeder Gruppe basierend auf dem Sortierfeld eine Sequenznummer zu. Wenn die Rangfolgewerte gleich sind, sind die Seriennummern gleich, aber es gibt Lücken in den Seriennummern (z. B. 1,1,3,4).
NTILE Gemäß dem Sortierfeld wird jede Gruppe gemäß der Sortierung des angegebenen Felds in entsprechende Gruppen unterteilt.
verteilt PERCENT_RANK Berechnet die Prozentränge für jede Gruppe oder Zeile in einem Ergebnissatz.
CUME_DIST Berechnen Sie die kumulative Verteilung eines Wertes in einem Satz geordneter Daten
Vorher und Nachher FÜHREN Gibt den Wert der N-ten Zeile nach der aktuellen Zeile in der Gruppe zurück. Wenn keine entsprechende Zeile vorhanden ist, wird NULL zurückgegeben. Wenn beispielsweise N = 1 ist, ist der Wert, der dem ersten Platz entspricht, der zweite Platz und das Ergebnis des letzten Platzes ist NULL
VERZÖGERUNG Gibt den Wert der Zeile N Zeilen vor der aktuellen Zeile in der Gruppe zurück. Wenn keine entsprechende Zeile vorhanden ist, wird NULL zurückgegeben. Wenn beispielsweise N = 1 ist, ist der Wert, der der ersten Stelle entspricht, NUL und der Wert an der letzten Stelle ist der vorletzte Wert.
Anfang und Ende ERSTER_WERT Gibt den Wert des Felds (oder Ausdrucks) zurück, das dem ersten Platz in jeder Gruppe entspricht. In diesem Artikel kann es sich beispielsweise um den Wert eines beliebigen Felds handeln, wie etwa die Punktzahl des ersten Platzes, die Studenten-ID usw.
LETZTER_WERT Gibt den Wert des Felds (oder Ausdrucks) zurück, das der letzten Person in jeder Gruppe entspricht. In diesem Artikel kann es sich beispielsweise um den Wert eines beliebigen Felds handeln, beispielsweise um die Punktzahl oder die Studenten-ID der letzten Person.
NTH_WERT

Gibt den Wert des entsprechenden Felds (oder Ausdrucks) zurück, das in jeder Gruppe an N-ter Stelle steht, aber der entsprechende Wert der Zeile kleiner als N ist NULL

Dies ist die Zusammenfassung der wichtigsten Fensterfunktionen in MySQL. Es wird empfohlen, sie zu üben. Darüber hinaus haben viele Leute die Implementierung von Sortiermethoden für MySQL 5.7 und frühere Versionen zusammengefasst und es wird auch empfohlen, sie in die Praxis umzusetzen.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Einführungsübungen und Zusammenfassungen der MySQL 8.0-Fensterfunktionen. Weitere relevante Übungsinhalte zu MySQL 8.0-Fensterfunktionen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!

Das könnte Sie auch interessieren:
  • Mysql8.0 verwendet Fensterfunktionen, um Sortierprobleme zu lösen
  • Fallanalyse von SQL-Fensterfunktionen zur Erzielung einer effizienten Paging-Abfrage
  • Spezifische Verwendung von MySQL-Fensterfunktionen
  • Lernen Sie schnell etwas über SQL-Fensterfunktionen

<<:  JavaScript-Tipps zur Verbesserung Ihrer Programmierkenntnisse

>>:  Interpretation des Moduls zum Lastenausgleich mit nginx

Artikel empfehlen

Detaillierte Erklärung zum effizienten MySQL-Paging

Vorwort Normalerweise wird für MySQL-Abfragen mit...

Ausführliche Erklärung der Closure in JavaScript

Einführung Closure ist eine sehr leistungsstarke ...

Die IE-Umgebung schreibt vor, dass die Div-Höhe größer sein muss als die Schrifthöhe

Code kopieren Der Code lautet wie folgt: <div ...

Nginx kompiliert nginx - neues Modul hinzufügen

1. Vorhandene Module anzeigen /usr/local/nginx/sb...

Informationen zur Docker-Sicherheit Probleme mit der Docker-TLS-verschlüsselten Kommunikation

Inhaltsverzeichnis 1. Sicherheitsprobleme mit Doc...

So verwenden Sie die Vue-Timeline-Komponente

In diesem Artikelbeispiel wird der spezifische Im...

Vue verwendet Echarts, um ein dreidimensionales Balkendiagramm zu implementieren

In diesem Artikel wird der spezifische Code von V...

Eine kurze Analyse von CSS3 mithilfe von Textüberlauf zur Lösung von Textlayoutproblemen

Grundlegende Syntax Die Verwendung von Text-Overf...

Vollständige Schritte zum Klonen von CentOS und zur gemeinsamen Nutzung virtueller Linux-Maschinen

Vorwort Wenn ein Linux vollständig eingerichtet i...

JavaScript, um das Bild mit der Maus zu bewegen

In diesem Artikel wird der spezifische JavaScript...

JavaScript-Verlaufsobjekt erklärt

Inhaltsverzeichnis 1. Routennavigation 2. API zur...

Analyse und Lösungen für Probleme bei der Verwendung von Label-Tags

Ich habe kürzlich bei einer bestimmten Aufgabe das...

So zeigen Sie im img-Tag in HTML nur die Bildmitte an (drei Methoden)

Derzeit gibt es drei Möglichkeiten, die Mitte ein...

So überprüfen Sie die Festplattennutzung unter Linux

1. Verwenden Sie den Befehl df, um die gesamte Fe...

5 Punkte, auf die Sie beim Erstellen einer Webseite achten sollten

1. Farbabstimmungsproblem <br />Eine Webseit...