14th Mar 2023 23 Leseminuten

25 Beispiele für fortgeschrittene SQL-Abfragen

Eine der besten Methoden, um fortgeschrittenes SQL zu lernen, ist das Studium von Beispielabfragen. In diesem Artikel zeigen wir Ihnen 25 Beispiele für fortgeschrittene SQL-Abfragen von mittlerer bis hoher Komplexität. Sie können diese Beispiele verwenden, um Ihre Kenntnisse in fortgeschrittenem SQL aufzufrischen oder um sie vor einem SQL-Interview zu überprüfen.

Viele der Beispiele in diesem Artikel basieren auf der folgenden employee Tabelle. Nur einige wenige Beispiele basieren auf anderen Tabellen; in diesen Fällen werden die Tabellen zusammen mit dem Beispiel erläutert.

employee_id	first_name	last_name	dept_id	manager_id	salary	expertise
100	John	White	IT	103	120000	Senior
101	Mary	Danner	Account	109	80000	junior
102	Ann	Lynn	Sales	107	140000	Semisenior
103	Peter	O'connor	IT	110	130000	Senior
106	Sue	Sanchez	Sales	107	110000	Junior
107	Marta	Doe	Sales	110	180000	Senior
109	Ann	Danner	Account	110	90000	Senior
110	Simon	Yang	CEO	null	250000	Senior
111	Juan	Graue	Sales	102	37000	Junior

Auch für Personen mit SQL-Kenntnissen kann ein guter interaktiver Online-SQL-Kurs eine echte Hilfe sein. Das umfangreichste Angebot an interaktiven SQL-Kursen finden Sie in unserer Rubrik SQL von A bis Z . Er enthält 7 interaktive SQL-Kurse mit über 850(!) Übungen, die logisch angeordnet sind und Sie vom Anfänger bis zum fortgeschrittenen SQL-Anwender führen. Die Einsteigerkurse decken die Grundlagen von SQL ab und eignen sich hervorragend zur Wiederholung und Auffrischung Ihrer SQL-Grundkenntnisse. In den SQL-Kursen für Fortgeschrittene lernen Sie Konzepte wie Fensterfunktionen, rekursive Abfragen und komplexe SQL-Berichte. Erstellen Sie ein kostenloses LearnSQL.de Konto und testen Sie unsere interaktiven Kurse, ohne Geld auszugeben. Wenn Ihnen das, was Sie lernen, gefällt, können Sie den vollen Zugang kaufen.

Also, los geht's mit unseren fortgeschrittenen SQL-Abfragen!

25 Fortgeschrittenes SQL Abfragebeispiele mit Erklärungen

Beispiel Nr. 1 - Rangfolge der Zeilen anhand eines bestimmten Ordnungskriteriums

Manchmal müssen wir eine SQL-Abfrage erstellen, um eine Rangfolge von Zeilen auf der Grundlage eines bestimmten Ordnungskriteriums anzuzeigen. In dieser Beispielabfrage zeigen wir eine Liste aller Mitarbeiter, geordnet nach Gehalt (das höchste Gehalt zuerst). Der Bericht enthält die Position jedes Mitarbeiters in der Rangliste.

Hier ist der Code:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  salary, 
  RANK() OVER (ORDER BY salary DESC) as ranking
FROM employee
ORDER BY ranking

In der obigen Abfrage verwenden wir die Funktion RANK(). Es handelt sich um eine Fensterfunktion, die die Position jeder Zeile in der Ergebnismenge zurückgibt, basierend auf der in der OVER Klausel definierten Reihenfolge (1 für das höchste Gehalt, 2 für das zweithöchste usw.). Wir müssen eine ORDER BY Ranking-Klausel am Ende der Abfrage verwenden, um die Reihenfolge anzugeben, in der die Ergebnismenge angezeigt werden soll.

Wenn Sie mehr über Ranking-Funktionen in SQL wissen möchten, empfehle ich Ihnen unseren Artikel Was ist die Funktion RANK() in SQL und wie wird sie verwendet?

Beispiel #2 - Auflisten der ersten 5 Zeilen einer Ergebnismenge

Die nächste SQL-Abfrage erstellt einen Bericht mit den Mitarbeiterdaten für die 5 höchsten Gehälter im Unternehmen. Diese Art von Bericht muss nach einem bestimmten Kriterium geordnet werden; in unserem Beispiel wird das Ordnungskriterium wieder salary DESC sein:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

Die Klausel WITH in der vorherigen Abfrage erstellt eine CTE namens employee_ranking, die eine Art virtuelle Tabelle ist, die in der Hauptabfrage verwendet wird. Die Unterabfrage in der CTE verwendet die Funktion RANK(), um die Position jeder Zeile in der Rangliste zu erhalten. Die Klausel OVER (ORDER BY salary DESC) gibt an, wie der Wert RANK() berechnet werden muss. Die Funktion RANK() für die Zeile mit dem höchsten Gehalt gibt 1 zurück, usw.

Schließlich fragen wir in der WHERE der Hauptabfrage nach den Zeilen mit einem Rankingwert kleiner oder gleich 5. Auf diese Weise erhalten wir nur die obersten 5 Zeilen nach Rangwert. Auch hier verwenden wir eine ORDER BY-Klausel, um die Ergebnismenge anzuzeigen, die nach Rang aufsteigend geordnet ist.

Beispiel Nr. 3 - Auflisten der letzten 5 Zeilen einer Ergebnismenge

Diese Abfrage ähnelt der Top-5-Abfrage, aber wir wollen die letzten 5 Zeilen. Wir müssen nur die Art der Reihenfolge ändern, d. h. ASC statt DESC verwenden. In der CTE erstellen wir eine Rangordnungsspalte auf der Grundlage einer aufsteigenden Reihenfolge des Gehalts (niedrigstes Gehalt zuerst):

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary ASC) as ranking
  FROM employee
)
SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking

In der Hauptabfrage verwenden wir WHERE ranking <= 5, um die Zeilen mit den 5 niedrigsten Gehältern zu filtern. Danach verwenden wir ORDER BY ranking, um die Zeilen des Berichts nach dem Ranglistenwert zu ordnen.

Beispiel Nr. 4 - Auflisten der zweithöchsten Zeile einer Ergebnismenge

Nehmen wir an, wir möchten die Daten des Mitarbeiters mit dem zweithöchsten Gehalt im Unternehmen erhalten. Wir können einen ähnlichen Ansatz wie bei unserer vorherigen Abfrage anwenden:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2

Die Bedingung WHERE ranking = 2 wird verwendet, um die Zeilen mit dem Gehalt an Position 2 zu filtern. Beachten Sie, dass wir mehr als einen Mitarbeiter an Position 2 haben können, wenn sie das gleiche Gehalt haben.

An dieser Stelle ist es wichtig, das Verhalten der Funktion RANK() sowie anderer verfügbarer Funktionen wie ROW_NUMBER() und DENSE_RANK() zu verstehen. Dieses Thema wird in unserem Überblick über Ranking-Funktionen in SQL ausführlich behandelt. Ich empfehle dringend, diesen Artikel zu lesen, wenn Sie mit verschiedenen Arten von Ranglisten arbeiten müssen.

Beispiel #5 - Auflistung des zweithöchsten Gehalts nach Abteilung

Fügen wir der vorherigen SQL-Abfrage eine Variation hinzu. Da jeder unserer Mitarbeiter einer Abteilung angehört, möchten wir nun einen Bericht mit der Abteilungs-ID und dem Namen des Mitarbeiters mit dem zweithöchsten Gehalt in dieser Abteilung erstellen. Wir wollen einen Datensatz für jede Abteilung im Unternehmen. Hier ist die Abfrage:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary, 
    dept_id
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  dept_id, 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ranking = 2
ORDER BY dept_id, last_name

Die wichtigste Änderung in dieser Abfrage ist die Klausel PARTITION BY dept_id in OVER. Diese Klausel gruppiert Zeilen mit demselben dept_id und ordnet die Zeilen in jeder Gruppe nach dem Gehalt DESC. Dann wird die Funktion RANK() für jede Abteilung berechnet.

In der Hauptabfrage geben wir die dept_id und die Mitarbeiterdaten für die Mitarbeiter an Position 2 ihrer Abteilungsrangliste zurück.

Lesern, die mehr über die Suche nach der N-tenhöchsten Zeile in einer Gruppe erfahren möchten, empfehle ich den Artikel Wie man das n-te höchste Gehalt nach Abteilung mit SQL findet.

Beispiel Nr. 6 - Auflisten der ersten 50 %-Zeilen in einer Ergebnismenge

In manchen Fällen sind wir daran interessiert, die ersten 50 % der Ergebnismenge (oder einen anderen Prozentsatz) zu erhalten. Für diese Art von Bericht gibt es eine SQL-Funktion namens NTILE(), die einen ganzzahligen Parameter erhält, der die Anzahl der Teilmengen angibt, in die die gesamte Ergebnismenge aufgeteilt werden soll. Zum Beispiel teilt NTILE(2) die Ergebnismenge in 2 Teilmengen mit der gleichen Anzahl von Elementen; für jede Zeile wird eine 1 oder eine 2 zurückgegeben, je nachdem, in welcher Teilmenge sich die Zeile befindet.

Hier ist die Abfrage:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(2) OVER (ORDER BY salary ) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 1
ORDER BY salary

Die obige Abfrage gibt nur die Zeilen in der ersten Hälfte eines Berichts über Angestellte zurück, die nach Gehalt in aufsteigender Reihenfolge geordnet sind. Wir verwenden die Bedingung ntile = 1, um nur die Zeilen in der ersten Hälfte des Berichts zu filtern. Wenn Sie an der Fensterfunktion NTILE() interessiert sind, lesen Sie den Artikel Common SQL Window Functions: Using Partitions With Ranking Functions.

Beispiel Nr. 7 - Auflisten der letzten 25 % Zeilen in einer Ergebnismenge

Wie bei der vorherigen Abfrage verwenden wir in diesem Beispiel NTILE(4), um die Ergebnismenge in 4 Teilmengen zu unterteilen; jede Teilmenge enthält 25 % der gesamten Ergebnismenge. Mit der Funktion NTILE() wird eine Spalte namens ntile mit den Werten 1, 2, 3 und 4 erzeugt:

WITH employee_ranking AS (
  SELECT 
    employee_id, 
    last_name, 
    first_name, 
    salary,
    NTILE(4) OVER (ORDER BY salary) as ntile
  FROM employee
)
SELECT 
  employee_id, 
  last_name, 
  first_name, 
  salary
FROM employee_ranking
WHERE ntile = 4
ORDER BY salary

Die Bedingung WHERE ntile = 4 filtert nur die Zeilen im letzten Quartal des Berichts. Die letzte Klausel ORDER BY salary ordnet die Ergebnismenge, die von der Abfrage zurückgegeben wird, während OVER (ORDER BY salary) die Zeilen ordnet, bevor sie mit NTILE(4) in 4 Teilmengen aufgeteilt werden.

Beispiel Nr. 8 - Nummerierung der Zeilen in einer Ergebnismenge

Manchmal möchte man eine Rangliste erstellen, die jeder Zeile eine Nummer zuweist, die die Position dieser Zeile in der Rangliste angibt: 1 für die erste Zeile, 2 für die zweite usw. SQL bietet einige Möglichkeiten, dies zu tun. Wenn wir eine einfache Folge von Zahlen von 1 bis N wollen , können wir die Funktion ROW_NUMBER() verwenden. Wenn wir jedoch eine Rangfolge wünschen, die zwei Zeilen an der gleichen Position zulässt (weil sie den gleichen Wert haben), können wir die Funktion RANK() oder DENSE_RANK() verwenden. Die folgende Abfrage erstellt einen Bericht, in dem jede Zeile einen Positionswert hat:

SELECT
  employee_id, 
  last_name, 
  first_name, 
  salary,
  ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position
FROM employee

Wenn Sie mehr über verschiedene fortgeschrittene Ranking-Funktionen erfahren möchten, empfehle ich Ihnen den Artikel Überblick über Ranking-Funktionen in SQL.

Beispiel Nr. 9 - Auflisten aller Zeilenkombinationen aus zwei Tabellen

In manchen Fällen benötigen wir eine Verknüpfung, die alle möglichen Kombinationen von Zeilen aus zwei Tabellen enthält. Nehmen wir an, wir haben eine Lebensmittelfirma, die 3 Arten von Müsli verkauft: Cornflakes, gezuckerte Cornflakes und Reisflakes. Alle diese Cerealien werden in 3 verschiedenen Verpackungsgrößen verkauft: 1 Pfund, 3 Pfund und 5 Pfund. Da wir 3 Produkte in 3 verschiedenen Packungsgrößen anbieten, haben wir neun verschiedene Kombinationen im Angebot.

Wir haben eine product Tabelle mit 3 Datensätzen (Cornflakes, gezuckerte Cornflakes und Reisflocken) und eine weitere Tabelle namens box_size mit 3 Datensätzen, einem für 1 Pfund und zwei Datensätzen für 3 bzw. 5 Pfund. Wenn wir einen Bericht mit der Preisliste für unsere neun Kombinationen erstellen möchten, können wir die folgende Abfrage verwenden:

SELECT
  grain.product_name,
  box_size.description, 
  grain.price_per_pound * box_size.box_weight
FROM product
CROSS JOIN	box_sizes

Das Ergebnis der Abfrage wird sein:

product	package_size	price
Corn flake	1 pound box	2.43
Corn flake	3 pound box	7.29
Corn flake	5 pound box	12.15
Sugared corn flake	1 pound box	2.85
Sugared corn flake	3 pound box	8.55
Sugared corn flake	5 pound box	14.25
Rice flake	1 pound box	1.98
Rice flake	3 pound box	5.94
Rice flake	5 pound box	9.90

Die CROSS JOIN Klausel ohne eine Bedingung erzeugt eine Tabelle mit allen Zeilenkombinationen aus beiden Tabellen. Beachten Sie, dass wir den Preis auf der Grundlage des Preises pro Pfund in der Tabelle product Tabelle und dem Gewicht aus box_sizes mit dem Ausdruck:

    grain.price_per_pound * box_size.box_weight

Einen tieferen Einblick in CROSS JOIN finden Sie in Eine illustrierte Anleitung zum SQL CROSS JOIN.

Beispiel Nr. 10 - Verknüpfung einer Tabelle mit sich selbst

In manchen Fällen müssen wir eine Tabelle mit sich selbst verknüpfen. Denken Sie an die employee Tabelle. Jede Zeile hat eine Spalte namens manager_id mit der ID des Managers, der für diesen Mitarbeiter zuständig ist. Mit Hilfe einer Selbstverknüpfung können wir einen Bericht mit den Spalten employee_name und manager_name erhalten, der uns zeigt, wer die einzelnen Mitarbeiter betreut. Hier ist die Abfrage:

SELECT 	
  e1.first_name ||’ ‘|| e1.last_name AS manager_name,
  e2.first_name ||’ ‘|| e2.last_name AS employee_name
FROM employee e1
JOIN employee e2 
ON e1.employee_id = e2.manager_id

In der obigen Abfrage sehen wir, dass die Tabelle employee zweimal als e1 und e2 referenziert wird, und die Verknüpfungsbedingung lautet e1.employee_id = e2.manager_id. Diese Bedingung verknüpft jede Mitarbeiterzeile mit der Managerzeile. Der Artikel Was ist ein Self Join in SQL? Eine Erklärung mit sieben Beispielen gibt Ihnen weitere Anregungen, wann Sie Self-Joins in Ihren SQL-Abfragen anwenden können.

Beispiel Nr. 11 - Alle Zeilen mit einem überdurchschnittlichen Wert anzeigen

Wir benötigen einen Bericht, der alle Mitarbeiter mit einem über dem Unternehmensdurchschnitt liegenden Gehalt anzeigt. Wir können zunächst eine Unterabfrage erstellen, um das Durchschnittsgehalt des Unternehmens zu ermitteln, und dann das Gehalt jedes Mitarbeiters mit dem Ergebnis der Unterabfrage vergleichen. Dies wird im folgenden Beispiel gezeigt:

SELECT 
  first_name, 
  last_name, 
  salary
FROM employee  
WHERE salary > ( SELECT AVG(salary) FROM employee )

Sie sehen die Unterabfrage, die das Durchschnittsgehalt ermittelt, in der WHERE-Klausel. In der Hauptabfrage wählen wir den Namen und das Gehalt des Mitarbeiters aus. Mehr über Unterabfragen erfahren Sie in dem Artikel Wie man SQL-Unterabfragen übt.

Beispiel Nr. 12 - Mitarbeiter mit einem höheren Gehalt als der Abteilungsdurchschnitt

Nehmen wir an, wir möchten Datensätze von Mitarbeitern erhalten, deren Gehalt höher ist als das Durchschnittsgehalt in ihrer Abteilung. Diese Abfrage unterscheidet sich von der vorherigen, da wir jetzt eine Unterabfrage benötigen, um das Durchschnittsgehalt für die Abteilung des aktuellen Mitarbeiters und nicht für das gesamte Unternehmen zu ermitteln. Dies wird als korrelierte Unterabfrage bezeichnet, da in der Unterabfrage ein Verweis auf eine Spalte in der aktuellen Zeile der Haupttabelle der Abfrage enthalten ist.

Hier ist der Code:

SELECT
  first_name, 
  last_name, 
  salary
FROM employee e1 
WHERE salary > 
    (SELECT AVG(salary) 
     FROM employee e2 
     WHERE e1.departmet_id = e2.department_id)

In der Unterabfrage sehen wir einen Verweis auf die Spalte e1.department_id, die in der Hauptabfrage referenziert wird. Die Bedingung e1.departmet_id = e2.department_id ist der Schlüssel in der Unterabfrage, da sie es uns ermöglicht, den Durchschnitt aller Mitarbeiter in der Abteilung der aktuellen Zeile zu ermitteln. Sobald wir das Durchschnittsgehalt der Abteilung erhalten haben, vergleichen wir es mit dem Gehalt des Mitarbeiters und filtern entsprechend.

Beispiel Nr. 13 - Ermitteln aller Zeilen, in denen ein Wert in einem Unterabfrageergebnis enthalten ist

Angenommen, John Smith leitet mehrere Abteilungen und wir möchten eine Liste aller Mitarbeiter in diesen Abteilungen erhalten. Wir verwenden eine Unterabfrage, um die IDs der von John Smith geleiteten Abteilungen zu erhalten. Dann verwenden wir den Operator IN, um die Mitarbeiter zu finden, die in diesen Abteilungen arbeiten:

SELECT 	
  first_name, 
  last_name
FROM employee e1 
WHERE department_id IN (
   SELECT department_id 
   FROM department
   WHERE manager_name=‘John Smith’)

Die vorherige Unterabfrage ist eine Unterabfrage mit mehreren Zeilen: Sie gibt mehr als eine Zeile zurück. Tatsächlich gibt sie mehrere Zeilen zurück, da John Smith viele Abteilungen verwaltet. Wenn Sie mit mehrzeiligen Unterabfragen arbeiten, müssen Sie spezielle Operatoren (wie IN) in der WHERE-Bedingung der Unterabfrage verwenden.

Beispiel Nr. 14 - Doppelte Zeilen in SQL finden

Wenn eine Tabelle doppelte Zeilen hat, können Sie diese mit SQL finden. Verwenden Sie eine Abfrage mit einer GROUP BY Klausel, die alle Spalten der Tabelle enthält, und einer HAVING Klausel, um Zeilen zu filtern, die mehr als einmal vorkommen. Hier ist ein Beispiel:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
FROM employee
GROUP BY 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Die Zeilen, die nicht doppelt vorhanden sind, haben eine COUNT(*) gleich 1, aber die Zeilen, die mehrfach vorhanden sind, haben eine COUNT(*), die die Anzahl der vorhandenen Zeilen angibt. Ich empfehle den Artikel Wie man doppelte Werte in SQL findet, wenn Sie mehr Details über diese Technik erfahren möchten.

Beispiel #15 - Doppelte Zeilen zählen

Wenn Sie doppelte Zeilen zählen möchten, können Sie die folgende Abfrage verwenden. Sie ähnelt der vorherigen, aber wir fügen eine COUNT(*) in die SELECT Liste ein, um zu zeigen, wie oft jede doppelte Zeile in der Tabelle vorkommt:

SELECT 	
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary, 
  COUNT(*) AS number_of_rows
FROM employee
GROUP BY
  employee_id, 
  last_name, 
  first_name, 
  dept_id, 
  manager_id, 
  salary
HAVING COUNT(*) > 1

Auch hier finden Sie wertvolle Informationen über die Verwaltung doppelter Datensätze in dem Artikel Wie man doppelte Werte in SQL findet.

Beispiel Nr. 16 - Gemeinsame Datensätze zwischen Tabellen finden

Wenn Sie zwei Tabellen mit demselben Schema haben oder wenn zwei Tabellen eine Untermenge von Spalten gemeinsam haben, können Sie die Zeilen, die in beiden Tabellen vorkommen, mit dem Mengenoperator INTERSECT ermitteln. Nehmen wir an, wir haben einen Snapshot der Tabelle employee aus dem Januar 2020 namens employee_2020_jan und wir möchten die Liste der Mitarbeiter erhalten, die in beiden Tabellen vorhanden sind. Das können wir mit dieser Abfrage tun:

SELECT 
  last_name, 
  first_name 
FROM employee
INTERSECT
SELECT 
  last_name, 
  first_name 
FROM employee_2020_jan

Als Ergebnis erhalten wir eine Liste von Mitarbeitern, die in beiden Tabellen vorkommen. Vielleicht haben sie unterschiedliche Werte in den Spalten wie salary oder dept_id. Mit anderen Worten, wir erhalten die Mitarbeiter, die im Januar 2020 für das Unternehmen gearbeitet haben und immer noch für das Unternehmen arbeiten.

Wenn Sie mehr über Mengenoperatoren erfahren möchten, empfehle ich Ihnen den Artikel Einführung in die SQL-Mengenoperatoren: Union, Union All, Minus und Intersectsss.

Beispiel Nr. 17 - Gruppierung von Daten mit ROLLUP

Die GROUP BY Klausel in SQL wird verwendet, um Zeilen in Gruppen zusammenzufassen und Funktionen auf alle Zeilen in der Gruppe anzuwenden, wobei ein einziger Ergebniswert zurückgegeben wird. Wenn wir zum Beispiel einen Bericht mit dem Gesamtbetrag der Gehälter pro Abteilung und Kompetenzstufe erhalten möchten, können wir die folgende Abfrage durchführen:

SELECT 	
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM	employee
GROUP BY dept_id, expertise

GROUP BY hat die optionale Klausel ROLLUP, mit der zusätzliche Gruppierungen in eine Abfrage aufgenommen werden können. Durch Hinzufügen der Klausel ROLLUP zu unserem Beispiel könnten wir die Gesamtsumme der Gehälter für jede Abteilung (unabhängig von der Kompetenzstufe des Mitarbeiters) und die Gesamtsumme der Gehälter für die gesamte Tabelle (unabhängig von der Abteilung und der Kompetenzstufe des Mitarbeiters) erhalten. Die geänderte Abfrage lautet:

SELECT 
  dept_id, 
  expertise, 
  SUM(salary) total_salary
FROM employee
GROUP BY ROLLUP (dept_id, expertise)

Und das Ergebnis wird sein:

dept_id	expertise	total_salary
Account	Senior	90000
Account	Junior	80000
Account	NULL	170000
CEO	Senior	250000
CEO	NULL	250000
IT	Senior	250000
IT	NULL	250000
Sales	Junior	110000
Sales	Semisenior	140000
Sales	Senior	180000
Sales	NULL	430000
NULL	NULL	1100000

Die Zeilen in der Ergebnismenge mit einem NULL sind die zusätzlichen Zeilen, die durch die Klausel ROLLUP hinzugefügt wurden. Ein NULL Wert in der Spalte expertise bedeutet eine Gruppe von Zeilen für einen bestimmten Wert von dept_id aber ohne einen bestimmten expertise Wert. Mit anderen Worten, es handelt sich um die Gesamtzahl der Gehälter für jeden dept_id. Auf die gleiche Weise bedeutet die letzte Zeile des Ergebnisses mit einem NULL für die Spalten dept_id und expertise die Gesamtsumme für alle Abteilungen des Unternehmens.

Wenn Sie mehr über die Klausel ROLLUP und andere ähnliche Klauseln wie CUBE erfahren möchten, finden Sie im Artikel Daten gruppieren, rollen und würfeln zahlreiche Beispiele.

Beispiel Nr. 18 - Bedingte Summierung

In manchen Fällen müssen wir Werte auf der Grundlage einer oder mehrerer Bedingungen zusammenfassen oder zählen. Wenn wir beispielsweise die Gesamtsumme der Gehälter in den Abteilungen Vertrieb und Personalwesen sowie in den Abteilungen IT und Support zusammen erhalten möchten, können wir die folgende Abfrage ausführen:

SELECT 
  SUM (CASE
    WHEN dept_id IN (‘SALES’,’HUMAN RESOURCES’) 
    THEN salary
    ELSE 0 END) AS total_salary_sales_and_hr,
  SUM (CASE
    WHEN dept_id IN (‘IT’,’SUPPORT’) 
    THEN salary
    ELSE 0 END) AS total_salary_it_and_support
FROM employee

Die Abfrage gibt eine einzelne Zeile mit zwei Spalten zurück. Die erste Spalte zeigt das Gesamtgehalt für die Abteilungen Vertrieb und Personalwesen. Dieser Wert wird mit Hilfe der Funktion SUM() in der Spalte salary berechnet - allerdings nur, wenn der Mitarbeiter zur Vertriebs- oder Personalabteilung gehört. Gehört der Mitarbeiter einer anderen Abteilung an, wird der Summe eine Null hinzugefügt. Die gleiche Idee wird für die Spalte total_salary_it_and_support angewendet.

Die Artikel Nützliche SQL-Muster: Conditional Summarization with CASE und Wie man CASE WHEN mit SUM() in SQL verwendet enthalten weitere Einzelheiten über diese Technik.

Beispiel Nr. 19 - Gruppieren von Zeilen nach einem Bereich

In der nächsten Beispielabfrage erstellen wir die Gehaltsbereiche low, medium und high. Dann zählen wir, wie viele Mitarbeiter sich in jedem Gehaltsbereich befinden:

SELECT 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
  END AS salary_category, 
  COUNT(*) AS number_of_employees
FROM	employee
GROUP BY 
  CASE
    WHEN salary <= 750000 THEN ‘low’
    WHEN salary > 750000 AND salary <= 100000 THEN ‘medium’
    WHEN salary > 100000 THEN ‘high’
END

In dieser Abfrage verwenden wir CASE, um den Gehaltsbereich für jeden Mitarbeiter zu definieren. Sie können dieselbe CASE-Anweisung zweimal sehen. Die erste definiert die Bereiche, wie wir gerade gesagt haben; die zweite in der GROUP BY aggregiert Datensätze und wendet die Funktion COUNT(*) auf jede Gruppe von Datensätzen an. Sie können die CASE-Anweisung auf die gleiche Weise verwenden, um Zählungen oder Summen für andere benutzerdefinierte Ebenen zu berechnen.

Wie man CASE in SQL verwendet erklärt weitere Beispiele für CASE-Anweisungen wie die in dieser Abfrage verwendete.

Beispiel Nr. 20 - Berechnen einer laufenden Summe in SQL

Eine laufende Summe ist ein sehr verbreitetes SQL-Muster, das häufig im Finanzwesen und bei Trendanalysen verwendet wird.

Wenn Sie eine Tabelle haben, in der eine beliebige tägliche Metrik gespeichert ist, wie z. B. eine Tabelle sales mit den Spalten day und daily_amount, können Sie die laufende Summe als kumulative Summe aller vorherigen daily_amount -Werte berechnen. SQL bietet eine Fensterfunktion namens SUM(), um genau das zu tun.

In der folgenden Abfrage berechnen wir die kumulierten Umsätze für jeden Tag:

SELECT 
  day,
  daily_amount,
  SUM (daily_amount) OVER (ORDER BY day) AS running_total
FROM sales

Die Funktion SUM() verwendet die Klausel OVER(), um die Reihenfolge der Zeilen festzulegen; alle Zeilen, die vor dem aktuellen Tag liegen, werden in die Klausel SUM() aufgenommen. Hier ist ein Teilergebnis:

day	daily_amount	running_total
Jan 30, 2023	1000.00	1000.00
Jan 31, 2023	800.00	1800.00
Feb 1, 2023	700.00	2500.00

Die ersten beiden Spalten Tag und daily_amount sind Werte, die direkt aus der Tabelle sales. Die Spalte running_total wird durch den Ausdruck berechnet:

SUM (daily_amount) OVER (order by day)

Sie können deutlich sehen, dass running_total die kumulierte Summe der vorherigen daily_amounts ist.

Wenn Sie dieses Thema vertiefen möchten, empfehle ich Ihnen den Artikel Was ist eine laufende Summe in SQL und wie wird sie berechnet? mit vielen erläuternden Beispielen.

Beispiel 21 - Berechnen eines gleitenden Durchschnitts in SQL

Ein gleitender Durchschnitt ist eine Zeitreihentechnik zur Analyse von Datentrends. Er wird als Durchschnitt des aktuellen Wertes und einer bestimmten Anzahl von unmittelbar vorangegangenen Werten für jeden Zeitpunkt berechnet. Die Hauptidee besteht darin, zu untersuchen, wie sich diese Durchschnittswerte im Laufe der Zeit verhalten, anstatt das Verhalten der ursprünglichen oder rohen Datenpunkte zu untersuchen.

Berechnen wir den gleitenden Durchschnitt für die letzten 7 Tage unter Verwendung der sales Tabelle aus dem vorherigen Beispiel:

SELECT 
  day,
  daily_amount,
  AVG (daily_amount) OVER (ORDER BY day ROWS 6 PRECEDING)
    AS moving_average
FROM sales

In der obigen Abfrage verwenden wir die Funktion AVG() window, um den Durchschnitt anhand der aktuellen Zeile (heute) und der vorherigen 6 Zeilen zu berechnen. Da die Zeilen nach Tagen geordnet sind, definieren die aktuelle Zeile und die 6 vorherigen Zeilen einen Zeitraum von 1 Woche.

Der Artikel Was ein gleitender Durchschnitt ist und wie man ihn in SQL berechnet, geht ausführlich auf dieses Thema ein; lesen Sie ihn, wenn Sie mehr erfahren möchten.

Beispiel #22 - Berechnen einer Differenz (Delta) zwischen zwei Spalten in verschiedenen Zeilen

Es gibt mehr als eine Möglichkeit, die Differenz zwischen zwei Zeilen in SQL zu berechnen. Eine Möglichkeit besteht darin, die Fensterfunktionen LEAD() und LAG() zu verwenden, wie in diesem Beispiel.

Nehmen wir an, wir möchten einen Bericht mit der Gesamtmenge der an jedem Tag verkauften Waren erhalten, aber wir möchten auch die Differenz (oder das Delta) zum Vortag ermitteln. Wir können eine Abfrage wie die folgende verwenden:

SELECT 
  day,
  daily_amount,
  daily_amount - LAG(daily_amount) OVER (ORDER BY day)
    AS delta_yesterday_today
FROM sales

Der Schlüsselausdruck in dieser Abfrage ist:

daily_amount - LAG(daily_amount) OVER (ORDER BY day)

Die beiden Elemente der arithmetischen Differenz stammen aus verschiedenen Zeilen. Das erste Element stammt aus der aktuellen Zeile und LAG(daily_amount) aus der Zeile des Vortags. LAG() gibt den Wert einer beliebigen Spalte aus der vorherigen Zeile zurück (basierend auf dem in der Klausel OVER angegebenen ORDER BY ).

Wenn Sie mehr über LAG() und LEAD() lesen möchten, empfehle ich Ihnen den Artikel Wie man die Differenz zwischen zwei Zeilen in SQL berechnet.

Beispiel #23 - Berechnen einer Jahresdifferenz

Vergleiche von Jahr zu Jahr (YOY) oder von Monat zu Monat sind ein beliebtes und effektives Mittel, um die Leistung verschiedener Arten von Organisationen zu bewerten. Sie können den Vergleich als Wert oder als Prozentsatz berechnen.

In diesem Beispiel verwenden wir die sales Tabelle, die Daten in täglicher Granularität enthält. Zunächst müssen wir die Daten auf das Jahr oder den Monat aggregieren. Dazu erstellen wir eine CTE mit nach Jahr aggregierten Beträgen. Hier ist die Abfrage:

WITH year_metrics AS (
  SELECT 
    extract(year from day) as year,
    SUM(daily_amount) as year_amount
  FROM sales 
  GROUP BY year)
SELECT 
  year, 
  year_amount,
  LAG(year_amount) OVER (ORDER BY year) AS revenue_previous_year,
  year_amount - LAG(year_amount) OVER (ORDER BY year) as yoy_diff_value,
  ((year_amount - LAG(year_amount) OVER (ORDER BY year) ) /
     LAG(year_amount) OVER (ORDER BY year)) as yoy_diff_perc
FROM year_metrics
ORDER BY 1

Der erste zu analysierende Ausdruck ist derjenige, der zur Berechnung von yoy_diff_value verwendet wird:

year_amount - LAG(year_amount ) OVER (ORDER BY year)

Er wird verwendet, um die Differenz (als Wert) zwischen dem Betrag des aktuellen Jahres und dem des Vorjahres zu berechnen, indem die Funktion LAG() window verwendet wird und die Daten nach Jahr geordnet werden.

Im nächsten Ausdruck wird die gleiche Differenz als Prozentsatz berechnet. Diese Berechnung ist etwas komplexer, da wir durch den Betrag des Vorjahres dividieren müssen. (Hinweis: Wir verwenden das Vorjahr als Basis für die Berechnung des Prozentsatzes, also ist das Vorjahr 100 Prozent).

((year_amount-LAG(year_amount ) OVER(ORDER BY year))/LAG(year_amount ) OVER(ORDER BY year))

Im Artikel Wie man in SQL die Unterschiede zwischen den Jahren berechnet finden Sie mehrere Beispiele für die Berechnung von Jahr-zu-Jahr- und Monat-zu-Monat-Differenzen.

Beispiel #24 - Verwenden Sie Rekursive Abfragen, um Datenhierarchien zu verwalten

Einige Tabellen in SQL können eine implizite Art von Datenhierarchie aufweisen. Ein Beispiel: Unsere employee Tabelle hat eine manager_id für jeden Mitarbeiter. Wir haben einen Manager, der anderen Managern unterstellt ist, die wiederum anderen Mitarbeitern unterstellt sind, und so weiter.

Bei dieser Art von Organisation können wir eine Hierarchie mit verschiedenen Ebenen haben. In jeder Zeile bezieht sich die Spalte manager_id auf die Zeile der nächsthöheren Ebene in der Hierarchie. In diesen Fällen besteht eine häufige Anfrage darin, eine Liste aller Mitarbeiter zu erhalten, die (direkt oder indirekt) dem CEO des Unternehmens unterstellt sind (der in diesem Fall die employee_id von 110 hat). Die zu verwendende Abfrage lautet:

WITH RECURSIVE subordinate AS (
 SELECT  
   employee_id,
   first_name,
   last_name,
   manager_id
  FROM employee
  WHERE employee_id = 110 -- id of the top hierarchy employee (CEO)
 
  UNION ALL
 
  SELECT  
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id
  FROM employee e 
  JOIN subordinate s 
  ON e.manager_id = s.employee_id
)
SELECT 	
  employee_id,
  first_name,
  last_name,
  manager_id
FROM subordinate ;

In dieser Abfrage haben wir eine rekursive CTE namens subordinate erstellt. Sie ist der Schlüsselteil dieser Abfrage, da sie die Datenhierarchie von einer Zeile bis zu den Zeilen in der Hierarchie unmittelbar darunter durchläuft.

Es gibt zwei Unterabfragen, die durch UNION ALL verbunden sind. Die erste Unterabfrage gibt die oberste Zeile der Hierarchie zurück und die zweite Abfrage gibt die nächste Ebene zurück, wobei diese Zeilen dem Zwischenergebnis der Abfrage hinzugefügt werden. Dann wird die zweite Unterabfrage erneut ausgeführt, um die nächste Ebene zurückzugeben, die wiederum der Zwischenergebnismenge hinzugefügt wird. Dieser Vorgang wird so lange wiederholt, bis dem Zwischenergebnis keine neuen Zeilen mehr hinzugefügt werden.

Schließlich verbraucht die Hauptabfrage die Daten in der subordinate CTE und gibt die Daten so zurück, wie wir es erwarten. Wenn Sie mehr über rekursive Abfragen in SQL erfahren möchten, empfehle ich Ihnen den Artikel Wie man alle Mitarbeiter unter jedem Manager in SQL findet.

Beispiel Nr. 25 - Die Länge einer Reihe ermitteln mit Fensterfunktionen

Angenommen, wir haben eine Tabelle mit Benutzerregistrierungsdaten. Wir speichern Informationen darüber, wie viele Benutzer sich an jedem Datum registriert haben. Wir definieren eine Datenreihe als die Folge von aufeinander folgenden Tagen, an denen sich Benutzer registriert haben. Ein Tag, an dem sich kein Benutzer registriert, unterbricht die Datenreihe. Für jede Datenreihe wollen wir ihre Länge ermitteln.

Die folgende Tabelle zeigt die Datenreihen:

id	day	Registered users
1	Jan 25 2023	51
2	Jan 26 2023	46
3	Jan 27 2023	41
4	Jan 30 2023	59
5	Jan 31 2023	73
6	Feb 1 2023	34
7	Feb 2 2023	56
8	Feb 4 2023	34

Es gibt 3 verschiedene Datenreihen, die in verschiedenen Farben dargestellt sind. Wir suchen nach einer Abfrage, um die Länge der einzelnen Datenreihen zu ermitteln. Die erste Datenreihe beginnt am 25. Januar und hat eine Länge von 3 Elementen, die zweite beginnt am 30. Januar und hat eine Länge von 4 Elementen, und so weiter.

Die Abfrage lautet wie folgt:

WITH data_series AS (
  SELECT  	
    RANK() OVER (ORDER BY day) AS row_number,
    day, 
    day - RANK() OVER (ORDER BY day) AS series_id
 FROM	user_registration )
SELECT	
  MIN(day) AS series_start_day,
  MAX(day) AS series_end_day,
  MAX(day) - MIN (day) + 1 AS series_length
FROM	data_series
GROUP BY series_id
ORDER BY series_start_date

In der vorherigen Abfrage hat die CTE die Spalte series_id, die als ID für die Zeilen in derselben Datenreihe verwendet werden soll. In der Hauptabfrage wird die Klausel GROUP BY series_id verwendet, um Zeilen derselben Datenreihe zu aggregieren. Dann können wir den Anfang der Reihe mit MIN(day) und ihr Ende mit MAX(day) erhalten. Die Länge der Reihe wird mit dem Ausdruck berechnet:

      MAX(day) - MIN (day) + 1

Wenn Sie dieses Thema vertiefen möchten, finden Sie im Artikel Berechnung der Länge einer Zeitreihe mit SQL eine ausführliche Erklärung dieser Technik.

Üben Sie Fortgeschrittenes SQL mit LearnSQL.com-Kursen

SQL ist eine einfach zu erlernende und leistungsstarke Sprache. In diesem Artikel haben wir 25 Beispiele für fortgeschrittene SQL-Abfragen gezeigt. Sie können alle in etwa 5 Minuten erklärt werden, was zeigt, dass SQL eine leicht zugängliche Sprache ist, selbst wenn Sie komplexe Berichte oder Abfragen erstellen müssen.

Wenn Sie SQL weiter erlernen möchten, empfehle ich Ihnen unsere SQL-Kurse für Fortgeschrittene: Fensterfunktionen, Rekursive Abfragen und GROUP BY Erweiterungen in SQL. Sie alle behandeln komplexe Bereiche der SQL-Sprache in einfachen Worten und mit vielen Beispielen. Steigern Sie Ihre Fähigkeiten und investieren Sie in sich selbst mit SQL!

Tags: