RANK

Was tut RANK?

Wie schon die in der letzten Woche vorgestellte ROW_NUMBER Funktion, ist auch RANK eine Window Function, also eine Funktion, die die Daten sortiert in sogenannten “Fenstern” oder “Partitionen” abarbeitet. Die Sortierung und die Partitionen definiert ihr, indem ihr nach dem Funktionsaufruf die Schlüsselworte ORDER BY und PARTITION BY verwendet. Die RANK-Funktion nummeriert dann alle Zeilen in der Ergebnismenge, anfangend bei 1 aufsteigend durch. Dabei startet jede Partition wieder bei 1. Anders als die ROW_NUMBER-Funktion, nummeriert die RANK-Funktion dabei Zeilen, die in der Sortierung an derselben Stelle stehen, mit derselben Nummer. Bei ROW_NUMBER wird hier eine zufällige Sortierung gewählt und eindeutig fortlaufend nummeriert.

Wie könnt ihr mit RANK arbeiten?

Um die RANK Funktion zu verwenden, erstellen wir uns nun in der Wide World Importers Datenbank eine Abfrage, die sortierbare Artikel liefert, bei denen die Sortierung aber nicht eindeutig sein muss. Dafür verwenden wir eine CTE mit der wir aus den OrderLines die Anzahl der Bestellungen zählen, in denen jedes Produkt auftauchte:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     StockItemID
    ,nOrders
    ,TotalSales
FROM cte_sales 
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

Nun möchten wir aber nicht mehr nur zählen und ausgeben, sondern eine Rangliste der populärsten Produkte erstellen. Würden wir hierfür die ROW_NUMBER-Funktion verwenden, so hätten die beiden populärsten Produkte mit den IDs 104 und 120, die in jeweils 1123 mal bestellt wurden die Nummern 1 und 2, welches die 1 und welches die 2 hätte, könnten wir beispielsweise über die Sortierung nach TotalSales bestimmen:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     StockItemID
    ,nOrders
    ,TotalSales
    ,ROW_NUMBER() OVER (ORDER BY nOrders DESC, TotalSales DESC) as Popularity
FROM cte_sales 
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

Unter kaufmännischen Gesichtspunkten mag diese zusätzliche Sortierung nach den Verkaufssummen sinnvoll sein, jedoch spiegelt sie ja nicht die tatsächliche Popularität des Produkts wider. Wenn wir rein nach Verkaufszahlen gehen, müssten nämlich beide Produkte auf der Nummer eins stehen und genau das ermöglicht die RANK-Funktion. Hierbei können wir dann auch auf den Tiebreaker verzichten, der zwischen der ersten und der zweiten Zeile unterschieden hat und brauchen dementsprechend nur noch ein ORDER BY Argument:

WITH cte_sales AS (
    SELECT 
         StockItemID
        ,COUNT(*) AS nOrders
        ,SUM(Quantity*UnitPrice) AS TotalSales 
    FROM 
        Sales.OrderLines
    GROUP BY StockItemID
)
SELECT  
     StockItemID
    ,nOrders
    ,TotalSales
    ,ROW_NUMBER() OVER (ORDER BY nOrders DESC, TotalSales DESC) as Popularity
    ,RANK() OVER (ORDER BY nOrders DESC) as Real_Popularity
FROM cte_sales 
ORDER BY 
     nOrders DESC
    ,TotalSales DESC

Im Resultat seht ihr, dass nun beide Produkte, 104 und 120 die (echte) Popularität 1 erhalten haben, während dem nächsten Produkt, ähnlich wie das bei Sportwettkämpfen üblich ist, der dritte Rang zugewiesen wurde. Wenn ihr also anhand der Daten nicht unterscheiden könnt, welche Zeile nun die “bessere” ist und eine zufällige Nummerierung vermeiden möchtet, dann verwendet statt der ROW_NUMBER-Funktion lieber die RANK-Funktion. Zum Aussortieren von Doubletten eignet sie sich aber natürlich im Gegenzug nicht, da die Dubletten hier alle denselben Rang zugewiesen bekämen…

Referenzen

• Offizielle Dokumentation von Microsoft