Aggregierte Werte pro Gruppe ohne GROUP BY
Mit einer korrelierten Unterabfrage das Gruppenmaximum neben Detailzeilen berechnen
Aggregierte Werte pro Gruppe ohne GROUP BY ist eine kostenlose SQL Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 2 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.
Das Problem mit Detaildaten und Aggregat
Eine typische Frage im Vorstellungsgespräch lautet: „Zeigen Sie jede Zeile zusammen mit einem Aggregat ihrer Gruppe an.“ Listen Sie beispielsweise jeden Mitarbeiter zusammen mit dem maximalen Gehalt seiner Abteilung in derselben Zeile auf.
Ein einfaches GROUP BY fasst Zeilen zusammen und kann daher die Details pro Mitarbeiter nicht beibehalten. Sie benötigen die Detailzeilen und eine Zahl auf Gruppenebene gemeinsam.
Eine korrelierte Unterabfrage löst dieses Problem elegant: Sie berechnet das Gruppenaggregat für jede Detailzeile, ohne die Zeilen zusammenzufassen.
Warum ein einfaches GROUP BY hier scheitert
Wenn Sie SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id schreiben, erhalten Sie eine Zeile pro Abteilung und verlieren die Namen der einzelnen Mitarbeiter.
Wenn Sie name in SELECT aufnehmen, ohne die Spalte auch in GROUP BY aufzunehmen, erhalten Sie den klassischen Fehler „Die Spalte muss in GROUP BY erscheinen“.
Der Interviewer prüft, ob Sie verstehen, dass GROUP BY die Kardinalität verringert. Um die Detailzeilen beizubehalten, müssen Sie das Aggregat auf eine andere Weise berechnen.
Korrelierte Unterabfrage als Lösung
Platzieren Sie das Gruppenaggregat als korrelierte Unterabfrage in der SELECT-Liste. Jede Mitarbeiterzeile löst ein inneres MAX aus, das auf die Abteilung dieses Mitarbeiters begrenzt ist.
Die Korrelation e2.dept_id = e1.dept_id bindet das Aggregat an die richtige Gruppe, während die äußere Abfrage weiterhin eine Zeile pro Mitarbeiter zurückgibt.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;Jede Zeile mit ihrer Gruppe vergleichen
Sobald das Gruppenaggregat in der Abfrage vorhanden ist, können Sie jede Zeile damit vergleichen. Eine häufige Frage lautet: „Finden Sie Mitarbeiter, die mehr als der Durchschnitt ihrer Abteilung verdienen.“
Hier steht das korrelierte AVG in WHERE, sodass jeder Mitarbeiter mit dem Durchschnitt seiner eigenen Abteilung verglichen wird.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Die Abweichung von der Gruppe berechnen
Sie können auch anzeigen, wie weit jede Zeile von ihrem Gruppenaggregat entfernt ist. Wenn Sie den korrelierten Durchschnitt abziehen, erhalten Sie die Abweichung pro Zeile.
Beachten Sie, dass dieselbe korrelierte Unterabfrage in mehreren SELECT-Ausdrücken wiederverwendet werden kann. Die Engine wertet sie bei jedem Auftreten für jede Zeile aus.
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;Den Spitzenverdiener pro Gruppe finden
Um nur die Person mit dem höchsten Gehalt pro Abteilung zurückzugeben, vergleichen Sie jedes Gehalt mit dem korrelierten MAX und behalten die passenden Zeilen.
Dieses Muster gibt Gleichstände zurück: Wenn zwei Mitarbeiter das maximale Gehalt ihrer Abteilung beziehen, werden beide angezeigt. Diese Behandlung von Gleichständen ist häufig die Anschlussfrage des Interviewers.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Die Alternative mit Fensterfunktionen
Modernes SQL bietet dafür ein übersichtlicheres Werkzeug: Fensterfunktionen. MAX(salary) OVER (PARTITION BY dept_id) berechnet das Gruppenaggregat, ohne die Zeilen zusammenzufassen und ohne einen korrelierten erneuten Tabellenscan.
Interviewer schätzen es, wenn Sie beide Lösungen liefern und erklären können, dass die Variante mit der Fensterfunktion normalerweise besser abschneidet, weil sie die Tabelle nur einmal scannt.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;Kompromisse zwischen korrelierter Abfrage und Fensterfunktion
Beide Ansätze liefern dieselbe Ergebnisform, unterscheiden sich aber in folgenden Punkten:
- Korrelierte Unterabfrage: portabel und auf sehr alten Engines einsetzbar, wertet sich aber pro Zeile erneut aus.
- Fensterfunktion: ein einziger Durchlauf, bei großen Tabellen deutlich schneller, erfordert jedoch Unterstützung für SQL-Fensterfunktionen.
Sagen Sie, welche Variante Sie wählen würden und warum. Für eine einmalige Abfrage auf einer kleinen Tabelle sind beide in Ordnung; für Analysen in großem Maßstab sollten Sie die Fensterfunktion bevorzugen.
Durchgearbeitetes Beispiel: Bestellungen über dem Kundendurchschnitt
Wenden Sie das Muster auf Bestellungen an. Zeigen Sie Bestellungen an, deren Betrag über dem persönlichen durchschnittlichen Bestellwert des auftraggebenden Kunden liegt.
Das korrelierte AVG wird durch o2.customer_id = o1.customer_id auf den jeweiligen Kunden begrenzt und liefert damit für jede Bestellung die persönliche Vergleichsbasis ihres Kunden.
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);Achten Sie auf NULL- und leere Gruppen
Wenn eine Gruppe nur eine Zeile enthält, entspricht ihr Durchschnitt dieser Zeile. Daher ist salary > avg falsch und die Zeile fällt heraus. Erwähnen Sie diesen Sonderfall von sich aus.
Außerdem werden NULL-Gehälter von AVG und MAX ignoriert, wie es der SQL-Semantik für Aggregate entspricht. Wenn jeder Wert einer Gruppe NULL ist, lautet das Aggregat NULL und Vergleiche ergeben UNKNOWN, wodurch die Zeile ausgeschlossen wird. Wer diese Fälle vorwegnimmt, gibt eine besonders gründliche Antwort.
Den Rang innerhalb einer Gruppe zählen
Sie können den Rang einer Zeile innerhalb ihrer Gruppe mit einem korrelierten COUNT ausdrücken. Um den Gehaltsrang jedes Mitarbeiters innerhalb seiner Abteilung zu ermitteln, zählen Sie, wie viele Kollegen mehr verdienen.
Rang 1 bedeutet, dass die Person am meisten verdient. Wenn Sie die Anzahl der Besserverdienenden um 1 erhöhen, erhalten Sie eine Position mit der Zählung ab 1; die Korrelation begrenzt die Berechnung weiterhin auf die Abteilung.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;Kurzer Test
Wählen Sie den Grund aus, warum eine korrelierte Unterabfrage für diese Aufgabe besser geeignet ist als ein einfaches GROUP BY.
Zusammenfassung: Gruppenaggregate ohne GROUP BY
Die wichtigsten Punkte:
- Eine korrelierte Unterabfrage fügt jeder Detailzeile ein Aggregat auf Gruppenebene hinzu, ohne die Zeilen zusammenzufassen.
- Verwenden Sie sie in SELECT, um das Aggregat anzuzeigen, oder in WHERE, um jede Zeile mit ihrer Gruppe zu vergleichen.
- Das Muster
= MAX(...)gibt alle Zeilen mit dem höchsten Wert zurück, auch bei Gleichständen. - Eine Fensterfunktion mit
PARTITION BYerledigt dasselbe in einem Durchlauf und lässt sich normalerweise besser skalieren.
Bieten Sie im Vorstellungsgespräch beide Lösungen an und begründen Sie Ihre Wahl.
Häufig gestellte Fragen
Ist die Lektion „Aggregierte Werte pro Gruppe ohne GROUP BY“ kostenlos?
Ja — der vollständige Text von „Aggregierte Werte pro Gruppe ohne GROUP BY“ 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 „Aggregierte Werte pro Gruppe ohne GROUP BY“?
Mit einer korrelierten Unterabfrage das Gruppenmaximum neben Detailzeilen berechnen 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 2 von 4.
Wie lange dauert die Lektion „Aggregierte Werte pro Gruppe ohne GROUP BY“?
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
- Aufbau einer korrelierten Unterabfrage
- Aggregierte Werte pro Gruppe ohne GROUP BY
- Korrelierte EXISTS- und NOT-EXISTS-Abfragen
- Korrelierte Unterabfragen als JOINs umschreiben