Verwendung von ROW_NUMBER OVER() in SQL zum Einordnen von Daten

Manchmal muss man die Position der Zeilen in einer Ergebnismenge kennen. Lernen Sie, wie Sie dies mit ROW_NUMBER und OVER in SQL bewerkstelligen können!

Haben Sie schon einmal eine fortlaufende Nummer zu den von einer SQL-Abfrage zurückgegebenen Datensätzen hinzufügen müssen? Oder vielleicht müssen Sie einen "Top N"-Bericht auf der Grundlage einer bestimmten Rangfolge erstellen. In jedem dieser Fälle müssen Sie die Position der Zeile in der Rangliste berechnen. Hierfür benötigen Sie die Funktion ROW_NUMBER(). Die Funktion ordnet jeder Zeile in der Ergebnismenge eine fortlaufende Ganzzahl zu.

In diesem Artikel erfahren Sie, wie Sie die Funktion ROW_NUMBER() in SQL verwenden können.

Was ist die Funktion ROW_NUMBER()?

ROW_NUMBER ist eine Fensterfunktion in SQL. Sie wird verwendet, um fortlaufende Nummern zu den Zeilen einer Ergebnismenge hinzuzufügen. Wie jede andere Fensterfunktion müssen Sie sie mit der OVER() -Klausel verwenden. Hier ist die Syntax:

SELECT
  ROW_NUMBER() OVER (...) as athlete_num
  …
FROM athletes;

Die Klausel OVER() hat zwei optionale Unterklauseln: PARTITION BY und ORDER BY. Wir werden Beispiele mit verschiedenen OVER Klauseln zeigen.

Bevor wir beginnen, sollten wir ein paar Worte über Fensterfunktionen im Allgemeinen verlieren. Fensterfunktionen sind ein sehr mächtiger Teil von SQL, aber sie sind dem durchschnittlichen SQL-Benutzer nicht sehr bekannt. Aus diesem Grund empfehle ich unseren interaktiven Kurs über Fensterfunktionen. In diesem Kurs werden Sie Schritt für Schritt anhand von über 200 praktischen Übungen durch die Fensterfunktionen geführt. Am Ende des Kurses werden Sie mit der Verwendung von Fensterfunktionen in SQL-Datenbanken vertraut sein.

Verwendung von ROW_NUMBER() mit OVER(): Ein einführendes Beispiel

Lassen Sie uns eine einfache SQL-Abfrage mit der Fensterfunktion ROW_NUMBER zeigen. Es gibt nichts Besseres als Sport, um Ranglisten zu veranschaulichen. Nehmen wir also an, dass wir für ein Unternehmen arbeiten, das Sportwettbewerbe in vielen Ländern organisiert.

Zunächst möchten wir jedem Athleten eine fortlaufende Nummer zuweisen, die in unserem Unternehmen als ID des Athleten verwendet wird. Um Konflikte zu vermeiden, soll es keine Kriterien geben, nach denen die Reihenfolge der fortlaufenden Nummerierung festgelegt wird. Wir möchten, dass die fortlaufenden Nummern jedem Athleten nach dem Zufallsprinzip zugewiesen werden und nicht alphabetisch nach Name, Land oder Sportart.

Wir haben eine Tabelle namens athlete mit den Spalten firstname, lastname, sport, und country. Die Abfrage zur Erstellung eines Berichts mit einer fortlaufenden Nummer für jeden Athleten lautet:

SELECT
  ROW_NUMBER() OVER () as athlete_id,
  firstname
  lastname,
  sport, 
  country
FROM athletes;

Der Ausdruck ROW_NUMBER() OVER () ordnet jeder Zeile in der Ergebnismenge der Abfrage einen fortlaufenden ganzzahligen Wert zu, der mit 1 beginnt. Die Reihenfolge der Zahlen, die den Zeilen im Ergebnis zugewiesen werden, ist nicht deterministisch, wenn Sie die einfache OVER() Klausel verwenden. (Beachten Sie, dass es keine zusätzlichen Klauseln wie ORDER BY-Klausel oder PARTITION BY in OVER() gibt) Der erste Datensatz kann ein beliebiger Datensatz der Tabelle sein; für diesen Datensatz gibt ROW_NUMBER 1 zurück. Dasselbe gilt für den zweiten Datensatz, der die Nummer 2 erhält, und so weiter. Nachfolgend sehen Sie ein Teilergebnis der Abfrage:

Bevor ich diesen Abschnitt beende, möchte ich Ihnen den Artikel Was ist die OVER-Klausel in SQL empfehlen, in dem Sie mehrere Beispiele für Fensterfunktionen mit verschiedenen Kombinationen der OVER -Klausel finden.

Erstellen von Ranglisten mit ROW_NUMBER() und ORDER BY

Nehmen wir nun an, dass das Unternehmen ein Etikett mit der Teilnehmernummer für alle Athleten erstellen muss, die an einem Marathon teilnehmen. Die Athleten sollen nach Nachnamen geordnet werden, und das Unternehmen möchte jedem Athleten eine fortlaufende Nummer zuweisen; die Athleten werden diese Nummern während des Marathons als Etiketten auf ihren Shirts tragen. Die Etiketten müssen bei 1001 beginnen. Die Abfrage lautet:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon';

Diese Abfrage ist ähnlich wie das vorherige Beispiel. Ein Unterschied ist die WHERE Klausel, die nur die Athleten zurückgibt, die am Marathon teilnehmen. Der andere Unterschied (der wichtigste) ist die Klausel OVER(ORDER BY lastname). Diese gibt ROW_NUMBER() an, dass die fortlaufende Nummer in der Reihenfolge der lastnamevergeben werden muss - z. B. 1 für den ersten lastname, 2 für den zweiten und so weiter.

In der vorherigen Ergebnismenge wurden die Teilnehmer nach lastname geordnet. Wenn jedoch zwei Teilnehmer denselben Nachnamen haben (z. B. Smith), dann ist die Reihenfolge dieser beiden Zeilen nicht deterministisch; die Zeilen können in beliebiger Reihenfolge stehen. Wenn wir sowohl nach lastname als auch nach firstname ordnen wollen, sollten wir den Ausdruck verwenden:

ROW_NUMBER() OVER (ORDER BY lastname, firstname)

Zweimalige Verwendung von ORDER BY in einer Abfrage

In der obigen Abfrage verwenden wir die ORDER BY-Klausel in der Funktion ROW_NUMBER(). Das Ergebnis der Abfrage folgt jedoch keiner Reihenfolge, d. h. die Zeilen sind willkürlich angeordnet. Wenn wir wollten, könnten wir am Ende der Abfrage eine zweite ORDER BY-Klausel hinzufügen, um die Reihenfolge zu definieren, in der die Ergebnisdatensätze angezeigt werden.

Ändern wir die vorherige Abfrage, indem wir eine einzige Änderung hinzufügen: Wir fügen ein ORDER BY country ein:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname ASC) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon'
ORDER BY country;

Die Zeilen im folgenden Ergebnis sind die gleichen Zeilen wie in der vorherigen Abfrage, aber sie werden in einer anderen Reihenfolge angezeigt. Jetzt sind sie nach dem Land des Athleten geordnet. Wenn jedoch zwei oder mehr Sportler aus demselben Land stammen, werden sie in beliebiger Reihenfolge angezeigt. Dies ist unten bei den beiden Athleten aus Kanada zu sehen:

In dieser Abfrage haben wir die ORDER BY-Klausel zweimal verwendet. Das erste Mal wurde sie in der Funktion ROW_NUMBER verwendet, um die fortlaufende Nummer nach der Reihenfolge der Nachnamen zuzuweisen. Das zweite Mal wurde verwendet, um die Reihenfolge zu definieren, in der die Ergebniszeilen angezeigt werden, die auf dem Ländernamen basiert.

Verwendung von ROW_NUMBER() mit PARTITION BY und ORDER BY

In der nächsten Beispielabfrage verwenden wir ROW_NUMBER() in Kombination mit den Klauseln PARTITION BY und ORDER BY. Wir zeigen eine Abfrage, um den Sportlern Zimmernummern zuzuweisen. Nehmen wir an, das Unternehmen möchte Sportler aus demselben Land in zusammenhängenden Hotelzimmern unterbringen. Die Idee ist, ein Etikett mit dem Land und einer fortlaufenden Nummer für jeden Athleten zu erstellen und dieses Etikett an der Tür jedes Hotelzimmers anzubringen. Wenn das Land z. B. Kanada ist und 3 Sportler hat, sollen die Zimmeretiketten "Kanada_1", "Kanada_2" und "Kanada_3" heißen.

Die Abfrage zur Erzeugung der Zimmerbezeichnungen mit dem Namen des Sportlers, der diesem Zimmer zugewiesen ist, lautet:

SELECT
  country || '_' || 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY lastname ASC) 
                                                            as room_label,
  firstname,
  lastname, 
  country
FROM athletes;

Das neue Element, das in die Abfrage eingeführt wird, ist OVER(PARTITION BY country). Es gruppiert die Zeilen desselben country und erzeugt für jedes Land eine andere fortlaufende Reihe von Nummern (beginnend mit 1).

Im folgenden Abfrageergebnis sehen Sie, dass die durch die PARTITION BY Klausel gruppierten Zeilen die gleiche Farbe haben. Eine Gruppe von Zeilen ist für Kanada (hellblau), eine andere für Frankreich (violett), und so weiter.

Innerhalb jeder Zeilengruppe wird die Klausel ORDER BY lastname verwendet, um den Sportlern nach Nachnamen fortlaufende Nummern zuzuweisen. Für "Irland" gibt es drei Zeilen; die erste ist für "Barry", die zweite für "Fox" und so weiter.

Ich empfehle den Artikel How to Use SQL PARTITION BY with OVER, in dem Sie weitere Beispiele für die Klauseln OVER und PARTITION BY finden können.

Andere Ranglisten Fensterfunktionen: RANK und DENSE_RANK

Neben ROW_NUMBER bietet SQL zwei weitere Fensterfunktionen zur Berechnung von Rangfolgen: RANK und DENSE_RANK. Die Funktion RANK arbeitet anders als ROW_NUMBER, wenn es Gleichstände zwischen Zeilen gibt. Bei einem Gleichstand weist RANK beiden Zeilen denselben Wert zu und überspringt den nächsten Rang (z. B. 1, 2, 2, 2, 5 - die Ränge 3 und 4 werden ausgelassen). Die Funktion DENSE_RANK überspringt die nächste(n) Reihe(n) nicht.

Schauen wir uns ein einfaches Beispiel an, um die Unterschiede zwischen diesen drei Funktionen zu sehen:

SELECT 
  lastname AS athlete_name, 
  time, 
  ROW_NUMBER() OVER (ORDER BY time) AS position_using_row_number, 
  RANK OVER() (ORDER BY time) AS position_using_rank,
  DENSE_RANK() OVER (ORDER BY time) AS position_using_dense_rank
 FROM competition_results
 WHERE sport = ‘Marathon men’; 

Die Ergebnisse sind:

Wenn Sie sich für die Fensterfunktionen RANK und DENSE_RANK interessieren, empfehle ich diese Artikel für weitere Details und Beispiele:

• Übersicht über die Ranking-Funktionen in SQL
• Wie benutzt man RANK Funktionen
• Was ist die RANK-Funktion in SQL und wie wird sie verwendet?

Verwendung von ROW_NUMBER() in der WHERE-Klausel

In SQL können Sie keine Fensterfunktionen in der WHERE -Klausel verwenden. In einigen Szenarien kann dies jedoch erforderlich sein. In einem Top-10-Bericht wäre es z. B. sehr nützlich, eine Bedingung wie WHERE ROW_NUMBER OVER() <= 10 verwenden zu können.

Obwohl Sie ROW_NUMBER() nicht direkt in WHERE verwenden können, können Sie dies indirekt über einen gemeinsamen Tabellenausdruck (CTE) tun. Nehmen wir zum Beispiel an, wir wollen die ersten 3 Plätze im Marathon und im 100-Meter-Lauf ermitteln. Zuerst schreiben wir die CTE, die mit WITH beginnt:

-- CTE starts
WITH positions AS (
  SELECT 
    lastname AS athlete_name,
    sport,
    country,
    time, 
    ROW_NUMBER OVER (PARTITION BY sport ORDER BY time) AS position
  FROM competition_results
  WHERE sport IN (‘Marathon men’, ‘Marathon women’)
)
	--CTE ends

	--main query starts
SELECT 
  sport, 
  athlete_name, 
  time, 
  country, 
  position
FROM positions
WHERE position <= 3
ORDER BY sport, position;

In der vorherigen Abfrage haben wir eine CTE mit dem Namen positions erstellt. Sie hat eine Spalte mit der Bezeichnung Position, die mit dem Ergebnis der Funktion ROW_NUMBER() gefüllt wird.

In der Hauptabfrage (d. h. der zweiten Anweisung SELECT ) können wir die Spalte position in der Klausel WHERE verwenden, um die Athleten zu filtern, die den Wettkampf auf den ersten drei Plätzen beenden.

Hinweis: Bei Gleichstand zwischen zwei Athleten könnte die Funktion RANK() besser geeignet sein als die Funktion ROW_NUMBER() in diesem Bericht.

Die Ergebnisse der Abfrage werden unten angezeigt:

Wenn Sie SQL-Fensterfunktionen üben möchten, empfehle ich Ihnen unser interaktives Fensterfunktionen Practice Set. Es bietet 100 praktische Übungen zu Fensterfunktionen, einschließlich der Erstellung von Ranglisten mit verschiedenen Ranking-Fensterfunktionen.

Die Oracle ROWNUM-Pseudospalte

Oracle SQL ermöglicht es uns, eine Pseudospalte namens ROWNUM in jede Abfrage einzufügen. Eine Pseudospalte verhält sich wie eine Tabellenspalte, wird aber nicht in der Tabelle gespeichert. Sie können aus einer Pseudospalte auswählen, als wäre sie eine Spalte in der Tabelle.

Die Pseudospalte ROWNUM gibt die Position der Zeile in der Ergebnismenge zurück. Sie beginnt mit 1 für die erste Zeile und jeder der folgenden Datensätze wird um 1 erhöht.

Oracle ROWNUM verfügt jedoch nicht über die Leistungsfähigkeit der Fensterfunktion ROW_NUMBER. Sie können zum Beispiel die Subclause PARTITION BY nicht verwenden, um mehrere verschiedene Sequenzen zu erstellen, wie wir es bei der Abfrage der Hotelzimmer getan haben. Eine weitere Einschränkung besteht darin, dass Sie die ORDER BY -Klausel nicht verwenden können, um eine andere Reihenfolge für die Sequenz anzugeben als die Reihenfolge der Ergebnismenge. Der Grund für diese Einschränkungen ist einfach: ROWNUM ist keine Fensterfunktion, sondern nur eine einfache Pseudospalte.

Sind Sie bereit, ROW_NUMBER() und OVER() in SQL zu üben?

Wir haben uns mit verschiedenen Möglichkeiten befasst, dem Ergebnis einer Abfrage eine numerische Folge hinzuzufügen, indem wir die Funktion ROW_NUMBER verwenden. Und wir haben verschiedene Möglichkeiten zur Verwendung der OVER() Klausel gezeigt. Außerdem haben wir zwei weitere SQL-Ranking-Funktionen vorgestellt: RANK und DENSE_RANK.

Windows-Funktionen sind eine mächtige Ressource in SQL. Wenn Sie tiefer einsteigen wollen, empfehle ich Ihnen unseren interaktiven Online Fensterfunktionen Kurs. Es handelt sich um eine schrittweise Anleitung, die Sie anhand von Beispielen und Übungen durch die SQL-Fensterfunktionen führt. Ich empfehle auch unseren kostenlosen Spickzettel für SQL-Fensterfunktionen, den ich am liebsten verwende. Ich habe ihn in meinem Büro an die Wand gehängt, um ihn als schnelle Hilfe für die Syntax der Fensterfunktionen zu verwenden.