MySQL praktische Fensterfunktion SQL-Analyse Klasse Testergebnisse und Lebenshaltungskosten Studenten

1. Hintergrund

Heute werden die monatlichen Prüfungsergebnisse der dritten Klasse einer Scheinuniversität veröffentlicht. Hiermit möchte ich Ihnen die Prüfungsergebnisse jedes einzelnen Schülers bekannt geben.

Als nächstes werde ich Ihnen die Lebenshaltungskosten jedes Studenten bekannt geben.

Als Nächstes verwenden wir die oben genannten Testergebnisse und Aufzeichnungen zu den Lebenshaltungskosten, um mit MySQL eine einfache Analyse durchzuführen.

Dies ist natürlich schon aus dem Titel dieses Artikels ersichtlich. Dieser Artikel verwendet diese Daten, um die Verwendung von SQL-Fensterfunktionen zu erklären.

Dies wird ein sehr wichtiger Wissenspunkt sein, unabhängig davon, ob Sie in Zukunft Hive- oder Oracle-Datenbanken studieren oder an Datenanalyse-Interviews teilnehmen.

2. Anweisungen zur Tabellenerstellung und zum Einfügen von Daten

Erstellen einer Tabelle

Tabelle exam_score erstellen(
    sname varchar(20),
    Alter int,
    Betreff varchar(20),
    Punktzahl varchar(20)
)Zeichensatz=utf8;

# ----------------------- #

Tabelle cost_fee erstellen(
    sname varchar(20),
    Kaufdatum varchar(20),
    Kaufkosten int
)Zeichensatz=utf8;

Einfügen von Daten

in exam_score-Werte einfügen
('Zhang San', 18, 'Chinesisch', 90),
('Zhang San', 18, 'Mathematik', 80),
('Zhang San', 18, 'Englisch', 70),
('Li Si', 21, 'Chinesisch', 88),
('Li Si', 21, 'Mathematik', 78),
('Li Si', 21, 'Englisch', 71),
('Wang Wu', 18, 'Chinese', 95),
('Wang Wu', 18, 'Mathematik', 83),
('Wang Wu', 18, 'Englisch', 71),
('Zhao Liu', 19, 'Chinesisch', 98),
('Zhao Liu', 19, 'Mathematik', 90),
('Zhao Liu', 19, 'Englisch', 80);
# ----------------------- #
in cost_fee-Werte einfügen
('Zhang San','2019-01-01',10),
('Zhang San','2019-03-03',23),
('Zhang San','2019-02-05',46),
('Li Si','2019-02-02',15),
('Li Si','2019-01-07',50),
('Li Si','2019-03-04',29),
('Wang Wu','2019-03-08',62),
('Wang Wu','2019-02-09',68),
('Wang Wu','2019-01-11',75),
('Zhao Liu','2019-02-08',55),
('Zhao Liu','2019-03-10',12),
('Zhao Liu','2019-01-12',80);

3. Einführung in die Klassifizierung von Fensterfunktionen

Bevor ich die Anwendung von „Fensterfunktionen“ formal bespreche, werde ich zunächst die Grundlagen von „Fensterfunktionen“ wiederholen. Wir können Fensterfunktionen in die folgenden Kategorien einteilen:

Aggregatfunktion + over()-Kombination;

Sortierfunktion + over()-Kombination;

Kombination aus ntile()-Funktion + over();

Kombination aus Offset-Funktion und over();

Was sind die Funktionen der einzelnen Kategorien? Beachten Sie die Mindmap unten.

Es gibt zwei häufig verwendete Schlüsselwörter in over(), die erklärt werden müssen. wie folgt:

Partitionierung nach + Feld: Sie können es sich als das Schlüsselwort „Gruppieren nach“ vorstellen, also das Schlüsselwort, das für die „ Gruppierung“ verwendet wird.

order by + field: Dies ist einfacher zu verstehen, es ist das Schlüsselwort, das für „Sortieren“ verwendet wird;

4. Anwendung der Fensterfunktion

Wir haben oben mehrere häufig verwendete „Fensterfunktionen“ vorgestellt. Hier verwenden wir die am Anfang des Artikels erstellten Daten, um über die Anwendung von „Fensterfunktionen“ zu sprechen.

Ich hoffe, Sie können die Bedeutung der einzelnen Funktionen im Einzelfall zusammenfassen, daher werde ich hier nicht im Detail darauf eingehen.

1. Aggregatfunktion + over()

① Berechnen Sie die Punktzahl jedes Schülers und den Durchschnitt

wählen 
	Name
    ,Thema
    ,Punktzahl
    ,avg(score) über(Partition nach Sname) als avg_score
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

② Berechnen Sie den Verbrauch jedes Schülers und den Gesamtverbrauch von Januar bis März

wählen
	Name
    ,Kaufdatum
    ,Kaufkosten
    ,Summe(Kaufkosten) über(Partition nach Sname) als Summe_Kosten
aus
	Kostengebühr

Die Ergebnisse sind wie folgt:

③ Berechnen Sie den Verbrauch jedes Schülers von Januar bis März und den gesamten kumulierten Verbrauch

wählen
	Name
    ,Kaufdatum
    ,Kaufkosten
    ,Summe(Kaufkosten) über(Partition nach Sname, sortiert nach Kaufdatum) als Summe_Kosten
aus
	Kostengebühr

Die Ergebnisse sind wie folgt:

Hinweis: Durch die Kombination von ②③ können Sie feststellen, dass die Kombination von Partition by mit Order by und ohne Order by völlig unterschiedliche Ergebnisse liefert. Eine besteht darin, die Gesamtsumme der Gruppen zu ermitteln (ohne Sortieren nach), die andere darin, die kumulative Summe der Gruppen zu ermitteln (mit Sortieren nach).

2. Sortierfunktion + over()

① Berechnen Sie die Rangfolge der einzelnen Fächer. Die gleiche Punktzahl hat eine andere Rangfolge und die Reihenfolge nimmt in der Reihenfolge zu.

wählen
	Name
	,Thema
	,Punktzahl
    ,row_number() über(Partition nach Thema, Sortierung nach Punktzahl) Rang1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

② Berechnen Sie die Rangfolge der einzelnen Fächer. Bei gleicher Punktzahl bleibt die Rangfolge gleich, und die übrigen Ränge steigen nach oben.

wählen
	Name
	,Thema
	,Punktzahl
    ,rank() über(Partition nach Thema, Sortierung nach Punktzahl) rank1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

③ Berechnen Sie die Rangfolge der einzelnen Fächer. Gleiche Punktzahlen werden gleich eingestuft, und die verbleibenden Punktzahlen werden in aufsteigender Reihenfolge eingestuft.

wählen
	Name
	,Thema
	,Punktzahl
    ,dense_rank() über(Partition nach Thema, Sortierung nach Punktzahl) rank1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

3. Kombination aus ntile()-Funktion und over()

Die Funktion ntile() wirkt etwas fehl am Platz und Sie wissen nicht, in welche Kategorie Sie sie einordnen sollen. Diese Funktion wird hauptsächlich zur „ Datensegmentierung“ verwendet. Der Nutzen dieser Funktion liegt darin, dass sie die Daten auch sortieren kann, ähnlich der oben erwähnten Funktion row_number().

① Teilen Sie die gesamte exam_score-Tabelle auf

wählen
	Name
	,Thema
	,Punktzahl
    ,ntile(4) über() Rang1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

Wenn Sie mir nicht glauben, probieren Sie es aus. Es scheint, dass es funktioniert, egal welche Zahl Sie in ntile() schreiben.

② Teilen Sie die exam_score-Tabelle nach Fächergruppen auf.

wählen
	Name
	,Thema
    ,Punktzahl
    ,ntile(4) über(Partition nach Thema) Rang1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

Auch wenn Sie nach Gruppen aufteilen, werden Sie feststellen, dass dies keinen Sinn ergibt, da die Ergebnisse nicht sortiert sind.

③ Sortieren Sie für die exam_score-Tabelle die Ergebnisse und gruppieren und teilen Sie sie dann nach Fächern auf (am nützlichsten).

wählen
	Name
	,Thema
    ,Punktzahl
    ,ntile(4) über(Partition nach Subjekt, Sortierung nach Punktzahl) Rang1
aus
	Prüfungsergebnis

Die Ergebnisse sind wie folgt:

Hinweis: Wenn Sie diese Verwendung sorgfältig beobachten, ist sie grundsätzlich gleichwertig mit der Funktion row_number() und die Wirkung ist dieselbe.

4. Kombination aus Offset-Funktion und over()

① Anzeige der „letzten Kaufzeit“ und „nächsten Kaufzeit“ jedes Schülers

Hinweis: Für den ersten Tag wird „Erster Kauf“ angezeigt, für den letzten Tag „Letzter Kauf “ ;

wählen
	Name
	,Kaufdatum
    ,lag(buydate,1,'erster Tag') über(Partition nach Sname, sortiert nach Kaufdatum) als letzte Kaufzeit,lead(buydate,1,'letzter Tag') über(Partition nach Sname, sortiert nach Kaufdatum) als nächste Kaufzeit von
	Kostengebühr

Die Ergebnisse sind wie folgt:

② Zum aktuellen Datum die „erste Kaufzeit“ und „letzte Kaufzeit“ jedes Schülers

wählen
	Name
	,Kaufdatum
    ,first_value(buydate) over(partition by sname order by buydate) als erstes Kaufdatum,last_value(buydate) over(partition by sname order by buydate) als letztes Kaufdatum von
	Kostengebühr

Die Ergebnisse sind wie folgt:

③ Anzeige der „ersten Kaufzeit“ und „letzten Kaufzeit“ jedes Schülers

Hinweis: Hier heißt es nicht „zum aktuellen Datum“, bitte beachten Sie den Unterschied zwischen ②③. Unterschiedliche Bedürfnisse führen zu unterschiedlichen Ergebnissen.

wählen
	Name
	,Kaufdatum
    ,first_value(buydate) over(partition by sname order by buydate) als erstes Kaufdatum,last_value(buydate) over(partition by sname ) als letztes Kaufdatum von
	Kostengebühr

Die Ergebnisse sind wie folgt:

Oben sind die Details zur Verwendung der praktischen MySQL-Fensterfunktion SQL zur Analyse der Testergebnisse und Lebenshaltungskosten von Schülern aufgeführt. Weitere Informationen zur SQL-Fensterfunktion zur Analyse von Ergebnissen und Lebenshaltungskosten finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!