Wie man Zeilen in einer SQL-Ergebnismenge nummeriert

Haben Sie schon einmal eine fortlaufende Nummer zu den Datensätzen im Ergebnis einer SQL-Abfrage hinzufügen müssen? Es ist nicht so einfach, wie es aussieht! In diesem Artikel erfahren Sie, wie Sie es richtig machen.

Um Zeilen in einer Ergebnismenge zu nummerieren, müssen Sie eine SQL-Fensterfunktion namens ROW_NUMBER() verwenden. Diese Funktion weist jeder Ergebniszeile eine fortlaufende ganzzahlige Nummer zu. Sie kann jedoch auch verwendet werden, um Datensätze auf andere Weise zu nummerieren, z. B. nach Teilmengen. Sie können damit sogar Datensätze für andere interessante Zwecke nummerieren, wie wir noch sehen werden.

Eine häufige Anfrage: Könnten Sie die Datensätze nummerieren?

Angenommen, Sie arbeiten für eine Autoverkaufsfirma und möchten den folgenden Bericht erstellen. Beachten Sie, dass die erste Spalte (row_num) keine Tabellenspalte ist; wir erzeugen sie mit ROW_NUMBER() in der Abfrage.

Ergebnistabelle

Die Abfrage, um den Bericht zu erhalten, lautet:

SELECT ROW_NUMBER() OVER () AS row_num,
       article_code,
       article_name,
       branch,
       units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

In der obigen Abfrage ist die Syntax der Funktion ROW_NUMBER() sehr einfach: Wir verwenden eine leere Klausel OVER. Das bedeutet, dass wir alle Datensätze in der Ergebnismenge mit einer einzigen Zahlenfolge nummerieren wollen, indem wir den Datensätzen Nummern ohne jegliche Reihenfolge zuweisen. Dies ist die einfachste Art, die Funktion ROW_NUMBER() zu verwenden:

ROW_NUMBER() OVER () AS row_num

Es gibt jedoch auch andere Möglichkeiten, ROW_NUMBER() zu verwenden. Wir können eine PARTITION BY und/oder eine ORDER BY Klausel zu OVER hinzufügen, wie wir im nächsten Abschnitt sehen werden. Mit der PARTITION BY -Klausel können wir mehrere Gruppen von Datensätzen unabhängig voneinander nummerieren, während wir mit der ORDER BY -Klausel die Datensätze in einer bestimmten Reihenfolge nummerieren können. Im nächsten Abschnitt werden wir einige Beispiele sehen.

Bevor wir mit ROW_NUMBER() fortfahren, sollten wir ein paar Worte über SQL-Fensterfunktionen verlieren. Wie wir bereits erwähnt haben, ist ROW_NUMBER() eine Fensterfunktion. Es gibt noch viele andere Fensterfunktionen, die Sie in Ihren Abfragen verwenden können, z. B. AVG(), MAX(), LEAD(), LAG() und FIRST_VALUE(). Wenn Sie sich näher damit befassen möchten, empfehle ich Ihnen den LearnSQL-Kurs Fensterfunktionen . Es handelt sich dabei um ein Schritt-für-Schritt-Tutorial, das Sie anhand von Beispielen und Übungen durch die SQL-Fensterfunktionen führt.

Tiefer gehen: Die Klauseln Partition By und Order By

Im vorangegangenen Abschnitt haben wir die einfachste Art der Verwendung der Fensterfunktion ROW_NUMBER() behandelt, d. h. die Nummerierung aller Datensätze in der Ergebnismenge in keiner bestimmten Reihenfolge. In den nächsten Abschnitten sehen wir uns drei Beispiele mit zusätzlichen Klauseln an, wie PARTITION BY und ORDER BY.

In unserem ersten Beispiel nummerieren wir die Datensätze mit einer anderen Reihenfolge für jeden Unternehmenszweig, die nach den in diesem Zweig verkauften Einheiten geordnet sind. In der nächsten Abfrage gruppiert die Klausel PARTITION BY branch die Datensätze, die denselben Wert in branch haben, und weist jeder Gruppe/Zweigstelle eine andere ROW_NUMBER Sequenz zu. (Jede Gruppe hat in der Abbildung unten eine andere Farbe.) Die ORDER BY units_sold Klausel definiert die Reihenfolge, in der die Zeilen innerhalb der Partition verarbeitet werden. In diesem Fall werden die Zeilen, die zu jeder Partition gehören, nach unit_sold in absteigender Reihenfolge geordnet.

SELECT 
   ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num,
   article_code,
   article_name,
   branch,
   units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Beachten Sie, dass in dieser Abfrage die Sequenzen pro Zweig zugewiesen werden - in der Abbildung unten hat jede Gruppe von Datensätzen eine andere Farbe - und nach units_sold geordnet sind. Die von uns verwendete Klausel lautet:

ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC)

Diese Klausel ermöglicht es uns, Gruppen von Datensätzen zu nummerieren, die denselben Wert in der Zweigspalte haben. In unserem Beispiel gibt es zwei Gruppen von Datensätzen: New York (rot) und San Francisco (blau). Nun wird jede Gruppe von Datensätzen auf der Grundlage der Spalte units_sold nummeriert (die Klausel ORDER BY ). Die Werte werden in absteigender Reihenfolge angezeigt.

Ergebnistabelle

Die Klauseln OVER, PARTITION BY und ORDER BY sind in Fensterfunktionen sehr gebräuchlich; wenn Sie sich näher damit befassen möchten, empfehle ich Ihnen, den Artikel Beispiele für Fensterfunktionen zu lesen, in dem mehrere Beispiele für Fensterfunktionen ausführlich erläutert werden.

Verwendung von ROW_NUMBER zum Entfernen von Duplikaten

Ein weiterer interessanter Anwendungsfall für die Funktion ROW_NUMBER() ist, wenn wir vollständig doppelte Datensätze in einer Tabelle haben. Vollständig doppelte Datensätze entstehen, wenn die Tabelle mehr als einen Datensatz mit denselben Werten in allen Spalten enthält (normalerweise aufgrund eines früheren Fehlers). Wir werden einen SQL-Code zeigen, mit dem diese Situation behoben werden kann; außerdem kann dieser Code an jeden Fall von vollständig doppelten Datensätzen angepasst werden.

Fügen wir zunächst einige vollständig duplizierte Datensätze in die Sales Tabelle ein. Angenommen, wir haben keinen Primärschlüssel in der Sales Tabelle und ein SQL-Entwickler führt fälschlicherweise die folgende INSERT -Anweisung aus:

INSERT INTO sales 
SELECT * FROM sales WHERE branch = 'San Francisco';

Nach der Ausführung von INSERT sieht die Tabelle Sales wie folgt aus. Die letzten beiden Zeilen sind vollständige Duplikate:

Tabelle: Sales

Um die doppelten Datensätze zu entfernen, fügen wir eine neue Spalte mit dem Namen row_num hinzu und füllen sie mit der folgenden INSERT, die die Funktion ROW_NUMBER() verwendet. Beachten Sie, dass wir PARTITION BY alle Spalten in der Tabelle. Hier ist der SQL-Code:

ALTER TABLE sales ADD COLUMN row_num INTEGER;
INSERT INTO sales 
SELECT 
 article_code, article_name, branch, units_sold, period, 
 ROW_NUMBER() OVER (PARTITION BY article_code,article_name,branch, units_sold,period) 
FROM sales ;

Nachdem wir die neue Spalte hinzugefügt und mit ROW_NUMBER() aufgefüllt haben, sieht unsere Tabelle wie folgt aus:

Tabelle: Sales

Es ist leicht zu erkennen, dass wir alle Datensätze mit einer NULL oder einer 2 in der Spalte row_num entfernen müssen. Wir tun dies mit dem Befehl DELETE. Danach müssen wir die Spalte row_num entfernen. Hier ist der Code:

DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2;
ALTER TABLE sales DROP COLUMN row_rank;

Nach der Ausführung der Anweisungen DELETE und ALTER ist die Tabelle Sales fest, ohne doppelte Datensätze.

Es gibt einen Nachteil bei diesem Ansatz, den wir klären müssen. An einem bestimmten Punkt des Prozesses wird die Anzahl der Datensätze in der Tabelle Sales verdoppelt werden. Das kann diese Methode ineffizient machen, besonders bei großen Tabellen. Wir empfehlen daher, sie nur bei kleinen und mittleren Tabellen anzuwenden.

Erstellen eines Ranking-Berichts mit ROW_NUMBER

In diesem Abschnitt werden wir die Funktion ROW_NUMBER() verwenden, um eine Rangliste zu erstellen. Wir werden sehen, dass es bessere Funktionen für eine Rangliste gibt, wie RANK und DENSE_RANK; wir können jedoch einen ziemlich guten Ranglistenbericht erstellen, indem wir ROW_NUMBER() verwenden.

Nehmen wir an, dass unsere Autoverkaufsfirma einmal im Jahr drei Prämien an ihre Verkäufer vergibt: eine Prämie für die Person, die die meisten Einheiten verkauft hat, eine weitere Prämie für die Person, die den höchsten Umsatz erzielt hat, und die dritte Prämie für die Person, die den höchsten Gewinn erzielt hat. Wenn eine Prämienkategorie von zwei Verkäufern gewonnen wird, erhalten beide 50 % der Prämie. Wir verwenden die Tabelle Sellers_2019 um die Rangfolge zu ermitteln und die Gewinner der einzelnen Boni zu bestimmen.

Tabelle: Sellers_2019

Die folgende Abfrage gibt die Ranglisten zurück, die wir benötigen, um die Bonusgewinner zu bestimmen. Diese Ranglisten werden für die Spalten units_ranking, revenue_ranking und profit_ranking erstellt.

SELECT  seller_name,
	  ROW_NUMBER() OVER (ORDER BY units_sold desc) units_ranking,
	  ROW_NUMBER() OVER (ORDER BY revenue desc) revenue_ranking,
	  ROW_NUMBER() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

Das nächste Bild zeigt die Ergebnisse der vorherigen Abfrage mit der Funktion ROW_NUMBER(). Auf den ersten Blick scheinen die Ranglistenwerte korrekt zu sein. Wenn wir sie jedoch im Detail betrachten, können wir einen Fehler bei Susan Graue in Units_ranking feststellen. Susan (und John Doyle) haben den Bonus für verkaufte Einheiten mit 123 Einheiten gewonnen. Die Funktion ROW_NUMBER weist jedoch John die Position 1 und Susan die Position 2 zu, was nicht korrekt ist.

Ergebnistabelle

In diesem Fall ist die Funktion ROW_NUMBER() also nicht die beste Wahl für Ranglistenberechnungen. Glücklicherweise bietet SQL zwei Funktionen speziell für Ranglistenzwecke: RANK() und DENSE_RANK(). In der nächsten Abfrage wird die Funktion RANK() anstelle der Funktion ROW_NUMBER() verwendet:

SELECT  seller_name,
	  RANK() OVER (ORDER BY units_sold desc) units_ranking,
	  RANK() OVER (ORDER BY revenue desc) revenue_ranking,
	  RANK() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

In der nächsten Abbildung sehen wir die Ergebnisse der Abfrage RANK(). Wir können feststellen, dass das Problem mit dem Rang von Susan gelöst ist.

Ergebnistabelle

Wenn Sie an den Fensterfunktionen RANK() und DENSE_RANK() interessiert sind, empfehle ich Ihnen, den Artikel Verwendung von Rangfunktionen in SQL zu lesen. Dort finden Sie mehrere Beispiele und Abfragen.