Was ist die Funktion RANK() in SQL, und wie wird sie verwendet?

Wer sind Ihre 10 besten Kunden? Wer sind die leistungsstärksten Mitarbeiter in den einzelnen Abteilungen? Um Fragen wie diese zu beantworten, müssen Sie die Ausgabe Ihrer SQL-Abfrage in eine Rangfolge bringen. Sehen wir uns an, wie die RANK()-Funktion bei der Einstufung von Ergebnissen in SQL behilflich ist.

Es gibt viele Anwendungsfälle, in denen Sie Zeilen in SQL einordnen müssen. Vielleicht möchten Sie Kunden auf der Grundlage des Umsatzvolumens, Studenten auf der Grundlage ihrer Prüfungsergebnisse, Unternehmensvermögen auf der Grundlage ihres aktuellen Wertes, Abteilungen auf der Grundlage der Anzahl der Mitarbeiter oder Benutzer auf der Grundlage ihres Registrierungsdatums einordnen.

Ranglisten sind ein wichtiges und weit verbreitetes Werkzeug in der Datenanalyse. In diesem Tutorium werde ich detailliert erklären, wie man Zeilen in SQL einordnet.

Einführung in die Funktion RANK()

Die Funktion RANK() ist eine der Fensterfunktionen in SQL. Fensterfunktionen betrachten einen Teil der Daten und berechnen die Ergebnisse für diesen Teil.

Die Funktion RANK() weist jeder Zeile auf der Grundlage einer angegebenen Spalte einen Rang zu. RANK() wird mit der folgenden Syntax in die SELECT-Anweisung aufgenommen:

RANK() OVER (ORDER BY column ASC|DESC)

Im Grunde genommen fügen Sie eine weitere Spalte zu Ihrer Ergebnismenge hinzu. Diese Spalte enthält den Rang der einzelnen Datensätze auf der Grundlage der nach dem Schlüsselwort ORDER BY angegebenen Reihenfolge. Dazu müssen Sie (1) die Spalte angeben, nach der die Zeilen sortiert werden sollen, und (2) ob die Reihenfolge auf- oder absteigend sein soll.

Die erste Zeile erhält Rang 1, und die folgenden Zeilen erhalten höhere Ränge. Wenn Zeilen den gleichen Wert in der Spalte haben, die für die Sortierung verwendet wird, erhalten sie den gleichen Rang. Die Funktion RANK() lässt in solchen Fällen Lücken.

Wenn zum Beispiel die erste und die zweite Zeile den gleichen Wert in der angegebenen Spalte haben, erhalten beide den Rang 1. Dann erhält die dritte Zeile den Rang 3 (d. h., Rang 2 fehlt).

Um sicher mit der Funktion RANK() umzugehen, schauen Sie sich unseren interaktiven Fensterfunktionen Kurs. Er deckt auch andere Ranking-Funktionen ab, die unterschiedliche Ansätze zur Verarbeitung der gleichen Werte in der angegebenen Spalte haben.

Schauen wir uns nun die Funktion RANK() in Aktion an.

Grundlegende Rangfolge

Um zu sehen, wie die Funktion RANK() in der Praxis funktioniert, gehen wir ein Beispiel durch. Nehmen wir an, wir haben ein Team von Entwicklern. Wir möchten sie nach ihrer Erfahrung einstufen. Hier ist die Tabelle, mit der wir beginnen:

Unsere Aufgabe ist es nun, den Vornamen, den Nachnamen, die Dienstaltersstufe, die Jahre der Erfahrung und den Rang der Entwickler anzuzeigen. Wir möchten, dass der erfahrenste Entwickler den Rang 1 erhält.

Hier ist unsere SQL-Abfrage zusammen mit der Ergebnismenge:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC)
FROM developers;

Um die Entwickler nach ihrer Erfahrung zu ordnen, haben wir das folgende Feld in die SELECT -Anweisung aufgenommen:

RANK() OVER (ORDER BY years_experience DESC)

Wir definieren die Reihenfolge nach der ORDER BY Klausel, indem wir die Spalte, die für die Sortierung verwendet werden soll (years_experience) und die Reihenfolge (absteigend) angeben. Dann ordnen wir die Entwickler auf der Grundlage dieser Reihenfolge.

Wir sehen, dass der Entwickler mit 8 Jahren Erfahrung auf Platz 1 steht, der Entwickler mit 5 Jahren Erfahrung auf Platz 2 und die beiden Entwickler mit 4 Jahren Erfahrung beide auf Platz 3. Beachten Sie, dass die Funktion RANK() hier eine Lücke hinterlässt und die nächsten Entwickler den Rang 5 erhalten (d.h. Rang 4 fehlt).

Jetzt haben Sie eine Vorstellung davon, wie die Funktion RANK() funktioniert, einschließlich der Verarbeitung der gleichen Werte in der Spalte, die für die Sortierung verwendet wird. Weitere Anwendungsfälle finden Sie in diesem vollständigen Leitfaden zum Sortieren von Zeilen in SQL.

Jetzt kommen wir zu fortgeschritteneren Beispielen.

Rangfolge innerhalb verschiedener Gruppen

In unserem ersten Beispiel haben wir die gesamte Ergebnismenge sortiert. Nehmen wir nun an, dass wir eine separate Rangliste für jede der Senioritätsstufen (d. h. Junior, Mid-Level und Senior) erstellen möchten.

Mit der Funktion RANK() in SQL können wir dies tun, indem wir das Schlüsselwort PARTITION BY hinzufügen und die Spalte angeben, die für die Gruppierung der Datensätze verwendet werden soll. Hier ist unsere SQL-Abfrage:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (PARTITION BY level ORDER BY years_experience    DESC)
FROM developers;

In der OVER() -Klausel legen wir zunächst fest, wie die Datensätze gruppiert werden sollen (unter Verwendung der Spalte level ), und definieren dann, wie die Datensätze innerhalb dieser Gruppen geordnet werden sollen (basierend auf den Jahren der Erfahrung, beginnend mit dem erfahrensten Entwickler).

Hier ist die resultierende Tabelle:

Beachten Sie, dass wir getrennte Ranglisten für die Junior-, Mid-Level- und Senior-Ebene haben. Dennoch erhält der erfahrenste Entwickler in jeder Gruppe Rang 1, und die Entwickler mit weniger Erfahrung erhalten höhere Ränge.

Beachten Sie auch, dass Kate Williams und Max Weber zwar dieselben 4 Jahre Erfahrung haben, Max aber als erfahrenster Entwickler der mittleren Ebene Rang 1 erhält, während Kate als am wenigsten erfahrene Senior-Entwicklerin Rang 3 erhält.

Nachdem Sie nun wissen, wie PARTITION BY funktioniert, ist es an der Zeit, sich mit anderen Ranking-Funktionen zu beschäftigen.

Andere Ranking-Funktionen in SQL

Um Zeilen in SQL zu ordnen, können Sie auch die Funktionen DENSE_RANK() und ROW_NUMBER() verwenden. Der Unterschied liegt in der Art und Weise, wie diese Funktionen mit denselben Werten umgehen.

Wie wir bereits gelernt haben, gibt die Funktion RANK() Datensätzen mit gleichem Wert den gleichen Rang und lässt eine Lücke, um diese Datensätze zu berücksichtigen. Im Gegensatz dazu:

• Die Funktion DENSE_RANK() hinterlässt keine Lücke;
• die Funktion ROW_NUMBER() ordnet alle Datensätze unterschiedlich ein, auch wenn die Werte gleich sind.

Es ist einfacher, diesen Unterschied anhand eines Beispiels zu verstehen. Kehren wir also zu unserem ersten Anwendungsfall zurück, bei dem wir die gesamte Ergebnismenge eingestuft haben. Diesmal verwenden wir aber auch DENSE_RANK() und ROW_NUMBER():

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC),
       DENSE_RANK() OVER (ORDER BY years_experience DESC),
       ROW_NUMBER() OVER (ORDER BY years_experience DESC)
FROM developers;

Ähnlich wie die Funktion RANK() gibt die Funktion DENSE_RANK() den Entwicklern mit denselben Erfahrungsjahren denselben Rang (z. B. Rang 3 für Kate Williams und Max Weber). Der nächste Rang in der Spalte dense_rank ist jedoch 4, so dass es keine Lücken in der Rangliste gibt.

Auf der anderen Seite weist die Funktion ROW_NUMBER() allen Entwicklern eine andere Nummer zu, auch denen mit den gleichen Erfahrungsjahren. Warum also erhält Max Rang 3 und Kate Rang 4? Es handelt sich um eine willkürliche Auswahl, und Sie wissen nie, welcher der Datensätze mit demselben Wert einen höheren Rang erhalten wird.

Jetzt, da Sie den Unterschied zwischen den Ranking-Funktionen kennen, können Sie entscheiden, welche für Ihre Anwendungsfälle besser geeignet ist. Wenn Sie sich nicht sicher sind, finden Sie weitere Beispiele für die Funktionen RANK(), DENSE_RANK() und ROW_NUMBER() in diesem Übersichtsartikel.

Zeit zum Üben von Ranking-Funktionen!

Ranking-Funktionen gehören zu den von Datenanalysten am häufigsten verwendeten Tools. Sie sollten sich also sicher fühlen im Umgang mit Ranking-Funktionen in SQL. Und Sicherheit kommt mit der Übung.

Schauen Sie sich diesen Fensterfunktionen Kurs, der 218 interaktive Übungen zu Ranking-Funktionen, Analysefunktionen, fortgeschrittenen Statistikberechnungen mit verschiedenen Fensterrahmen usw. enthält. Mehr über den Kurs erfahren Sie in diesem Artikel.

Und hier ist ein Bonus: Dieses 2-seitige SQL Fensterfunktionen Cheat Sheet hilft Ihnen beim Üben von Ranking- und anderen Fensterfunktionen.

Vielen Dank fürs Lesen und viel Spaß beim Lernen!