Was ist fortgeschrittenes SQL?

Sind Sie verwirrt über fortgeschrittene SQL-Kenntnisse? Was sind sie? In diesem Artikel wird erklärt, was fortgeschrittenes SQL bedeuten kann, insbesondere wie wir es auf LearnSQL.de verwenden.

Ich bin mir sicher, dass Sie die Ausdrücke 'fortgeschrittene SQL-Fähigkeiten' oder 'fortgeschrittene SQL-Themen' sehr oft finden. Sie lesen einen Artikel über fortgeschrittenes SQL und freuen sich darüber, wie einfach diese fortgeschrittenen Themen zu sein scheinen. Dann sprechen Sie mit jemandem und stellen fest, dass er alles, was Sie wissen, als SQL-Grundkenntnisse betrachtet. Wie definieren Sie sich selbst? Halten Sie sich für einen einfachen, mittleren oder fortgeschrittenen SQL-Benutzer?

Fortgeschrittenes SQL Ist überall

Fortgeschrittenes SQL ist überall. Nun, zumindest das Wort "fortgeschritten" ist es. Es wird sehr häufig von SQL-Lernenden und SQL-Anwendern verwendet. Man findet es in Beschreibungen von SQL-Kursen, in Stellenanzeigen und in den Fragen im Vorstellungsgespräch. Es steht in der SQL-Literatur. Sie hören es, wenn sich Kollegen bei der Arbeit unterhalten. Es steht in zahlreichen Artikeln, in denen versucht wird zu definieren, was fortgeschrittenes SQL ist.

Eigentlich versuche ich nicht, Ihnen eine Definition von Advanced SQL zu geben. Ich versuche, Ihnen etwas anderes zu sagen: Es gibt keine einheitliche Definition von Advanced SQL! Und Sie sollten aufhören, nach ihr zu suchen. Was sollten Sie stattdessen tun?

Akzeptieren Sie die Inkonsistenz

Das ist richtig! Sie sollten akzeptieren, dass der Begriff "Advanced SQL" uneinheitlich verwendet wird. Er bedeutet je nach Kontext und je nachdem, wer den Begriff verwendet, etwas anderes.

Es ist nur logisch, dass fortgeschrittenes SQL für jemanden, der SQL-Berichte schreibt, das eine bedeutet und für jemanden, der einen Datenanalysten einstellt, etwas ganz anderes. Ein Softwareentwickler hat sicherlich eine ganz andere Definition von "fortgeschrittenem SQL".

Sie verstehen schon. Fortgeschrittenes SQL kann nicht nur eine Definition haben. Wenn Sie über fortgeschrittene SQL-Kenntnisse lesen, sollten Sie immer den Kontext, die Person, die spricht, und die Zielgruppe berücksichtigen.

Was könnte Fortgeschrittenes SQL beinhalten?

Es gibt zum Beispiel eine wirklich interessante Diskussion über fortgeschrittenes SQL auf Stack Overflow.

Die Diskussion wurde von jemandem eröffnet, der einen SQL-Job suchte und feststellte, dass es viele Jobs gibt, die "fortgeschrittene SQL-Kenntnisse" erfordern. Der Benutzer fragt, was von dieser Art von Job erwartet wird. Welche Kenntnisse werden als fortgeschritten angesehen?

In der ersten Antwort wird ein recht langer Codeschnipsel als Maßstab für fortgeschrittene Kenntnisse angegeben. Auch wenn er ziemlich lang ist, ist er nicht besonders kompliziert. Laut dieser Antwort umfasst fortgeschrittenes SQL die Auswahl von Spalten, Aggregatfunktionen wie MIN() und MAX(), die CASE WHEN-Anweisung, JOINs, die WHERE -Klausel, GROUP BY, die Deklaration von Variablen und Unterabfragen.

In der folgenden Antwort hingegen werden die meisten dieser Themen als grundlegend oder bestenfalls als fortgeschritten angesehen. Dieser Benutzer glaubt, dass fortgeschrittene SQL-Themen Funktionen, gespeicherte Prozeduren, hierarchische Abfragen, Trigger, Indizes, Datenmodellierung (Normalformen, Primär- und Fremdschlüssel, Tabellenbeschränkungen), Transaktionen und vieles mehr umfassen. Dies kommt meiner Definition von fortgeschrittenem SQL und dem, was ich in den SQL-Vorlesungen gelernt habe, sehr viel näher. Da es sich jedoch um ein Programm für Datenbankadministratoren handelt, wird dieses Wissen verständlicherweise als fortgeschritten angesehen. Einige Berichtsspezialisten und Datenanalysten werden solche Dinge vielleicht nie brauchen.

Interessant ist, dass JOINs manchmal als fortgeschritten gilt, während das Schreiben von gespeicherten Prozeduren immer noch als Grundwissen angesehen wird. Ich kann verstehen, warum, denn ein Benutzer weist auf das Problem mit JOINs hin. Auch wenn sie im Allgemeinen als Grundkenntnisse gelten, lernen viele SQL-Benutzer viel fortgeschrittenere Themen, bevor sie JOINs wirklich verstehen. Auf diese Weise werden die Grundlagen leicht zu fortgeschrittenem Wissen. Es ist nicht ungewöhnlich, dass jemand auffällige Funktionen, Trigger und dergleichen verwendet, ohne zu wissen, wie man eine einfache JOIN schreibt.

Was ist Fortgeschrittenes SQL bei LearnSQL.com?

Bevor wir erklären, was fortgeschrittenes SQL ist, ist es wichtig zu wissen, was es nicht ist. Wenn Sie sich unsere Kurse und Artikel ansehen, dann ist grundlegendes/mittleres SQL alles in SQL-92. (Hier finden Sie die Geschichte und die Details der SQL-Standards, wenn Sie mehr darüber erfahren möchten.) Dies beinhaltet:

• Alle Arten von JOINs
• Aggregat-Funktionen
• GROUP BY
• HAVING
• Unterabfragen
• Mengenoperationen (UNION, UNION ALL, INTERSECT, MINUS)

Sie müssen mit diesen Themen vertraut sein, wenn Sie behaupten, SQL zu kennen. Dies sind Dinge, die Sie verstehen sollten, bevor Sie zu fortgeschritteneren Themen übergehen.

Im Allgemeinen betrachten wir drei Themen als "fortgeschrittenes SQL":

• Fensterfunktionen
• Gemeinsame Tabellenausdrücke (CTEs)
• GROUP BY Erweiterungen (ROLLUP, CUBE, und GROUPING SETS)

Wer alle drei Themen lernen (oder üben) möchte, sollte sich unseren Fortgeschrittenes SQL Kurs ansehen. Natürlich ist dies nicht der einzige SQL-Kurs für Fortgeschrittene, den es gibt; wir haben bereits einige ausgezeichnete SQL-Kurse für Fortgeschrittene von anderen Plattformen besprochen. Schauen wir uns nun ein Beispiel zu jedem dieser Themen an.

Fensterfunktionen

Mit den SQL-Fensterfunktionen können Sie Operationen durchführen, die häufig für die Erstellung von Berichten erforderlich sind, z. B. das Ranking von Daten, die Berechnung von laufenden Summen und gleitenden Durchschnitten, das Ermitteln der Differenz zwischen Zeilen usw. Darüber hinaus können Sie Daten in Fenster unterteilen, wodurch Sie Operationen an Datenuntergruppen und nicht an den Daten als Ganzes durchführen können. Mehr darüber erfahren Sie in unserem Kurs Fensterfunktionen .

Lassen Sie uns ein Beispiel sehen. Dieser Code zeigt den Unterschied in der jährlichen Anzahl der verkauften Autos nach Marke (d. h. Automarke):

SELECT	car_make,
		cars_sold,
		year,
		cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff
FROM cars_sale;

Um diese Informationen zu erhalten, müssen Sie zunächst die Spalten auswählen, die Sie im Ergebnis haben möchten: car_make, cars_sold, year. Um die jährliche Differenz zu erhalten, subtrahieren Sie den Verkauf des Vorjahres vom Verkauf des laufenden Jahres: cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff. In diesem Fall bedeutet cars_sold die Verkäufe des laufenden Jahres. Mit der Funktion LAG() können Sie Daten aus der vorherigen Zeile abrufen. Die Klausel OVER bedeutet, dass es sich um eine Fensterfunktion handelt. Dann folgt die PARTITION BY -Klausel, mit der das Fenster (die Datenuntermenge) definiert wird, das verwendet werden soll. In diesem Fall ist es die car_make; das bedeutet, dass die Funktion die Verkaufsdifferenz nur innerhalb einer bestimmten Automarke berechnet. Wenn sie auf eine andere Automarke stößt, wird die Funktion zurückgesetzt und beginnt die Berechnung der Verkaufsdifferenz von neuem.

Schließlich ist die Operation nach Jahr aufsteigend geordnet. Warum ist das so? Die Funktion LAG() liefert uns die Daten der vorherigen Zeile. Wenn diese Operation also für jedes Jahr in aufsteigender Reihenfolge durchgeführt wird, bedeutet "Vorjahr" die vorhergehende Zeile. Das ist genau das, was wir brauchen.

Werfen Sie einen Blick auf das Ergebnis:

Haben Sie den Wert NULL in der ersten Zeile gesehen? Das liegt daran, dass 2015 das erste Jahr ist; es gibt keine vorherigen Daten, die davon abgezogen werden können. Wenn Sie die Ergebnisse verfolgen, werden Sie feststellen, dass jede Zeile die Differenz zwischen dem Jahresumsatz der aktuellen Zeile und dem Jahresumsatz der vorherigen Zeile darstellt. Wenn Sie die Zeile erreichen, in der Renault beginnt, gibt es wieder eine NULL. Das ist die Aufgabe von Fensterfunktionen, die mit Daten innerhalb eines bestimmten Fensters arbeiten. Ich habe das Fenster entsprechend der car_make definiert, so dass die Fensterfunktion zurückgesetzt wird, wenn wir einen neuen Wert in dieser Spalte erhalten. Das ist nur logisch. Warum sollte ich die Renault-Verkäufe von den Nissan-Verkäufen abziehen? Ich möchte das für jede Automarke separat machen.

Gemeinsame Tabellenausdrücke (CTEs)

Mit CTEs können Sie komplexe Abfragen schreiben, ohne Unterabfragen zu verwenden, damit Ihr Code einfach und überschaubar bleibt. Sie geben Ihnen die Möglichkeit, komplexe Berichte schnell und effizient zu erstellen. Sie ermöglichen Ihnen auch einige Berechnungen, die Sie sonst nicht durchführen könnten.

Was ist ein allgemeiner Tabellenausdruck, werden Sie sich fragen? Es ist ein temporäres Ergebnis, das Sie in der SELECT-Anweisung verwenden können. Es funktioniert wie eine temporäre Tabelle - Sie können es mit anderen Tabellen, anderen CTEs oder mit sich selbst verknüpfen.

Sie können hilfreich sein, wenn Sie z. B. einen Bericht über die für ein bestimmtes Projekt aufgewendete Zeit erstellen müssen. Auf der einen Seite gibt es eine Tabelle, die Daten über das Datum enthält, an dem jeder Mitarbeiter an diesem Projekt gearbeitet hat. Außerdem gibt es die Start- und Endzeit. Auf der anderen Seite gibt es eine Tabelle mit den Namen der Mitarbeiter. Sie müssen eine Tabelle erstellen, die den Namen jedes Mitarbeiters und seine oder ihre durchschnittliche Arbeitszeit für dieses Projekt enthält.

Hier kann Ihnen das CTE helfen:

WITH time_worked AS (
	SELECT	employee_id,
			end_time - start_time AS time
FROM project_timesheet
)
SELECT	e.first_name,
		e.last_name,
		AVG (tw.time) AS avg_time_worked
FROM employee e 
LEFT JOIN time_worked tw 
ON e.id = tw.employee_id
GROUP BY e.first_name, e.last_name;

Wie funktioniert dieser CTE? Jedes CTE beginnt mit der Klausel WITH. Dann müssen Sie Ihre CTE benennen; in diesem Fall ist es time_worked. Dann schreiben Sie eine SELECT Anweisung. In diesem Fall verwende ich die CTE, um zu berechnen, wie viel Zeit jeder Mitarbeiter bei jedem Projekt gearbeitet hat. Ich benötige die CTE, weil diese Information nicht explizit in der Tabelle angegeben ist; ich habe nur die start_time und end_time. Um die durchschnittlich geleistete Arbeitszeit zu berechnen, muss man zunächst die geleistete Arbeitszeit ermitteln. Deshalb zieht dieses CTE die start_time von der end_time ab und zeigt das Ergebnis in der Spalte time an. Die Daten stammen aus der Tabelle project_timesheet.

Jetzt, wo ich das CTE geschrieben habe, kann ich es in der nächsten Anweisung SELECT verwenden. Zuerst hole ich den Vornamen und den Nachnamen aus der Tabelle employee. Dann verwende ich die Funktion AVG() für die Spalte time aus der CTE-Anweisung time_worked. Dazu habe ich LEFT JOIN verwendet - und zwar genau so, wie ich es bei jeder anderen Tabelle tun würde. Schließlich werden die Daten nach den Vor- und Nachnamen der Mitarbeiter gruppiert.

Das Ergebnis ist eine kleine Tabelle wie diese:

Wenn CTEs Ihr Interesse geweckt haben, stellen Sie sich vor, was Sie nach Abschluss unseres Rekursive Abfragen Kurses tun können. Ach ja - ich habe noch nicht erwähnt, dass eine CTE rekursiv sein kann, d. h. sie verweist auf sich selbst. Das bedeutet, dass sie auf sich selbst verweist. Dadurch gibt sie das Teilergebnis zurück und wiederholt den Vorgang, bis sie das Endergebnis liefert. Während CTEs nicht rekursiv sein können, gibt es keine rekursiven Abfragen, die nicht CTE sind. Wenn Sie rekursive Abfragen lernen wollen, ist die Kenntnis von CTEs ein Muss.

GROUP BY-Erweiterungen

Die GROUP BY Erweiterungen von SQL bieten Ihnen zusätzliche Möglichkeiten, Daten zu gruppieren. Dies wiederum kann die Komplexität Ihrer Datenanalyse und der von Ihnen erstellten Berichte erhöhen.

Es gibt drei GROUP BY Erweiterungen:

• ROLLUP
• CUBE
• GROUPING SETS

Im Gegensatz zur regulären GROUP BY können Sie mit ROLLUP die Daten in mehrere Datensätze gruppieren und die Ergebnisse auf verschiedenen Ebenen aggregieren. Einfach ausgedrückt: Sie können ROLLUP verwenden, um Summen und Zwischensummen zu berechnen, genau wie in Excel-Pivot-Tabellen.

Die Erweiterung CUBE ist ähnlich, aber es gibt einen entscheidenden Unterschied: CUBE erzeugt Zwischensummen für jede Kombination der angegebenen Spalten.

Schließlich gibt es noch GROUPING SETs. Eine Gruppierungsgruppe ist eine Gruppe von Spalten, die Sie in der GROUP BY Klausel verwenden. Sie können verschiedene Abfragen, die GROUP BY enthalten, miteinander verbinden, wenn Sie UNION ALL verwenden. Je mehr Abfragen Sie jedoch haben, desto unübersichtlicher wird es. Sie können das gleiche Ergebnis, aber mit viel ordentlicheren Abfragen erreichen, indem Sie GROUPING SETS verwenden.

Ich möchte Ihnen zeigen, wie ROLLUP funktioniert. Angenommen, Sie arbeiten für ein Gitarrengeschäft, das mehrere Standorte hat. Manchmal müssen Sie einen Bericht erstellen, der die Gesamtzahl der auf Lager befindlichen Gitarren anzeigt. Hier ist eine Abfrage, die dies auf Hersteller-, Modell- und Geschäftsebene tut:

SELECT	manufacturer,
		model,
		store,
		SUM(quantity) AS quantity_sum
FROM guitars
GROUP BY ROLLUP (manufacturer, model, store)
ORDER BY manufacturer;

Das sieht nicht kompliziert aus. Es ist eine einfache SELECT Anweisung, die Ihnen die Spalten manufacturer, model und store aus der Tabelle guitars. Ich habe die Aggregatfunktion SUM() verwendet, um die Menge zu erhalten. Dann habe ich GROUP BY geschrieben, unmittelbar gefolgt von ROLLUP. Die Daten werden nach den Spalten in den Klammern gruppiert. Schließlich wird das Ergebnis nach dem Hersteller geordnet.

Was wird diese Abfrage zurückgeben? Sehen Sie es sich an:

Es sollte einfacher sein zu verstehen, was ich mit den verschiedenen Gruppierungsebenen meine. Ein kleiner Tipp, bevor ich fortfahre: Wo immer Sie einen NULL Wert sehen, handelt es sich um eine Zwischensumme. Werfen wir einen Blick auf die Tabelle. Zunächst gibt es 9 Fender Jazzmasters in Amsterdam. Dann gibt es 32 Fender Jazzmasters in New York. Die Gesamtmenge beträgt 41, was in der Zeile angezeigt wird:

Der Wert NULL bedeutet, dass die Daten auf Filialebene gruppiert sind. Das Ergebnis lautet: "Es gibt insgesamt 41 Fender Jazzmasters, sowohl in New York als auch in Amsterdam". Die gleiche Berechnung wird für jedes andere Fender-Modell durchgeführt, d. h. für Stratocaster und Telecaster. Dann gibt es noch diese Zeile:

Was bedeutet sie? Es bedeutet, dass es insgesamt 592 Fender aller drei Modelle in beiden Geschäften gibt.

Das gleiche Prinzip gilt für Gibson. Zunächst wird die Anzahl der Gitarren in Amsterdam und New York für das jeweilige Modell angegeben. Danach wird eine Zwischensumme der Mengen aus beiden Geschäften gebildet. Dies wird für alle drei Gibson-Modelle durchgeführt: ES-335, Les Paul und SG. Dann gibt es eine Zeile, die die Gesamtzahl aller drei Gibson-Gitarrenmodelle in beiden Geschäften anzeigt (genau wie bei Fenders):

Schließlich gibt es eine Zeile, die die Gesamtzahl der Gitarren anzeigt, unabhängig von Geschäft, Gitarrenhersteller oder Modell:

Sicherlich wollen Sie jetzt herausfinden, wie CUBE und GROUPING SETS funktionieren. Dazu empfehle ich Ihnen einen Blick in den Kurs GROUP BY-Erweiterungen.

Diese fortgeschrittenen Themen sind etwas, das Datenanalysten sehr häufig verwenden werden. Ich habe daher einige SQL-Konstruktionen für meine Kollegen aus dem Bereich der Datenanalyse vorbereitet. Wenn Sie sich für Finanzen interessieren, finden Sie hier einige fortgeschrittene SQL-Abfragen für Finanzanalysen.

Betrachten Sie sich selbst als Fortgeschrittenes SQL Benutzer?

Wie fühlen Sie sich jetzt? Habe ich Ihr Selbstvertrauen gestärkt? Wenn Sie bereits SQL-Fensterfunktionen, CTEs und die GROUP BY-Erweiterungen kennen, können Sie mit Ihren fortgeschrittenen SQL-Kenntnissen prahlen.

Oder habe ich vielleicht genau das Gegenteil bewirkt? Vielleicht habe ich Ihr Selbstvertrauen erschüttert, als Sie merkten, dass Sie nichts über die fortgeschrittenen Themen wissen, über die ich in diesem Artikel gesprochen habe.

Aber keine Sorge! Egal, zu welcher Gruppe Sie gehören, es gibt LearnSQL.de Kurse, die Ihnen helfen werden, Ihr Wissen und Ihre Fähigkeiten zu erweitern. Möchten Sie Fensterfunktionen lernen? Kein Problem - siehe unseren Fensterfunktionen Kurs. Interessieren Sie sich für CTEs? In unserem Kurs Rekursive Abfragen können Sie sie lernen und üben. Möchten Sie mehr aus GROUP BY herausholen? Unser Kurs GROUP BY-Erweiterungen in SQL hilft Ihnen dabei.