Ranking in SQL

Oft höre ich in meinen Kursen die Sätze: “Hättest du mir das nicht zwei Wochen früher sagen können?”. Oder der Klassiker: “Ohman, wenn ich das gewusst hätte, hätte ich mir eine Menge Zeit gespart!”. Deshalb hier ein Auszug von Ranking-Funktionen.

Was sind Ranking-Funktionen
Wie der Name eigentlich schon sagt wird eine Rangfolge erstellt. Mit diesen Funktionen lassen sich extra Spalten anzeigen die dann z.B. eine Rangfolge über die Kunden bringt die den meisten Umsatz erbracht haben. Das hört sich bis jetzt noch nicht so berauschend an, denn ich könnte auch nach dem Umsatz sortieren mit Order by, dann würde ich den Kunden mit dem größten Umsatz ganz oben sehen.

Hier mal das Beispiel:

Select Companyname, sum(quantity*Unitprice) as Umsatz 
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by Companyname
order by Umsatz desc

Ergebnis würde so ausschauen:

orank ausgabe

Schön und gut, nun das ganze mal mit der Rank-Funktion.

Rank()

Select Companyname, sum(quantity*Unitprice) as Umsatz,
rank() over (order by sum(quantity*unitprice) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by Companyname


So schaut's aus:

rank ausgabe

Wenn wir uns mal den Syntax der Rank-Funktion anschauen ist eigentlich nur zu beachten was in der Klammer steht und nach welcher Spalte platziert werden soll.

Jetzt habe ich zwei Fragen! Was ist wenn ich jetzt noch einen Schritt weiter gehen will und mir die Rangfolge der Kunden pro Land anzeigen lassen will aber immer noch am Umsatz orientiert.

Die Zweite Frage ist: Was passiert mit der Reihenfolge wenn es zwei oder mehr identische Werte gibt?
Wird der Folgerang  übersprungen? Oder zählt er einfach weiter? Was passiert schauen wir uns ein Stück später an, jetzt erst mal zur oberen Frage.

Nun sehen wir die Besonderheit dieser Ranking-Funktion denn sie ist in der Lage ein Ranking auf eine bestimmte Spalte zu machen aber sie noch an einem Unterkriterium zu orientieren.

Ich zeige gleich ein Beispiel das dem oberem gleich ist bis auf die Sache das ich herausfinden will welcher Kunde in jedem Land auf Rank: 1, 2, 3 usw. steht.

Select Companyname, Country, sum(quantity*Unitprice) as Umsatz,
rank() over (partition by Country order by sum(quantity*unitprice) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by Companyname, country


Was hat sich verändert? Ich habe eine weitere Spalte im Select hinzugefügt, und zwar Country. Außerdem habe in der Klammer nach der over-Klausel das Schlüsselwort partition by eingefügt und das führt dazu das er das Ranking nicht anhand der ganzen Tabelle erstellt, stattdessen für das Country.

Das Ergebnis sollte fast alle Fragen beantworten:

 rank partition

Nun wurde ein Ranking für jedes Country durchgeführt.

Soweit so gut, jetzt komm ich auf die Frage von vorher zurück was passiert wenn zwei Einträge den gleichen Umsatz haben? Wie wird der Rang verteilt?

Habe meine Abfrage ein “bisschen” umgebaut damit man das Phänomen von gleichen Werten beobachten kann.
Die Abfrage liefert nun die Bestellung mit den meisten Positionen zurück.

Select o.orderid,  count(od.OrderID) as Anzahl,
rank() over (order by count(o.orderid) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by o.OrderID

Hier mal ein krassen Beispiel:

rank falsch

Diese Platzierung ist meines Erachtens die beste. Ich nehme hier gerne ein Beispiel aus meiner früheren Leichtathletikzeit. 100m Sprint: Es gibt einen 1.Platz, zwei 2.Plätze und somit keinen 3.Platz sondern nur noch einen 4.Platz. Jetzt gibt es noch die Funktion Dense_Rank(). Die macht es anders. Es gibt einen 1.Platz, zwei 2.Plätze und somit einen 3.Platz usw.! Wem die zweite Variante zusagt, der sollte statt Rank() die Dense_Rank()-Funktion benutzen.

Dense_Rank() 

Select o.orderid,  count(od.OrderID) as Anzahl,
dense_rank() over (order by count(o.orderid) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by o.OrderID

Und hier das dazugehörige Ergebnis:

dense_rank

Wie man siehst, ignoriert dense_rank() doppelt vergebene Plätze.

So viel zur Platzierung. Zwecks der Vollständig stelle ich auch noch zwei weitere Funktion vor.

Row_Number()

Was macht Row_Number()? Eigentlich, genau das was es sagt. Die Funktion nummeriert durch, beachtet aber nicht die Werte wie Rank() oder Dense_Rank().

Select o.orderid,  count(od.OrderID) as Anzahl,
row_number() over (order by count(o.orderid) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by o.OrderID

Ausgabe:

row_number

Die Row_number-Funktion kann natürlich auch mit dem Schlüsselwort Partition by verwendet werden.

Letzte Funktion die ich hier beschreibe hört auf den Namen ntile().

ntile()

Ntile() spaltet sich ein bisschen ab von den bisherigen Funktion, denn bei Ntile muss ein Parameter mitgeben werden. Was macht ntile()? Die Funktion teilt die Summe der Einträge durch die Zahl die der Funktion mitgegeben werden. Nehmen wir an wir haben eine Tabelle mit 100 Zeilen und benutzen
ntile(10), dann sollte es 10 Bereiche geben mit jeweils 10 Zeilen. Das Ergebnis der Abfrage wird es verdeutlichen.

Select o.orderid,  count(od.OrderID) as Anzahl,
ntile(10) over (order by count(o.orderid) desc) as Rank
from Customers c 
inner join Orders o on c.customerid = o.customerid
inner join [order Details] od on od.orderid = o.orderid
group by o.OrderID

Das Ergebnis sagt alles:

ntile

Meine Tabelle hat 830 Zeilen und ich teile sie in 10 gleich große Bereiche.
830/10 = 83
Das heißt der erste Bereich geht bis 83, der zweite bis 166, der dritte 249 usw..

Das war’s zu den Ranking-Funktionen.

Ich hoffe das wird dem ein oder anderen eine große Hilfe sein.

Kommentare sind geschlossen