Überblick über Ranking-Funktionen in SQL

SQL-Ranking-Funktionen erleichtern die Arbeit mit relationalen Datenbanken, insbesondere für Datenanalysten, Marketingspezialisten und Finanzfachleute. Diese Funktionen dienen dazu, jedem Datensatz eine Rangnummer zuzuweisen und ermöglichen es Ihnen, effizient nützliche Berichte zu erstellen.

SQL-Ranking-Funktionen sind Fensterfunktionen. Fensterfunktionen berechnen das Ergebnis auf der Grundlage einer Reihe von Zeilen. Das Wort "Fenster" bezieht sich auf diese Reihe von Zeilen. Schauen wir uns die Syntax von Ranglistenfunktionen an:

rank_function OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC] ...
)

Die Syntax beginnt mit dem Namen der Ranking-Funktion, z. B. RANK(), DENSE_RANK(), ROW_NUMBER() oder PERCENT_RANK(), und der Klausel OVER(). In der Klausel OVER() geben Sie die Klauseln PARTITION BY und ORDER BY an. Für Ranking-Funktionen ist die ORDER BY-Klausel, die den/die Namen der Spalte(n) oder einen Ausdruck enthält, obligatorisch.

Vor der Klausel ORDER BY steht die optionale Klausel PARTITION BY, die den/die Namen der Spalte(n) oder einen Ausdruck enthält. Die PARTITION BY -Klausel unterteilt die Menge der Zeilen in Gruppen von Zeilen für getrennte Rangfolgen.

Machen Sie sich keine Sorgen, wenn Ihnen diese Syntax kompliziert erscheint. Ich werde sie in den folgenden Abschnitten Schritt für Schritt erklären.

Die Beispiele in den folgenden Abschnitten verwenden die Tabelle sale, die Daten in den Spalten salesman_id, sale_date und sale_amount speichert. Siehe den Tabellenverkauf unten:

ROW_NUMBER()

Die erste Rangordnungsfunktion, die ich besprechen werde, ist ROW_NUMBER(). Sie gibt die fortlaufende Nummer jedes Datensatzes in der Ergebnismenge oder innerhalb der Partition der Ergebnismenge zurück, beginnend mit 1. Mit ROW_NUMBER() können Sie alle Datensätze auswählen und sie nummerieren. Siehe Beispiel 1 unten.

Beispiel 1

SELECT 
ROW_NUMBER() OVER(ORDER BY sale_amount) 
  AS row_number, 
sale_date, 
salesman_id,
sale_amount 
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

In diesem Fall enthält die OVER -Klausel nur die ORDER BY -Klausel mit der Spalte sale_amount (diese Klausel sortiert die Zeilen nach dem Verkaufsbetrag aufsteigend von $9.500 bis $31.000). Die Abfrage gibt die fortlaufende Nummer beginnend mit 1 in der Spalte row_number zurück.

Beachten Sie, dass Zeilen, die denselben Wert in der Spalte sale_amount haben, unterschiedliche Nummern erhalten. Wenn Sie also alle Datensätze mit einer eindeutigen Nummer ordnen möchten, verwenden Sie ROW_NUMBER().

Was, wenn Sie einzelne Gruppen von Zeilen nummerieren möchten? Sie können die optionale Klausel PARTITION BY vor der ORDER BY-Klausel verwenden. Siehe Beispiel 2 unten.

Beispiel 2

SELECT
ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_amount) 
  AS row_number, 
sale_date, salesman_id, sale_amount 
FROM sale;

Die Datensätze werden nach dem Verkaufsdatum in Gruppen (sogenannte "Partitionen") unterteilt. Innerhalb jeder Unterteilung werden die Datensätze separat nummeriert.

Diese Abfrage gibt das Ergebnis zurück:

Für das Verkaufsdatum 2020-04-20 haben die Zeilen die Nummern 1 und 2, obwohl die Werte der Verkaufsbeträge gleich sind. In der nächsten Partition hat die Zeile mit dem niedrigeren Wert des Verkaufsbetrags die Nummer 1 und die Zeile mit dem höheren Wert des Verkaufsbetrags hat die Nummer 2. Die Datensätze innerhalb jeder Partition werden nach der Spalte in ORDER BY sortiert.

RANK()

Die zweite Rangordnungsfunktion ist RANK(). Diese Funktion fügt jeder Zeile in der Ergebnismenge oder innerhalb der Partition der Ergebnismenge eine Rangnummer hinzu, die eine fortlaufende Nummer ist.

Der Unterschied zwischen RANK() und ROW_NUMBER() besteht darin, dass RANK() doppelte Werte überspringt. Wenn es doppelte Werte gibt, wird derselbe Rang zugewiesen, und für jeden doppelten Rang erscheint eine Lücke in der Reihenfolge.

Siehe Beispiel 1 unten.

Beispiel 1

SELECT
RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

In diesem Fall weist RANK() eine Rangnummer für jeden Datensatz wie ROW_NUMBER() zu, aber für denselben Wert in sale_amount ist die Rangnummer dieselbe. Die Verkäufer 11 und 13 haben am 22.04.2020 den gleichen Verkaufsbetrag von 11.000 $ erzielt. Daher haben sie dieselbe Rangnummer, 2. In diesem Fall hat ROW_NUMBER() eine andere Rangnummer zugewiesen.

Beachten Sie, dass der nächste Datensatz nicht die Nummer 3 hat. RANK() überspringt die Rangnummer(n) der weiteren Zeilen mit demselben Wert. Nach zwei Zeilen mit der Rangnummer 2 ist die nächste Rangnummer also 4, nicht 3.

Natürlich vergibt RANK() auch Nummern innerhalb von Partitionen. Siehe Beispiel 2 unten.

Beispiel 2

SELECT
RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

Die obige Abfrage hat die Ergebnismenge in Gruppen von Zeilen mit dem gleichen Verkaufsdatum unterteilt. Zum Beispiel enthält eine Partition die Verkäufe am 2020-04-22. Die Zeilen werden für jedes Verkaufsdatum separat nummeriert.

Am 2020-04-22 haben die Verkäufer 11 und 13 denselben Verkaufsbetrag von $11000. Daher haben diese beiden Datensätze die Rangnummer 1, und der nächste Datensatz hat die Rangnummer 3, weil die zusätzliche Zeile übersprungen wird.

DENSE_RANK()

Die dritte Rangordnungsfunktion ist DENSE_RANK(). Wenn Sie Zeilen mit dem gleichen Wert in einer bestimmten Spalte die gleiche Nummer zuweisen, aber die nächsten Nummern nicht überspringen möchten, verwenden Sie DENSE_RANK().

DENSE_RANK() Die Funktion DENSE_RANK() ist ähnlich wie RANK(), aber bei DENSE_RANK() wird die Rangnummer bei gleichen Werten nicht übersprungen. Siehe Beispiel 1 unten.

Beispiel 1

SELECT
DENSE_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

Für den gleichen Verkaufsbetrag haben die Zeilen die gleiche Nummer. Die nächsten Zeilen werden jedoch nicht übersprungen, sondern haben die nächste fortlaufende Nummer.

Beachten Sie, dass für den gleichen Verkaufsbetrag von 11.000 $ durch die Verkäufer 11 und 13 am 22.04.2020 die zugewiesene Rangnummer 2 ist, aber für die nächsten beiden Datensätze mit dem Verkaufsbetrag von 12.500 $ ist die Rangnummer 3. Diese Funktion überspringt die nächste Nummer nicht.

RANK() funktioniert anders. In diesem Fall würde RANK() für den Verkaufsbetrag von 12.500 $ die Rangnummer 4 zuweisen und die 3 überspringen, weil zwei Zeilen die 2 hatten.

DENSE_RANK() funktioniert auch mit Partitionen. Siehe Beispiel 2 unten.

Beispiel 2

SELECT
DENSE_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

In diesem Fall, am 2020-04-22, haben die Verkäufer mit dem Verkaufsbetrag von $11.000 die Rangnummer 1, aber der nächste Datensatz hat die Rangnummer 2, nicht 3 wie bei RANK().

PERCENT_RANK()

Die letzte Rangordnungsfunktion, die ich besprechen werde, ist PERCENT_RANK(). Diese Funktion gibt die prozentualen Ränge zurück. Siehe Beispiel 1 unten.

Beispiel 1

SELECT
PERCENT_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

Diese Abfrage berechnet den relativen Rang der einzelnen Zeilen in der Ergebnismenge. Dem höchsten Wert des Verkaufsbetrags wird 1 als prozentualer Rang zugewiesen, dem niedrigsten Wert 0. Die Werte dazwischen werden als Rang aus einem Bereich von Werten zurückgegeben, die größer als 0 und kleiner als 1 sind.

In der Mitte zwischen dem höchsten und dem niedrigsten Wert liegt der Prozentrang bei 0,5. Hier wird den Verkäufern 11 und 12 am 20.04.2020 die prozentuale Rangnummer 0,5 zugewiesen. Die Datensätze der Verkäufer 11 und 13 am 22.04.2020 liegen zwischen 0 und 0,5, also haben sie die Prozentrangnummer 0,1666666.

PERCENT_RANK() funktioniert in ähnlicher Weise für Partitionen von Datensätzen. Siehe Beispiel 2 unten.

Beispiel 2

SELECT
PERCENT_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Diese Abfrage gibt das Ergebnis zurück:

In jeder Partition hat die höchste zurückgegebene Zeile den Prozentrang 1 und die niedrigste den Rang 0. In diesen Partitionen gibt es keine Datensätze zwischen dem höchsten und dem niedrigsten. Daher gibt es keine Prozentrangzahl zwischen 0 und 1 wie im vorherigen Beispiel.

Zusammenfassung

In diesem Artikel habe ich die SQL-Ranking-Funktionen besprochen, ihre Syntax erklärt und sie in praktischen Beispielen verwendet. Wenn Sie mehr über Ranking-Funktionen erfahren möchten, schauen Sie sich den Kurs "Fensterfunktionen" auf LearnSQL.de oder lesen Sie die Artikel "Beispiel für SQL Fensterfunktionen mit erklärungen," "How to Use Rank Functions in SQL," und "Common SQL Window Functions: Using Partitions With Ranking Functions."