LeetCode SQL Problem und Lösung: Die drei höchsten Gehälter der Abteilung

Sie suchen eine ausführliche Erklärung für die schwierigste Übung im LeetCode SQL 50? Wir haben eine ausführliche Erklärung dieser SQL-Aufgabe zusammengestellt, mit dem Gedankengang hinter der Lösung und einer Anleitung, wie man die Abfrage Schritt für Schritt aufbaut.

LeetCode ist eine beliebte Online-Plattform, die eine Vielzahl von SQL-Aufgaben anbietet, die zum Lernen, Üben und zur Vorbereitung auf Vorstellungsgespräche dienen. Es werden auch Programmieraufgaben in anderen Sprachen angeboten. Die Probleme sind nach Themen wie Algorithmen, Datenstrukturen, Datenbanken, Systemdesign und einzelnen Programmiersprachen gruppiert.

LeetCode enthält auch einen SQL-Lernplan mit 50 Problemen, um Ihnen alle notwendigen Fähigkeiten für ein erfolgreiches Vorstellungsgespräch in den Datenwissenschaften zu vermitteln. Wenn Sie jedoch mehr als nur 50 Probleme zum Lernen und Üben suchen, sollten Sie die über 120 Übungen in unserem SQL für Anfänger Kurs in Betracht ziehen. Wir haben auch eine Sammlung von 100+ SQL-Interview-Fragen, die speziell für Sie zum Üben vor einem Vorstellungsgespräch entwickelt wurden.

In diesem Artikel werden wir uns die schwierigste Aufgabe aus dem SQL-Set von LeetCode genauer ansehen: Abteilung Top Drei Gehälter. Wir besprechen den Gedankengang, der hinter der Lösung steht, und wie man die Abfrage Schritt für Schritt aufbaut. Sind Sie bereit?

Das Problem mit den drei höchsten Abteilungsgehältern

Department Top Three Salaries ist das einzige Hard-Problem innerhalb des SQL 50-Studienplans von LeetCode. Es gibt mehrere Stellen, an denen man hängenbleiben könnte, also lassen Sie uns dieses Problem Schritt für Schritt angehen.

Die Aufgabe

Die Führungskräfte eines Unternehmens möchten wissen, wer in den einzelnen Abteilungen des Unternehmens am meisten Geld verdient. Ein Spitzenverdiener ist ein Mitarbeiter, dessen Gehalt zu den drei höchsten Einzelgehältern in dieser Abteilung gehört.

Schreiben Sie eine Lösung, um die Mitarbeiter zu finden, die in den einzelnen Abteilungen zu den Spitzenverdienern gehören.

Werfen Sie einen Blick auf das Schema der Datenbank für diese Aufgabe:

Die Lösung

Schritt 1: Suche nach den Spitzenverdienern des Unternehmens

Beginnen wir mit der Suche nach Gutverdienern, ohne uns um die Abteilungen zu kümmern. In der Problembeschreibung heißt es, dass ein Spitzenverdiener jeder ist, dessen Gehalt zu den 3 höchsten Einzelgehältern gehört. Das bedeutet, dass, wenn es mehrere Personen mit demselben Gehalt gibt, sie alle zu den Spitzenverdienern gehören. Werfen Sie einen Blick auf die folgende Ergebnistabelle:

Beachten Sie, dass wir zwar nur die vier besten Gehälter auswählen, es aber mehr als vier Spitzenverdiener geben kann. Zum Beispiel haben Will und Max den gleichen Gehaltsbetrag und landen beide auf der Liste der Spitzenverdiener mit dem gleichen Gehaltsrang 3.

Wir können nicht die Basisdaten ORDER BY und LIMIT verwenden, um die Spitzenverdiener zu ermitteln, da wir nicht wissen können, wie viele Spitzenverdiener es gibt, bevor wir die Abfrage ausführen! Aber wir können das Ergebnis nach dem Gehaltsrang filtern. Lassen Sie uns besprechen, wie wir diesen Rang erhalten können, da er nicht in der Datenbank enthalten ist.

Es gibt drei Ranking-Funktionen in SQL: RANK(), DENSE_RANK(), und ROW_NUMBER(). Sie alle geben eine Rangfolge der Werte innerhalb des angegebenen Fensterrahmens und der Ordnungsrichtung zurück. Der Unterschied zwischen ihnen besteht darin, wie sie mit doppelten Werten umgehen. Eine ausführliche Erläuterung zur Verwendung von Ranking-Funktionen finden Sie in unserem Überblick über Ranking-Funktionen in SQL.

Schauen Sie sich das gleiche Szenario wie zuvor an, aber jetzt mit allen Ranking-Funktionen:

Wie Sie sehen können, geben RANK() und DENSE_RANK() den gleichen Rang zurück, wenn die Gehälter gleich sind; dies ist das Verhalten, das wir brauchen, um sicherzustellen, dass mehrere Personen als Spitzenverdiener aufgenommen werden können, wenn sie das gleiche Gehalt haben. Bei Verwendung von ROW_NUMBER() würde Max von der Top-3-Liste ausgeschlossen, obwohl er ein Top-3-Gehalt hat.

Der Unterschied zwischen RANK() und DENSE_RANK() besteht darin, wie sie mit Lücken in der Rangliste umgehen, die durch doppelte Werte entstehen. RANK() überspringt eine fortlaufende Rangnummer für jeden doppelten Rang, was dazu führt, dass nicht doppelte Werte denselben Rang haben. DENSE_RANK() überspringt keine Ränge im Falle von Duplikaten. Das ist genau das, was wir für dieses Problem wollen, denn es verhindert, dass ein Top-3-Gehalt ausgelassen wird, wenn es zwei oder mehr Top-2-Gehälter gibt.

Hier ist eine Abfrage, die allen Mitarbeitern einen Rang auf der Grundlage ihres Gehalts zuweist ( DENSE_RANK()):

SELECT
  e.name,
  e.salary,
  DENSE_RANK() OVER(ORDER BY e.salary DESC)
FROM employee e;

Großartig! Wir erhalten nun das gleiche Ergebnis wie im ersten Beispiel: Allen Mitarbeitern wird ein Rang zugewiesen, der ihrem Gehaltsrang entspricht. Der nächste Schritt besteht darin, für jede Abteilung eine eigene Rangliste zu erstellen.

Schritt 2: Suche nach den Spitzenverdienern der Abteilung

Wir können eine PARTITION BY innerhalb der OVER() Klausel hinzufügen, um den Fensterrahmen für jede Zeile auf der Grundlage eines bestimmten Wertes anzugeben.

In diesem Problem wollen wir eine Rangliste der Top-3-Gehälter in jeder Abteilung erstellen, also sollten wir den Fensterrahmen nach der Abteilungs-ID partitionieren. Einen tieferen Einblick in diesen Ausdruck erhalten Sie in unserem Artikel Wie man PARTITION BY mit OVER() verwendet.

Hier ist die Abfrage, die die Ränge für jede Abteilung separat zuweist:

SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId;

Wir müssen die Felder department und employee verbinden, weil die Aufgabe uns auffordert, die Abteilungsnamen anzuzeigen. Die PARTITION BY Klausel sollte jedoch die department.id Spalte (den Primärschlüssel der department (der Primärschlüssel der Tabelle) verwenden, um sicherzustellen, dass keine zwei Abteilungen derselben Partition zugeordnet sind (was passieren könnte, wenn zwei Abteilungen denselben Namen haben).

Schritt 3: Erstellen der endgültigen Abfrage

Großartig! Wir haben eine lokale Rangliste der Gehälter für jede Abteilung. Was nun?

Wir können nicht nach der Rangspalte filtern, da Fensterfunktionen in der WHERE-Klausel nicht zulässig sind.

Wir müssen die Ranking-Abfrage in eine CTE verpacken und das endgültige Ergebnis von dort abrufen. Wir filtern nach der Rangspalte, um nur die 3 besten Gehälter jeder Abteilung zu erhalten. Denken Sie daran, den Rang selbst nicht in das Endergebnis aufzunehmen, da wir nur die Gehälter benötigen.

Werfen Sie einen Blick auf die vollständige Lösung des Problems:

WITH ranks AS (
SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS salary_rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId
)
SELECT
  department,
  employee,
  salary,
FROM ranks 
WHERE  salary_rank <= 3;

Und schon haben wir eine vollständige Liste der Spitzenverdiener für jede Abteilung!

Gelöst: Das schwierigste SQL-Problem von LeetCode!

Sehr gut gemacht! Wir haben das schwierigste Problem des LeetCode SQL 50-Studienplans unter die Lupe genommen.

Sind Sie auf der Suche nach mehr Praxis über den SQL 50 Plan hinaus? Testen Sie unseren SQL-Praxis Track und wählen Sie aus über 1.000 interaktiven SQL-Übungen!