Wie man das n-te höchste Gehalt nach Abteilung mit SQL findet

Lernen Sie, wie man das n-te höchste Gehalt in SQL findet, und Sie werden lernen, wie man den n-ten Wert in beliebigen Daten erhält.

In diesem Artikel werde ich mich auf ein Problem konzentrieren und Ihnen mehrere Lösungen für dieses Problem geben. Das Problem ist bereits im Titel genannt: Finde das n-te höchste Gehalt nach Abteilung mit SQL.

Das mag zu spezifisch klingen. Aber wenn Sie lernen, wie man diese Aufgabe löst, können Sie den n-ten Wert in beliebigen Daten finden, nicht nur in Gehaltsdaten. Sie werden eine Vorstellung davon bekommen, wie Sie andere ähnliche Probleme lösen können, die Ihnen begegnen.

Wie ich schon sagte, werden wir nur ein Problem betrachten, aber mehrere Lösungen dafür erforschen. Tatsächlich werde ich Ihnen vier Lösungen zeigen, die diese Fensterfunktionen verwenden:

• NTH_VALUE()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Wenn Sie Hilfe bei der Syntax oder Details zu diesen Funktionen benötigen, können Sie das SQL Fensterfunktionen Cheat Sheet zu Rate ziehen. Sie können auch einen Blick auf dieses Beispiel werfen, um zu sehen, wie Fensterfunktionen im Allgemeinen funktionieren.

Die zu verwendenden Daten

Wir werden Daten verwenden, die in den folgenden zwei Tabellen gespeichert sind:

• employee
• department

So sehen die Daten in der Tabelle aus employee aussehen:

Die Tabelle department hat die folgenden Daten:

Was ist hier die Aufgabe?

Wir wollen das dritthöchste Gehalt nach Abteilung finden. Das bedeutet, dass wir den dritthöchsten Wert nicht insgesamt, sondern innerhalb jeder Untergruppe finden müssen, wobei eine Untergruppe die Gehälter für eine bestimmte Abteilung enthält. Das hilfreichste Werkzeug hierfür sind die Fensterfunktionen.

Hier ist also die erste Lösung mit Hilfe einer Fensterfunktion.

Verwendung von NTH_VALUE()

Der Zweck der Funktion NTH_VALUE() ist es, den Wert der n-ten Zeile im Datensatz zu ermitteln. So können wir sie verwenden, um das dritthöchste Gehalt nach Abteilung zu ermitteln:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		NTH_VALUE (salary, 3) OVER (
PARTITION BY department_name ORDER BY salary DESC
			RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest_salary
FROM department d JOIN employee e ON d.id = e.department_id;

Dieser Code wählt zunächst einige Spalten aus den Tabellen employee und department. Um NTH_VALUE() zu verwenden, müssen wir die Spalte und den Wert von N angeben. Da wir das dritthöchste salary erhalten wollen, ist die Spalte das Gehalt und N = 3; daher haben wir NTH_VALUE(salary, 3). Damit erhalten wir das dritthöchste Gehalt.

Damit eine Fensterfunktion funktioniert, müssen wir eine OVER() Klausel verwenden. Erinnern Sie sich, wir suchen nach dem dritthöchsten Gehalt in jede Abteilung. Dazu verwenden wir PARTITION BY, um die Daten nach der Spalte department_name aus der Tabelle in Teilmengen aufzuteilen department. Da wir nach dem dritthöchstenhöchste Gehalt suchen, verwenden wir ORDER BY, damit die Fensterfunktionen die Berechnungen vom höchsten zum niedrigsten Gehalt durchführen. Dies ist der Grund für ORDER BY salary DESC im Code. Außerdem benötigen wir die Klausel RANGE, in diesem Fall RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Das bedeutet, dass die Fensterfunktion zwischen der ersten und der letzten Zeile der Partition suchen wird.

Um all dies zu erhalten, müssen wir natürlich die beiden Tabellen verbinden.

Hier ist das Ergebnis:

Das Ergebnis zeigt uns, dass das dritthöchste Gehalt in der Buchhaltung 5.371,02 beträgt, das ist das Gehalt von Lois Skain. Abra Clemons Gehalt (5.564,25) ist das dritthöchste Gehalt in der Personalabteilung. Im Bereich Forschung und Entwicklung ist das Gehalt von Zachariah Rapi das dritthöchste (6.657,11).

Verwendung von ROW_NUMBER()

Die zweite Möglichkeit, das dritthöchste Gehalt nach Abteilung zu ermitteln, ist die Verwendung von ROW_NUMBER(). Diese Fensterfunktion gibt die laufenden Nummern der Zeilen in einem Datensatz zurück. Wenn wir die Gehälter innerhalb jeder Abteilung ordnen, ist es einfach, das dritthöchste Gehalt zu ermitteln. So sieht die Lösung aus:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		ROW_NUMBER() OVER (
PARTITION BY d.id ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
ORDER BY department_name;

Bei dieser Lösung werden die gleichen Spalten ausgewählt wie bei der vorherigen Lösung. Der Unterschied besteht darin, dass wir hier ROW_NUMBER() anstelle von NTH_VALUE() verwenden. Alle Kriterien in der Klausel OVER() sind dieselben wie in der vorherigen Lösung.

Das Ergebnis sieht wie folgt aus:

Es sieht ein wenig anders aus als das Ergebnis der vorherigen Lösung. Die letzte Spalte enthält nicht den Wert für das dritthöchste Gehalt. Stattdessen enthält sie eine Rangfolge, und man kann leicht erkennen, dass der Effekt derselbe ist. Die Werte des dritthöchsten Gehalts nach Abteilung sind 5.371,02 (Buchhaltung), 5.564,25 (Personalwesen) und 6.657,11 (Forschung und Entwicklung), was dem Ergebnis der ersten Lösung entspricht.

Wir können auch ROW_NUMBER() in einem CTE verwenden. Wenn Sie mit CTEs nicht vertraut sind, wird in diesem Artikel erklärt, was das ist. Wir können zum Beispiel den folgenden Code schreiben:

WITH salaries_ranks AS (
SELECT e.first_name,
 e.last_name,
 d.department_name,
 salary,
 ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY salary DESC
 ) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
)

SELECT *
FROM salaries_ranks
WHERE salary_rank = 3;

Wir beginnen unser CTE mit einem WITH, gefolgt vom CTE-Namen, salaries_ranks. Dann schreiben wir eine SELECT Anweisung, die genau die gleiche ist wie im vorherigen Code.

Nun, da wir eine CTE geschrieben haben, können wir sie wie jede andere Tabelle verwenden. Wir tun dies in einer separaten SELECT Anweisung, die alle Spalten aus der CTE auswählt salaries_ranksauswählt und nur die Zeilen anzeigt, in denen salary_rank = 3 steht.

Das Ergebnis dieses Codes ist:

Dies ist eine recht elegante Lösung. Wir erhalten nur die notwendigen Daten - nur drei Zeilen mit dem dritthöchsten Gehalt für jede Abteilung.

Verwendung von RANK()

Die dritte Möglichkeit besteht darin, die Funktion RANK() zu verwenden. Sie ähnelt ROW_NUMBER() insofern, als sie auch die Zeilen innerhalb einer Partition ordnet. Ähnlich, aber nicht dasselbe. Ich werde später auf die Unterschiede eingehen, oder Sie können sie hier nachlesen.

Lassen Sie uns den Code mit RANK() schreiben:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Auch dieser Code unterscheidet sich nicht wesentlich von den vorherigen, mit Ausnahme der verwendeten Fensterfunktion. Hier ist es RANK(), mit den gleichen Kriterien in der OVER() Klausel. Das Ergebnis ist genau dasselbe wie bei der Verwendung von ROW_NUMBER(), so dass ich nicht glaube, dass es notwendig ist, die gleiche Ergebnistabelle noch einmal zu zeigen.

Verwendung von DENSE_RANK()

Die letzte Lösung, die ich Ihnen hier zeigen möchte, ist die Fensterfunktion DENSE_RANK(). Genau wie ROW_NUMBER() und RANK() ordnet sie die Werte innerhalb eines Datensatzes.

Der Code ist auch nicht wirklich anders. Es ist derselbe wie zuvor, nur unter Verwendung einer anderen Fensterfunktion:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		DENSE_RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC 
		) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Wenn wir diesen Code ausführen, erhalten wir genau das gleiche Ergebnis wie bei den beiden vorherigen Lösungen.

Überblick über die Konzepte und ihre Unterschiede

Wie Sie gesehen haben, können Sie jede dieser vier Fensterfunktionen verwenden, um das dritthöchste Gehalt nach Abteilung zu ermitteln.

Die Funktion NTH_VALUE() zeigt Ihnen explizit den Wert des dritthöchsten Gehalts nach Abteilung an.

Die Funktionen ROW_NUMBER(), RANK() und DENSE_RANK() ordnen die Gehälter innerhalb jeder Abteilung. Sie können dann einfach den Gehaltswert finden, der dem Rang 3 entspricht. Diese drei Funktionen sind ähnlich, aber nicht identisch. In diesem Beispiel scheinen sie identisch zu sein, aber andere Daten können ihre Unterschiede deutlich machen.

Um Ihnen unangenehme Überraschungen zu ersparen, wird hier erklärt, wie sie sich unterscheiden.

Die Unterschiede kommen zum Vorschein, wenn Sie in Ihren Daten Verknüpfungen (mehrere Zeilen mit demselben Wert) haben. Da ich wusste, dass meine Daten keine Verknüpfungen enthielten, würde ich mit allen drei Funktionen die gleiche Lösung erhalten.

Aber was ist, wenn es doch Verknüpfungen gibt? In diesem Fall würde ROW_NUMBER() die Ränge sequentiell zuweisen, d. h. die Verknüpfungen hätten unterschiedliche Rangnummern, die willkürlich zugewiesen werden. Im Gegensatz dazu weist RANK() den gleichen Werten die gleiche Rangnummer zu und überspringt eine Rangnummer, um sie zu kompensieren, wenn es zu einer Zeile mit einem anderen Wert kommt. DENSE_RANK() schließlich weist Gleichständen die gleiche Rangnummer zu, ohne eine Rangnummer beim nächsten Wert zu überspringen. Hier ein Beispiel zur Veranschaulichung:

Wenn Sie nach dem Wert des dritthöchsten Gehalts in diesen Daten suchen, liefert ROW_NUMBER() die richtige Lösung: Das Gehalt von 4.000 ist das dritthöchste. Wenn Sie jedoch nach allen Arbeitnehmern mit dem dritthöchsten Gehalt suchen, erhalten Sie eine falsche Lösung. Es würde nur Angestellter 2 angezeigt, obwohl auch Angestellter 5 angezeigt werden sollte. In diesem Fall wäre RANK() oder DENSE_RANK() die bessere Wahl.

Was ist, wenn Sie nach dem Wert des vierthöchsten Gehalts suchen? Die Funktion ROW_NUMBER() würde Ihnen eine völlig falsche Lösung liefern, da der dritthöchste und der vierthöchste Wert identisch sind. Die Funktion RANK() liefert überhaupt kein Ergebnis, da sie den vierten Rang überspringt - wie bereits erläutert, wird bei Unentschieden die gleiche Rangnummer vergeben und die nächste Rangnummer übersprungen, um dies auszugleichen. Nur DENSE_RANK() liefert Ihnen in diesem Fall eine korrekte Lösung.

Bevor Sie sich für eine Funktion entscheiden, sollten Sie sich über die Daten im Klaren sein und wissen, was Sie als Lösung erhalten möchten. Der sicherste Weg ist die Verwendung von NTH_VALUE(). Wenn Sie eine Ranking-Funktion verwenden möchten, ist es im Allgemeinen am besten, DENSE_RANK() zu benutzen, wenn Sie nicht wissen, ob es in den Daten irgendwelche Gleichheiten gibt. Wenn Sie stattdessen ROW_NUMBER() oder RANK() verwenden, stellen Sie sicher, dass Sie wissen, wie das Ergebnis aussehen würde. Vielleicht gibt Ihnen eine oder beide dieser Funktionen, was Sie brauchen. Wählen Sie die Funktion entsprechend Ihren Bedürfnissen.

Sie können diese vier Funktionen auch verwenden, wenn Sie das Höchst- oder Mindestgehalt nach Abteilung ermitteln möchten. Oder, zum Beispiel, das teuerste oder das billigste Produkt nach Produktkategorie. Oder ein beliebiges Produkt mit dem n-ten höchsten Preis. Sie können auch nach dem n-ten höchsten oder dem n-ten niedrigsten Umsatz, Ertrag, Arbeitsstunden, Kosten, Anzahl der Likes, Logins, Engagements, Streams, Kommentare usw. suchen, was auch immer.

Wenn Sie MySQL verwenden, wird diese umfassende Einführung Ihnen diese vier und alle anderen Fensterfunktionen in MySQL zeigen.

Verschiedene Möglichkeiten, den n-ten SQL-Wert zu ermitteln

Die Funktion NTH_VALUE() eignet sich hervorragend, um das n-te höchste Gehalt oder den n-ten Wert einer beliebigen anderen Spalte zu ermitteln. Schließlich wurde sie genau für diesen Zweck entwickelt. Ich habe Ihnen gezeigt, wie Sie den gleichen Effekt mit drei weiteren Fensterfunktionen erzielen können: ROW_NUMBER(), RANK(), und DENSE_RANK(). Verwenden Sie die Funktion, die am besten zu Ihren Bedürfnissen und Daten passt.

Aber das sind nur vier Fensterfunktionen. Das ist nur die Spitze des Eisbergs, und es gibt noch so viel mehr! Diese und andere Fensterfunktionen werden in diesem Kurs über Fensterfunktionen erklärt.