Theorie und Praxis einer unterschätzten SQL-Operation
Der IT-Bereich ist für seine ständigen Veränderungen bekannt. Jeden Tag werden neue Instruments, neue Frameworks, neue Cloud-Anbieter und neue LLMs erstellt. Doch selbst in dieser geschäftigen Welt scheinen einige Prinzipien, Paradigmen und Instruments die Standing Quo von „nichts ist für die Ewigkeit“. Und im Datenbereich gibt es dafür kein so eindrucksvolles Beispiel wie die SQL-Sprache.
Seit seiner Entstehung in den 80er Jahren hat es das Zeitalter der Information Warehouses überstanden, sich in Hadoop/Information-Lake/Large Information als Hive materialisiert und ist heute noch als eine der Spark-APIs lebendig. Die Welt hat sich stark verändert, aber SQL ist nicht nur lebendig geblieben, sondern auch sehr wichtig und präsent.
Aber SQL ist wie Schach: Die Grundregeln sind leicht zu verstehen, aber schwer zu meistern! Es ist eine Sprache mit vielen Möglichkeiten, vielen Wegen, um dasselbe Drawback zu lösen, vielen Funktionen und Schlüsselwörtern und leider vielen unterschätzten Funktionalitäten, die uns, wenn wir sie besser kennen würden, beim Erstellen von Abfragen sehr helfen könnten.
Aus diesem Grund möchte ich in diesem Beitrag über eine der weniger bekannten SQL-Funktionen sprechen, die ich beim Erstellen meiner täglichen Abfragen äußerst nützlich fand: Fensterfunktionen.
Die traditionellen und bekanntesten SGBDs (PostgreSQL, MySQL und Oracle) basieren auf Konzepten der relationalen Algebra. Darin werden die Zeilen Tupel genannt und die Tabellen sind Relationen. Eine Relation ist eine Menge (im mathematischen Sinne) von Tupeln, d. h. es gibt keine Ordnung oder Verbindung zwischen ihnen. Aus diesem Grund gibt es keine Standardordnung der Zeilen in einer Tabelle, und die Berechnung, die an einer Zeile durchgeführt wird, hat keinen Einfluss auf die Ergebnisse einer anderen Zeile und wird auch nicht von diesen beeinflusst. Selbst Klauseln wie ORDER BY ordnen nur Tabellen, und es ist nicht möglich, Berechnungen in einer Zeile basierend auf den Werten anderer Zeilen durchzuführen.
Einfach ausgedrückt beheben Fensterfunktionen dieses Drawback, indem sie die SQL-Funktionalitäten erweitern und es uns ermöglichen, Berechnungen in einer Zeile basierend auf den Werten anderer Zeilen durchzuführen.
1-Aggregieren ohne Aggregation
Das trivialste Beispiel zum Verständnis von Home windows-Funktionen ist die Fähigkeit, ‚aggregieren ohne aggregieren‚.
Wenn wir eine Aggregation mit dem herkömmlichen GROUP BY-Verfahren durchführen, wird die gesamte Tabelle in eine zweite Tabelle zusammengefasst, in der jede Zeile ein Ingredient einer Gruppe darstellt. Mit Home windows Features ist es möglich, statt der Zusammenfassung der Zeilen eine neue Spalte in derselben Tabelle zu erstellen, die die Aggregationsergebnisse enthält.
Wenn Sie beispielsweise alle Ausgaben in Ihrer Ausgabentabelle addieren müssen, gehen Sie üblicherweise wie folgt vor:
SELECT SUM(worth) AS complete FROM myTable
Mit Home windows-Funktionen würden Sie etwa Folgendes erstellen:
SELECT *, SUM(worth) OVER() FROM myTable
-- Word that the window perform is outlined at column-level
-- within the question
Das folgende Bild zeigt die Ergebnisse:
Anstatt eine neue Tabelle zu erstellen, wird der Wert der Aggregation in einer neuen Spalte zurückgegeben. Beachten Sie, dass der Wert derselbe ist, die Tabelle jedoch nicht ‚zusammengefasst‚, die ursprünglichen Zeilen wurden beibehalten — wir berechneten lediglich eine Aggregation ohne Aggregation Der Tisch 😉
Die OVER-Klausel ist ein Hinweis darauf, dass wir eine Fensterfunktion erstellen. Diese Klausel definiert, über welche Zeilen die Berechnung durchgeführt wird. Im obigen Code ist sie leer, daher wird SUM() über alle Zeilen berechnet.
Dies ist nützlich, wenn wir Berechnungen basierend auf Summen (oder Durchschnitts-, Mindest- und Höchstwerten) von Spalten durchführen müssen. Beispielsweise um zu berechnen, wie viel jede Ausgabe in Prozent zum Gesamtwert beiträgt.
In realen Fällen möchten wir vielleicht auch die Particulars nach Kategorien, wie im Beispiel in Bild 2, wo wir die Unternehmensausgaben nach Abteilungen haben. Auch hier können wir die Gesamtausgaben jeder Abteilung mit einem einfachen GROUP BY ermitteln:
SELECT depto, sum(worth) FROM myTable GROUP BY depto
Oder geben Sie eine PARTITION-Logik in der Fensterfunktion an:
SELECT *, SUM(worth) OVER(PARTITION BY depto) FROM myTable
Sehen Sie das Ergebnis:
Dieses Beispiel hilft zu verstehen, warum die Operation als „Fenster“-Funktion bezeichnet wird – die OVER-Klausel definiert eine Reihe von Zeilen, über die die entsprechende Funktion ausgeführt wird, ein „Fenster“ in der Tabelle.
Im obigen Fall wird die Funktion SUM() in den von der Spalte „Depto“ (RH und SALES) erstellten Partitionen ausgeführt – sie summiert alle Werte in der Spalte „Wert“ für jedes Ingredient in der Spalte „Depto“ isoliert. Die Gruppe, zu der die Zeile gehört (RH oder SALES), bestimmt den Wert in der Spalte „Gesamt“.
2 — Zeit- und Bestellbewusstsein
Manchmal müssen wir den Wert einer Spalte in einer Zeile auf Grundlage der Werte anderer Zeilen berechnen. Ein klassisches Beispiel ist das jährliche Wachstum des BIP eines Landes, das anhand des aktuellen und des vorherigen Werts berechnet wird.
Berechnungen dieser Artwork, bei denen wir den Wert des vergangenen Jahres, die Differenz zwischen der aktuellen und der nächsten Zeile, den ersten Wert einer Reihe usw. benötigen, sind ein Beweis für die Leistungsfähigkeit der Home windows-Funktion. Tatsächlich weiß ich nicht, ob dieses Verhalten mit Commonplace-SQL-Befehlen erreicht werden könnte! Wahrscheinlich wäre es möglich, aber es wäre eine sehr komplexe Abfrage …
Aber Home windows-Funktionen machen es unkompliziert, siehe das Bild unten (Tabelle mit der Aufzeichnung der Körpergröße einiger Kinder):
SELECT
12 months, peak,
LAG(peak) OVER (ORDER BY 12 months) AS height_last_year
FROM myTable
Die Funktion LAG( ‚column‘ ) ist dafür verantwortlich, den Wert von ‚column‘ in der vorherigen Zeile zu referenzieren. Sie können sich das als eine Abfolge von Schritten vorstellen: In der zweiten Zeile berücksichtigen Sie den Wert der ersten; in der dritten den Wert der zweiten; und so weiter… Die erste Zeile zählt nicht (daher die NULL), da es keinen Vorgänger hat.
Natürlich ist ein Ordnungskriterium erforderlich, um zu definieren, was die „vorherige Zeile“ ist. Und das ist ein weiteres wichtiges Konzept in Home windows-Funktionen: Analysefunktionen.
Im Gegensatz zu herkömmlichen SQL-Funktionen berücksichtigen analytische Funktionen (wie LAG), dass eine Reihenfolge in den Zeilen vorhanden ist – und diese Reihenfolge wird durch die Klausel ORDER BY innerhalb von OVER() definiert, d. h. das Konzept der ersten, zweiten, dritten Zeile usw. wird innerhalb des Schlüsselworts OVER definiert. Das Hauptmerkmal dieser Funktionen ist die Möglichkeit, auf andere Zeilen relativ zur aktuellen Zeile zu verweisen: LAG verweist auf die vorherige Zeile, LEAD verweist auf die nächsten Zeilen, FIRST verweist auf die erste Zeile in der Partition usw.
Das Schöne an LAG und LEAD ist, dass beide ein zweites Argument akzeptieren, den Offset, der angibt, wie viele Zeilen nach vorne (für LEAD) oder hinten (für LAG) geschaut werden soll.
SELECT
LAG(peak, 2) OVER (ORDER BY 12 months) as height_two_years_ago,
LAG(peak, 3) OVER (ORDER BY 12 months) as height_three_years_ago,
LEAD(peak) OVER (ORDER BY 12 months) as height_next_year
FROM ...
Und auch Berechnungen mit diesen Funktionen sind problemlos möglich:
SELECT
100*peak/(LAG(peak) OVER (ORDER BY 12 months))
AS "annual_growth_%"
FROM ...
3 – Zeitbewusstsein und Aggregation
Zeit und Raum sind nur eins — sagte einmal Einsteinm, oder so ähnlich, ich weiß nicht ¯_(ツ)_/¯
Da wir nun wissen, wie man partitioniert und ordnet, können wir beides zusammen verwenden! Um auf das vorherige Beispiel zurückzukommen: Nehmen wir an, es gibt mehrere Kinder an diesem Tisch und wir müssen die Wachstumsrate jedes einzelnen berechnen. Das ist ganz einfach: Kombinieren Sie einfach Ordnen und Partitionieren! Ordnen wir nach Jahr und partitionieren wir nach dem Namen des Kindes.
SELECT 1-height/LAG(peak) OVER (ORDER BY 12 months PARTITION BY identify) ...
Die obige Abfrage führt Folgendes aus: Sie partitioniert die Tabelle nach untergeordneten Elementen, ordnet in jeder Partition die Werte nach Jahr und dividiert den aktuellen Höhenwert des Jahres durch den vorherigen Wert (und subtrahiert das Ergebnis von eins).
Wir nähern uns dem vollständigen Konzept des „Fensters“! Es handelt sich um einen Tabellenausschnitt, eine Reihe von Zeilen, die nach den in PARTITION BY definierten Spalten gruppiert und nach den Feldern in ORDER BY sortiert sind. Alle Berechnungen werden nur unter Berücksichtigung der Zeilen in derselben Gruppe (Partition) und einer bestimmten Reihenfolge durchgeführt.
4-Rangliste und Place
Home windows-Funktionen können in drei Kategorien unterteilt werden, von denen wir zwei bereits besprochen haben: Aggregationsfunktionen (COUNT, SUM, AVG, MAX, …) und Analysefunktionen (LAG, LEAD, FIRST_VALUE, LAST_VALUE, …).
Die dritte Gruppe ist die einfachste – Rangfolgefunktionen, deren größter Exponent die Funktion row_number() ist, die eine Ganzzahl zurückgibt, die die Place einer Zeile in der Gruppe darstellt (basierend auf der definierten Reihenfolge).
SELECT row_number() OVER(ORDER BY rating)
Rating-Funktionen geben, wie der Identify schon sagt, Werte basierend auf der Place der Zeile in der Gruppe zurück, die durch die Sortierkriterien definiert wird. ROW_NUMBER, RANK und NTILE sind einige der am häufigsten verwendeten.
Im Bild oben wird eine Zeilennummer basierend auf der Punktzahl jedes Spielers erstellt
… und ja, es begeht die schreckliche Programmiersünde, bei 1 zu beginnen.
5-Fenstergröße
Alle bisher vorgestellten Funktionen berücksichtigen bei der Berechnung der Ergebnisse ALLE Zeilen in der Partition/Gruppe. Beispielsweise berücksichtigt die im ersten Beispiel beschriebene Funktion SUM() alle Zeilen der Abteilung, um die Gesamtsumme zu berechnen.
Es ist jedoch möglich, eine kleinere Fenstergröße anzugeben, d. h. wie viele Zeilen vor und nach der aktuellen Zeile in die Berechnungen einbezogen werden sollen. Dies ist eine hilfreiche Funktion zum Berechnen von gleitenden Durchschnitten/rollierenden Fenstern.
Betrachten wir das folgende Beispiel mit einer Tabelle, die die tägliche Anzahl der Fälle einer bestimmten Krankheit enthält, wobei wir die durchschnittliche Anzahl der Fälle unter Berücksichtigung des aktuellen Tages und der beiden vorherigen berechnen müssen. Beachten Sie, dass dieses Drawback mit der zuvor gezeigten LAG-Funktion gelöst werden kann:
SELECT
( n_cases + LAG(n_cases, 1) + LAG(n_cases, 2) )/3
OVER (ORDER BY date_reference)
Aber wir können das gleiche Ergebnis eleganter erreichen, indem wir das Konzept von Rahmen:
SELECT
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
Der obige Rahmen gibt an, dass wir den Durchschnitt nur für die beiden vorherigen (VORHERGEHENDEN) Zeilen und die aktuelle Zeile berechnen müssen. Wenn wir die vorherige, die aktuelle Zeile und die folgende Zeile berücksichtigen möchten, können wir den Rahmen ändern:
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
Und das ist alles, was ein Body ist – eine Möglichkeit, die Reichweite einer Funktion auf eine bestimmte Grenze zu beschränken. Standardmäßig (in den meisten Fällen) berücksichtigen Home windows-Funktionen den folgenden Body:
ROWS BETWEEN UNBOUDED PRECEDING AND CURRENT ROW
-- ALL THE PREVIOUS ROWS + THE CURRENT ROW
