0Pricing
SQL Interview Prep · Lektion

Der Spitzenverdiener je Abteilung

Partitionierung und Rangfolge für gruppierte Top-N-Gehaltsprobleme kombinieren

Der Spitzenverdiener je Abteilung ist eine kostenlose SQL Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Von globaler zu gruppenweiser Rangfolge

Die nächste Steigerung lautet: „Finden Sie den bestbezahlten Mitarbeiter in jeder Abteilung.“ Das verbindet Rangfolgen mit Gruppierung und ist eine typische Frage für das mittlere Erfahrungsniveau.

Nehmen Sie eine Tabelle employee mit id, name, department_id und salary an. Wir möchten pro Abteilung den bestbezahlten Mitarbeiter zurückgeben – bei Gleichstand mehrere –, nicht nur das globale Maximum.

Das wichtigste neue Werkzeug ist PARTITION BY. Damit wird die Rangfolge innerhalb jeder Abteilung neu begonnen.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY setzt die Rangfolge zurück

Wenn Sie PARTITION BY department_id zur Fensterdefinition hinzufügen, berechnet die Datenbank die Rangfolge unabhängig innerhalb jeder Abteilung.

Jede Abteilung beginnt mit einem eigenen Rang 1. Der bestbezahlte Mitarbeiter in Abteilung 1 und der bestbezahlte Mitarbeiter in Abteilung 5 erhalten also beide Rang 1. Ohne Partitionierung würde nur das eine globale Maximum Rang 1 erhalten.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Auf Rang 1 filtern

Um nur die bestbezahlten Mitarbeiter zu behalten, umschließen Sie die Abfrage mit der Rangfolge und filtern nach Rang 1. Wie immer muss die Fensterfunktion zuerst in einer Unterabfrage oder einem CTE berechnet werden, bevor Sie darauf filtern können.

Wenn Sie hier DENSE_RANK oder RANK verwenden, werden bei einem Gleichstand um das höchste Gehalt in einer Abteilung beide Mitarbeiter zurückgegeben. Das ist normalerweise die richtige Interpretation von „der bestbezahlte Mitarbeiter“.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER, wenn Sie genau eine Zeile benötigen

Manchmal möchte der Interviewer genau eine Zeile pro Abteilung, selbst wenn ein Gleichstand besteht. Verwenden Sie dann ROW_NUMBER und fügen Sie einen deterministischen Tie-Breaker hinzu, zum Beispiel die kleinste id.

Ohne den Tie-Breaker werden Gleichstände beliebig aufgelöst, und Ihr Ergebnis ist nicht deterministisch. Durch das Hinzufügen von , id ASC wird die Auswahl wiederholbar.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK vs. ROW_NUMBER vs. RANK in diesem Fall

Wählen Sie abhängig vom genauen Wortlaut:

  • DENSE_RANK = 1: alle Mitarbeiter, die pro Abteilung beim höchsten Gehalt gleichauf liegen.
  • RANK = 1: für den ersten Rang identisch mit DENSE_RANK; Lücken sind erst unterhalb von Rang 1 relevant.
  • ROW_NUMBER = 1: genau ein Mitarbeiter pro Abteilung, wobei Gleichstände durch Ihr ORDER BY aufgelöst werden.

Zu sagen, welche Variante Sie gewählt haben und warum, ist der Teil, den Interviewer bewerten.

Der korrelierte Ansatz vor Fensterfunktionen

Bevor es Fensterfunktionen gab, war die Standardlösung eine korrelierte Unterabfrage: Behalten Sie eine Zeile nur dann, wenn niemand in derselben Abteilung mehr verdient.

Damit werden automatisch alle gleichauf liegenden Topverdiener zurückgegeben. Die Lösung ist portabel, kann aber langsam sein, weil das innere MAX für jede äußere Zeile ausgewertet wird, sofern der Optimizer die Abfrage nicht umschreibt.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

Der GROUP-BY-Join-Ansatz

Ein weiteres portables Muster: Berechnen Sie mit GROUP BY das maximale Gehalt pro Abteilung und führen Sie anschließend einen Join zurück aus, um die passenden Mitarbeiter zu erhalten.

Das ist effizient und übersichtlich. Der Join gibt jeden Mitarbeiter zurück, dessen Gehalt dem Maximum seiner Abteilung entspricht; Gleichstände bleiben daher erhalten.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

Top N pro Abteilung

Das Muster lässt sich ohne neue Konzepte auf „Top 3 Verdiener pro Abteilung“ erweitern. Ändern Sie einfach den Filter in einen Bereich.

Mit DENSE_RANK liefert rnk <= 3 die drei höchsten unterschiedlichen Gehaltsstufen zurück, bei Gleichständen also möglicherweise mehr als drei Zeilen. Mit ROW_NUMBER liefert rn <= 3 genau drei Zeilen pro Abteilung.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Beispiel

Abteilung 1: Ana 120, Bob 120, Cara 90. Abteilung 2: Dan 200, Eve 150.

  • DENSE_RANK = 1: Ana (120) und Bob (120) aus Abteilung 1 sowie Dan (200) aus Abteilung 2. Drei Zeilen.
  • ROW_NUMBER = 1 mit id als Tie-Breaker: einer von Ana und Bob, je nachdem, wer die niedrigere id hat, sowie Dan. Zwei Zeilen.

Dieselben Daten, aber je nach Funktion eine unterschiedliche Zeilenanzahl. Wählen Sie die Variante passend zur Frage.

Abteilungen einbeziehen und Namen verknüpfen

Interviewer fügen häufig eine department-Tabelle hinzu und fragen nach dem Abteilungsnamen. Führen Sie einfach nach dem Ranking einen Join auf diese Tabelle aus.

Belassen Sie das Ranking auf der employee-Tabelle und verknüpfen Sie die Lookup-Tabelle erst am Ende, damit die Partitionierung weiterhin auf der richtigen Granularität erfolgt.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Zu vermeidende Fallstricke

Typische Fehler beim Ranking pro Gruppe:

  • PARTITION BY vergessen und global ranken, sodass nur der bestverdienende Mitarbeiter des gesamten Unternehmens zurückgegeben wird.
  • ROW_NUMBER verwenden, obwohl die Frage impliziert, dass alle Gleichstände angezeigt werden sollen, und dadurch unbemerkt gleichauf liegende Topverdiener ausschließen.
  • Versuchen, die Fensterfunktion direkt in WHERE zu verwenden, statt sie einzubetten.
  • Die department-Tabelle vor dem Ranking verknüpfen und dadurch versehentlich die Granularität der Partition zu ändern.

Schnelltest

Wählen Sie die passende Ranking-Funktion für die Anforderung.

Zusammenfassung

Der Topverdiener pro Abteilung folgt demselben Muster wie das globale Ranking, ergänzt um PARTITION BY department_id:

  • DENSE_RANK = 1 gibt alle gleichauf liegenden Topverdiener pro Abteilung zurück.
  • ROW_NUMBER = 1 mit einem Tie-Breaker gibt genau einen Mitarbeiter pro Abteilung zurück.
  • Portable Alternativen sind eine korrelierte Abfrage mit MAX pro Abteilung oder das Zurückverknüpfen des mit GROUP BY berechneten Maximums mit der Tabelle.

Erweitern Sie das Muster auf Top-N, indem Sie = 1 in <= N ändern. Sagen Sie ausdrücklich, wie Sie mit Gleichständen umgehen.

Häufig gestellte Fragen

Ist die Lektion „Der Spitzenverdiener je Abteilung“ kostenlos?

Ja — der vollständige Text von „Der Spitzenverdiener je Abteilung“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Interview Prep-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Der Spitzenverdiener je Abteilung“?

Partitionierung und Rangfolge für gruppierte Top-N-Gehaltsprobleme kombinieren Du übst SQL Interview Prep mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Interview Prep zu starten?

Keine Vorkenntnisse erforderlich. SQL Interview Prep auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „Der Spitzenverdiener je Abteilung“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Interview Prep-Lektion Code schreiben und ausführen?

Ja. Jede SQL Interview Prep-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Das zweithöchste Gehalt auf fünf Arten
  2. Der n-höchste Wert mit DENSE_RANK
  3. Der Spitzenverdiener je Abteilung
  4. NULL zurückgeben, wenn kein n-ter Wert existiert
← Zurück zu SQL Interview Prep