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

CSS implementiert 0,5px-Zeilen, um Kompatibilitätsprobleme mit Mobilgeräten zu lösen (empfohlen)

【Inhalt】: 1. Verwenden Sie den Verlaufsstil des H...

mysql-8.0.16 winx64 neuestes Installationstutorial mit Bildern und Text

Ich habe erst vor Kurzem angefangen, mich mit Dat...

Detaillierte Schritte zum Ändern des Standardkennworts bei der Installation von MySQL in Ubuntu

Schritt 1: Geben Sie das Verzeichnis ein: cd /etc...

So verwenden Sie Vue3-Mixin

Inhaltsverzeichnis 1. Wie verwende ich Mixin? 2. ...

So verwenden Sie Vue+ElementUI Tree

Die Verwendung von Vue + ElementUI Tree dient zu ...

JavaScript implementiert die Operationen „Alle auswählen“ und „Alle abwählen“

In diesem Artikel wird der spezifische Code für J...

So beheben Sie den Fehler "ERROR 1045 (28000)" beim Anmelden bei MySQL

Heute habe ich mich beim Server angemeldet und mi...

Docker stellt nginx bereit und mountet Ordner und Dateioperationen

Während dieser Zeit habe ich Docker studiert und ...

Schnelle Deinstallation und Installation der Knotenumgebung eines Linux-Servers (einfacher Einstieg)

1. Deinstallieren Sie zuerst npm sudo npm deinsta...

Detaillierte Schritte zur Neuinstallation von VMware Tools (grafisches Tutorial)

VMware Tools ist ein Tool, das mit virtuellen VMw...

dh Filtersammlung

Der IE hat uns in der frühen Entwicklungsphase Ko...

Detaillierte Erklärung der Verwendung und Unterschiede von MySQL-Ansichten und -Indizes

MySQL-Ansichten Einfach ausgedrückt ist eine MySQ...

Detaillierte Verwendung des Linux-Textsuchbefehls find

Der Befehl „Find“ wird hauptsächlich zum Suchen v...

Einführung in die Containerfunktion of() in der Linux-Kernel-Programmierung

Vorwort Bei der Linux-Kernel-Programmierung werde...

Beheben Sie das Problem, dass der benutzerdefinierte CSS-Stil von ElementUI nicht wirksam wird

Beispielsweise gibt es ein Eingabefeld <el-Ein...