Wie man SQL RANK OVER (PARTITION BY) verwendet

Das Ranking von Daten in SQL ist ein Kinderspiel, wenn man weiß, wie man RANK() zum Ranking über eine Partition verwendet. Dieser Artikel zeigt Ihnen, wie das geht, und wie sich RANK() von DENSE_RANK() und ROW_NUMBER() unterscheidet.

Wenn Sie beruflich mit SQL arbeiten, mussten Sie schon mindestens einmal Daten einordnen. Denken Sie nur an die Einstufung von Monaten/Jahren/Quartalen nach Umsatz oder Kosten, an die meistverkauften Produkte, die am meisten angesehenen Beiträge oder gestreamten Songs, die Mitarbeiter nach Gehalt, die profitabelsten Filialen und so weiter.

Oder eine Rangliste der Bücher nach ihren Verkäufen.

Die Sache ist die, dass man oft Daten innerhalb einer bestimmten Kategorie, oder Partition, wie wir es nennen, einordnen muss. Hier kommt die SQL RANK OVER (PARTITION BY) ins Spiel!

Dies ist ein typisches Beispiel für die Fensterfunktionen in SQL. Für eine detailliertere Erklärung der Fensterfunktionen ist unser Kurs Fensterfunktionen die beste Wahl. Anhand von 218 interaktiven Übungen lernen Sie mehr über PARTITION BY und andere Fensterfunktionsklauseln, wie ORDER BY, ROWS und RANGE. Diese Funktionen werden nicht nur in der Rangliste, sondern auch in den Fensterfunktionen für Aggregate und Analysen verwendet.

Kehren wir zu den Buchverkäufen zurück. Werfen Sie einen Blick auf diese Tabelle, deren Code hier zu finden ist. Wie ordnen wir die Buchverkäufe innerhalb jeder Sprache ein?

Die Lösung ist einfach, wenn Sie wissen, dass die Sprache in diesem Beispiel als Datenpartition fungiert.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Und hier ist sie!

Lassen Sie uns dieses Beispiel nun analysieren.

Was ist RANK()?

Einfach ausgedrückt, ist RANK() eine Fensterfunktion.

Die SQL-Fensterfunktionen ähneln den Aggregatfunktionen insofern, als sie auf eine Gruppe von Zeilen angewendet werden. Ein großer Unterschied besteht darin, dass Fensterfunktionen im Gegensatz zu Aggregatfunktionen mit GROUP BY Details zu den einzelnen Zeilen speichern.

Was macht RANK()?

Wie der Name schon sagt, ordnet sie die Daten. Damit ist sie eine Ranking-Fensterfunktion, ebenso wie DENSE_RANK() und ROW_NUMBER().

Wenn eine dieser Fensterfunktionen verwendet wird, muss sie von einer OVER (ORDER BY) Klausel begleitet werden. Die Klausel OVER() ist für jede Fensterfunktion obligatorisch. Sie verwandelt eine "normale" Funktion in eine Fensterfunktion.

Bei diesen Ranking-Funktionen definiert die ORDER BY in Klammern die Reihenfolge, in der das Ranking durchgeführt wird. Diese Reihenfolge kann aufsteigend oder absteigend sein. Denken Sie daran, dass dies keinen Einfluss auf die Reihenfolge der Zeilen im Ergebnis hat; diese wird mit ORDER BY am Ende der Abfrage festgelegt.

Erfahren Sie mehr darüber in unserem Artikel über die Fensterfunktion RANK().

Wie RANK() mit OVER (ORDER BY) funktioniert

Verwenden wir den obigen Datensatz. Nehmen Sie die gesamte Tabelle und ordnen Sie die Bücher nach Verkäufen. Schauen wir uns an, was passiert.

SELECT
  title,
  author,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM books;

Dies ist die gleiche Abfrage wie die vorherige, nur dass hier die Sprache nicht ausgewählt und PARTITION BY nicht verwendet wird. Also folgt auf die Funktion RANK() die Funktion OVER(). Die darin enthaltene Klausel ORDER BY weist die Funktion an, die Daten nach den Verkäufen in absteigender Reihenfolge zu ordnen, d. h. von den meistverkauften zu den am wenigsten verkauften Büchern. Da die Klausel PARTITION BY weggelassen wird, ordnet die Funktion die gesamte Tabelle.

Hier sind die ersten zehn Zeilen der Ausgabe.

Was hier auffällt: Es gibt vier Bücher mit 50 Millionen verkauften Exemplaren, die alle an fünfter Stelle stehen.

Darin unterscheidet sich RANK() von den beiden anderen Ranking-Fenster-Funktionen - sie gibt gleichrangigen Werten den gleichen Rang. Wenn die Funktion den nächsten Verkaufswert erreicht (in diesem Fall 36 Millionen verkaufte Exemplare), weist sie nicht den nächstfolgenden Rangwert (6) zu, sondern überspringt ihn, um die Anzahl der gleichwertigen Verkaufswerte zu berücksichtigen. Wie bereits erwähnt, taucht der Rang "5" viermal auf; der nächste zugewiesene Rang ist also neun.

DENSE_RANK() rangiert auch die Gleichstände mit demselben Rang. Im Gegensatz zu RANK() überspringt es jedoch keine Rangwerte aufgrund von Gleichständen. Die gleichen Daten werden mit DENSE_RANK() wie folgt eingestuft.

Nach mehreren Büchern, die an fünfter Stelle stehen, ist der nächste Rang der sechste und nicht der neunte wie bei RANK().

Was ist mit ROW_NUMBER()? Es kümmert sich nicht um Gleichstände oder Überspringen. Es ordnet die Zeilen einfach der Reihe nach. Die ersten zehn Zeilen mit ROW_NUMBER() sind unten zu sehen.

Die Unterschiede zwischen diesen Funktionen werden in der Übersicht der Ranking-Funktionen erläutert.

Wie RANK() mit OVER (PARTITION BY) funktioniert

Die PARTITION BY Klausel unterteilt Daten in Partitionen oder Teilmengen. Bei Verwendung mit RANK() bedeutet dies, dass die Daten innerhalb der Partition in eine Rangfolge gebracht werden. Wenn die zweite Partition erreicht ist, wird die Rangfolge wieder auf den ersten Platz zurückgesetzt.

Schauen wir uns noch einmal die Abfrage vom Anfang dieses Artikels an, um dies zu verdeutlichen.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

In dieser Abfrage wird RANK() mit PARTITION BY verwendet. Die Partition ist in diesem Fall original_language. Die Rangfolge wird nach Verkäufen in absteigender Reihenfolge erstellt, wie in der Klausel ORDER BY angegeben.

Die Schreibweise von PARTITION BY und ORDER BY bedeutet, dass die Bücher nach den Verkäufen, aber innerhalb jeder Sprachkategorie geordnet werden. Sobald die Funktion alle Bücher in einer Sprache eingestuft hat, beginnt sie erneut, wenn sie die zweite Sprache erreicht, und so weiter.

Dies wird in der Abfrageausgabe deutlich.

Wir haben jede Partition mit einer anderen Farbe markiert, damit die verschiedenen Partitionen leicht zu erkennen sind. Das meistverkaufte Buch in englischer Sprache ist J.K. Rowlings Harry Potter and the Philosopher's Stone. Dann folgt Der Hobbit. Lolita und Watership Down liegen beide an dritter Stelle, da RANK() Büchern mit gleichem Verkaufswert den gleichen Rang zuweist. Dann wird ein Rangwert übersprungen, und Neunzehnhundertvierundachtzig steht an fünfter Stelle.

Die nächste Unterteilung ist die französische Sprache, und die Rangliste wird neu gestartet. Der Kleine Prinz ist das meistverkaufte Buch in französischer Sprache.

Die gleiche Logik gilt auch für Bücher in Italienisch, Russisch und Spanisch.

Bonus-Beispiel

Wir haben diese Abfragen untersucht, um zu zeigen, wie RANK() OVER (PARTITION BY) funktioniert. Jetzt wollen wir üben!

Das folgende Beispiel unterscheidet sich nicht wesentlich von der ersten Abfrage. Sie sollten keine Probleme haben, das Gelernte anzuwenden.

Es gibt eine Spalte in der books Tabelle namens classify_under. Sie gibt die Kategorie an, unter der jedes Buch in der Buchhandlung platziert werden soll.

Lassen Sie uns die Bücher nach den Verkäufen für jede Kategorie ordnen.

SELECT
  classify_under,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY classify_under ORDER BY sales DESC)
    AS sales_rank
FROM books;

Die Abfrage wählt die Spalte classify_under anstelle von original_language in der ersten Abfrage aus. Alle anderen ausgewählten Spalten sind die gleichen.

Es gibt auch einen Unterschied in RANK(). Da wir die Rangliste nach classify_under erstellen, muss diese Spalte in der Klausel PARTITION BY enthalten sein.

Auch hier wollen wir die Bücher nach Verkäufen in absteigender Reihenfolge ordnen. Hier ist die Rangfolge:

Wie es der Zufall will, gibt es wieder fünf Partitionen. In der Kategorie "Klassiker" ist Don Quijote das meistverkaufte Buch. Danach folgen drei Bücher auf dem zweiten Platz. Die Rangfolge wird übersprungen, bis wir zu Krieg und Frieden auf Platz fünf kommen. Die übrigen Klassiker werden in der Reihenfolge ihrer Platzierung aufgeführt, da es keine weiteren Gleichstände gibt.

In anderen Kategorien gibt es keine Gleichstände: In den Kategorien "Lyrik" und "Science Fiction" gibt es jeweils nur ein Buch. Es gibt also nur den ersten Rang.

Weitere Beispiele mit anderen Fensterfunktionen finden Sie in unserem Artikel über die Verwendung von PARTITION BY.

Erweitern Sie Ihr SQL-Vokabular um RANK()

Wir haben die typischsten Verwendungen der Fensterfunktion RANK() gesehen. Sie erfordert zwar eine OVER (ORDER BY), aber die PARTITION BY Klausel schaltet ihre Möglichkeiten frei. Sie macht RANK() zu einem ausgefeilten Werkzeug, mit dem Sie Daten in einer oder mehreren Partitionen bei Ihrer täglichen Arbeit mühelos einordnen können.

Um mehr über RANK() und andere (Ranking-)Fensterfunktionen zu erfahren und zu üben, nutzen Sie unseren Fensterfunktionen Kurs. Sie erhalten eine noch detailliertere Erklärung von Ranking und die Möglichkeit, in unseren Übungen viel Code zu schreiben.

Viel Spaß beim Ranking!