Die LAG-Funktion und die LEAD-Funktion in SQL

LAG() und LEAD() sind positionale Funktionen. Eine positionale Funktion ist eine Art von Fensterfunktion. Wenn Sie nicht wissen, wann und wie man sie verwendet, wie die Syntax jeder Funktion lautet, warum man sie verwenden sollte und worin die Unterschiede bestehen, lesen Sie weiter!

LAG() und LEAD() sind positionale Funktionen. Es handelt sich dabei um Fensterfunktionen, die bei der Erstellung von Berichten sehr nützlich sind, da sie sich auf Daten aus Zeilen oberhalb oder unterhalb der aktuellen Zeile beziehen können. In diesem Artikel werden wir uns diese beiden Funktionen im Detail ansehen.

Die Syntax der LAG-Funktion

Die Funktion LAG() ermöglicht den Zugriff auf einen Wert, der in einer anderen Zeile oberhalb der aktuellen Zeile gespeichert ist. Die Zeile kann sich direkt über der aktuelle Zeile oder eine bestimmte Anzahl von Zeilen entfernt befinden, sortiert nach einer bestimmten Spalte oder einer Reihe von Spalten.

Schauen wir uns ihre Syntax an:

LAG( Ausdruck [,offset[,Standardwert]]) OVER(ORDER BY columns)

LAG() nimmt drei Argumente entgegen: den Namen der Spalte oder eines Ausdrucks, aus dem der Wert ermittelt wird, die Anzahl der zu überspringenden Zeilen (Offset) und den Standardwert, der zurückgegeben wird, wenn der gespeicherte Wert aus der darüber liegenden Zeile leer ist. Nur das erste Argument ist erforderlich. Das dritte Argument (Standardwert) ist nur zulässig, wenn Sie das zweite Argument, den Versatz, angeben.

Wie bei anderen Fensterfunktionen ist auch bei LAG() die Klausel OVER erforderlich. Sie kann optionale Parameter annehmen, die wir später erklären werden. Bei LAG() müssen Sie in der Klausel OVER ein ORDER BY angeben, mit einer Spalte oder einer Liste von Spalten, nach denen die Zeilen sortiert werden sollen.

Betrachten wir die folgende Tabelle Verkauf:

Und die folgende Abfrage mit einer LAG() Funktion:

SELECT  Verkaeufername, Verkaufswert,
  LAG(Verkaufswert) OVER(ORDER BY Verkaufswert) as Frueherer_Verkaufswert
FROM Verkauf;

Hier ist das Ergebnis:

Bei dieser einfachsten Verwendung von LAG() wird der Wert aus der benachbarten Zeile darüber angezeigt. Der zweite Datensatz zeigt zum Beispiel den Verkaufsbetrag von Alice (12.000 $) und den von Stef (7.000 $) aus der Zeile darüber in den Spalten Verkaufswert bzw. Frueherer_Verkaufswert an. Beachten Sie, dass die erste Zeile keine benachbarte Zeile darüber hat, und folglich ist das Feld Frueherer_Verkaufswert leer (NULL), da die Zeile, aus der der Wert von Verkaufswert ermittelt werden sollte, nicht existiert.

Wenn Sie, wie in diesem Beispiel, nur das erforderliche Argument (den Namen der Spalte oder einen anderen Ausdruck) angeben, ist das Argument offset standardmäßig 1 und das dritte Argument standardmäßig NULL. In unserem Beispiel steht in der ersten Zeile der Ergebnismenge NULL in Frueherer_Verkaufswert und in den anderen Zeilen stehen die Werte aus den jeweiligen Zeilen unmittelbar darüber, weil der Versatz 1 ist.

Mit LAG() können Sie sowohl den Wert für die aktuelle Zeile als auch den Wert aus der darüber liegenden Zeile sehen. Auf diese Weise können Sie z.B. den Verkaufsbetrag einer bestimmten Zeile mit dem der vorherigen Zeile vergleichen, wobei der Verkaufsbetrag vom niedrigsten zum höchsten sortiert ist.

Die folgende Abbildung zeigt, wie der Wert aus der benachbarten Zeile oben an die aktuelle Zeile angehängt wird.

Die Funktion LAG() ist in unserem praktischen "SQL Fensterfunktionen Spickzettel" enthalten .

Die Syntax der LEAD-Funktion

LEAD() ist ähnlich wie die von LAG(). Während LAG() auf einen Wert zugreift, der in einer Zeile darüber gespeichert ist, greift LEAD() auf einen Wert zu, der in einer Zeile darunter gespeichert ist.

Die Syntax von LEAD() entspricht der von LAG():

LEAD( Ausdruck [,offset[,Standardwert]]) OVER(ORDER BY columns)

Genau wie LAG() benötigt die Funktion LEAD() drei Argumente: den Namen einer Spalte oder eines Ausdrucks, den Offset, der übersprungen werden soll, und den Standardwert, der zurückgegeben werden soll, wenn der gespeicherte Wert aus der Zeile darunter leer ist. Nur das erste Argument ist erforderlich. Das dritte Argument, der Standardwert, kann nur angegeben werden, wenn Sie das zweite Argument, den Offset, angeben.

Genau wie LAG() ist LEAD() eine Fensterfunktion und erfordert eine OVER Klausel. Und wie bei LAG() muss LEAD() von einem ORDER BY in der OVER Klausel begleitet werden.

Wir sehen uns die Tabelle Verkauf erneut an:

Hier ist eine Abfrage mit einer LEAD() Funktion:

SELECT  Verkaeufername, Verkaufswert,
  LEAD(Verkaufswert) OVER(ORDER BY Verkaufswert) as Naechster_Verkaufswert
FROM Verkauf;

Hier ist die Ergebnismenge:

Die Zeilen sind nach der in ORDER BY (Verkaufswert) angegebenen Spalte sortiert. Die Funktion LEAD() holt sich den Verkaufsbetrag aus der Zeile darunter. Stefs eigener Verkaufsbetrag ist zum Beispiel 7.000 $ in der Spalte Verkaufswert, und die Spalte Naechster_Verkaufswert im selben Datensatz enthält 12.000 $. Der letztere Betrag stammt aus der Spalte Verkaufswert für Alice, die Verkäuferin in der nächsten Zeile. Beachten Sie, dass es in der letzten Zeile keine nächste Zeile gibt, so dass das Feld Naechster_Verkaufswert für die letzte Zeile leer ist (NULL).

Wenn Sie nur das erforderliche Argument angeben, d.h. nur den Namen der Spalte oder einen anderen Ausdruck, ist der Versatz standardmäßig 1 und das dritte Argument standardmäßig NULL. In unserem Beispiel stammt der Wert für Alices Naechster_Verkaufswert aus der Spalte Verkaufswert der benachbarten Zeile darunter, da der Standardversatz 1 ist.

Mit LEAD() können Sie zeilenübergreifend Werte vergleichen. Die folgende Abbildung zeigt, wie der von LEAD() zurückgegebene Betrag an die aktuelle Zeile angehängt wird.

Verwendung von LAG() und LEAD() zum Vergleichen von Werten

Eine wichtige Verwendung von LAG() und LEAD() in Berichten ist der Vergleich der Werte in der aktuellen Zeile mit den Werten in derselben Spalte, aber in einer Zeile darüber oder darunter.

Betrachten wir die folgende Tabelle, Jährlicher_Verkauf, die unten gezeigt wird:

Wie Sie sehen können, enthält diese Tabelle den Gesamtverkaufsbetrag pro Jahr. Mit LAG() und LEAD() können wir die jährlichen Verkaufsbeträge über die Jahre hinweg vergleichen.

Schauen wir uns diese Abfrage an:

SELECT  Jahr, Aktueller_Gesamtverkauf,
   LAG(Gesamtverkauf) OVER(ORDER BY Jahr) AS Frueherer_Gesamtverkauf,
   Gesamtverkauf - LAG(Gesamtverkauf) OVER(ORDER BY Jahr) AS Unterschied
FROM Jaehrlicher_Verkauf;

Hier ist die Ergebnismenge:

Diese Abfrage erfasst den Verkaufsbetrag aus dem Vorjahr und setzt ihn mit der Funktion LAG() in die Spalte Frueherer_Gesamtverkauf. Die ORDER BY in der OVER Klausel ordnet die Datensätze nach Jahr und stellt sicher, dass die benachbarte Zeile darüber das Vorjahr darstellt. Dann wird der Betrag aus der Spalte Aktueller_Gesamtverkauf in der vorherigen Zeile in die aktuelle Zeile übertragen.

Diese Abfrage berechnet auch den Unterschied im Verkaufsbetrag zwischen dem aktuellen Jahr und dem Vorjahr. Dies kann uns helfen zu verstehen, ob es einen Anstieg (positive Differenz) oder einen Rückgang (negative Differenz) der Verkäufe von einem Jahr zum nächsten gab.

Für 2015 liegen uns keine Informationen über das Vorjahr vor. Daher lautet der von der Funktion LAG() zurückgegebene Wert NULL und damit auch die Differenz. Nun betrug der Gesamtumsatz im Jahr 2018 32.000 $, im Jahr 2017 (dem Vorjahr) jedoch 34.000 $, wie in der Spalte Frueherer_Gesamtverkauf angegeben. Die Differenz beträgt -$2.000, was bedeutet, dass im Jahr 2018 ein Umsatzrückgang von $2.000 im Vergleich zum Jahr 2017 zu verzeichnen war.

Verwendung von LAG() und LEAD() mit einem bestimmten Offset

Sie können die Funktionen LAG() und LEAD() mit zwei Argumenten verwenden: dem Namen der Spalte und dem Offset.

Betrachten Sie die folgende Tabelle Mitarbeiter:

Die folgende Abfrage wählt den Bonus für den Mitarbeiter mit ID=1 für jedes Quartal eines jeden Jahres aus. Sie identifiziert dann die Boni für das entsprechende Quartal im Jahr davor und im Jahr danach.

SELECT Jahr, Quartal,
  LAG(Praemie,4) OVER(ORDER BY Jahr,Quartal) AS vorherige_Praemie,
 Praemie AS Aktueller_Bonus,
  LEAD(Praemie,4) OVER(ORDER BY Jahr,Quartal) AS Naechster_Bonus 
FROM Mitarbeiter
WHERE Mitarbeiter_ID=1;

Diese Abfrage gibt die folgende Ergebnismenge zurück:

Die grün markierten Zeilen sind die Datensätze für das erste Quartal eines jeden Jahres, die weiß markierten Zeilen für das zweite Quartal eines jeden Jahres, usw. In jeder Zeile werden der vorherige und der nächste Bonusbetrag aus dem entsprechenden Quartal des Vorjahres bzw. des Folgejahres übernommen und den Spalten vorherige_Praemie bzw. Naechster_Bonus zugeordnet.

Beispiel: Der Mitarbeiter ID=1 erhielt im ersten Quartal 2018 einen Bonus von 80 USD. Für denselben Mitarbeiter betrug der Bonus für das erste Quartal 2017 $100 und der Bonus für das erste Quartal 2019 $0. ORDER BY gibt an, dass die Zeilen nach Jahr und Quartal sortiert werden sollen. Ein Offset von 4 weist LEAD() und LAG() an, jeweils 4 Zeilen vor und nach der aktuellen Zeile zu überspringen. Mit diesem Offset können Sie Werte desselben Quartals aus verschiedenen Jahren vergleichen, da ein Jahr aus 4 Quartalen besteht. Die folgende Abbildung veranschaulicht diese Idee.

Mehr über die Funktionen LAG() und LEAD() finden Sie in unseren Artikeln "Allgemeine SQL Fensterfunktionen: Positionale Funktionen " von Aldo Zelen und "Wann benutze ich SQL Fensterfunktionen?" von Tihomir Babic.

Verwendung von LAG() und LEAD() mit einem Standardwert

Im vorigen Abschnitt haben wir die Verwendung des Offset-Arguments in LAG() und LEAD() besprochen. Jetzt betrachten wir Fälle mit einem dritten Argument: dem Standardwert, der zugewiesen wird, wenn der erhaltene Wert NULL lautet. Um dieses Argument anzugeben, müssen Sie auch das zweite Argument, den Offset, angeben. Der Standardversatz ist 1, also geben Sie 1 an, um den diesen beizubehalten, oder einen anderen für Ihren Fall geeigneten Wert.

Betrachten wir ein weiteres Beispiel. Die folgende Tabelle Verkaufsprodukt enthält die Produkt_IDs, den Monat (1 = Januar, 2 = Februar, usw.) und die Anzahl der verkauften Produkte pro Monat.

Hier sind die Datensätze, für die die Produkt_ID 1 ist.

Die Abfrage:

SELECT Produkt_ID, Monat,
  LAG(Zaehlung,1,0) OVER(ORDER BY Monat) AS Vorherige_Zählung,
  Zaehlung AS Aktuelle_Anzahl,
  Zaehlung - LAG(Zaehlung,1,0) OVER(ORDER BY Monat) AS Unterschied
FROM Verkaufsprodukt
WHERE Produkt_ID=1;

gibt das Ergebnis zurück:

Für das Produkt mit der ID=1 werden der Verkaufsmonat, die Anzahl der Verkäufe in diesem Monat (Aktuelle_Anzahl) und die Anzahl der Verkäufe im Vormonat (der Wert aus der vorherigen Zeile, der von LAG() zurückgegeben wird) ausgewählt.

Wir möchten Null anstelle von NULL anzeigen, wenn LAG() versucht, Werte aus Zeilen zu erhalten, die nicht in unserem Datensatz vorhanden sind. Sowohl für LAG() als auch für LEAD() wird dies durch die Angabe eines dritten Arguments, des Standardwerts, erreicht. Denken Sie daran, dass das Offset-Argument erforderlich ist, um das Argument für den Standardwert anzugeben. Hier geben wir einen Offset von 1 an, um die Zeile darüber zu betrachten. Dann geben wir 0 als drittes Argument an. Damit wird jeder Versuch, Werte aus Zeilen zu erhalten, die nicht existieren, auf Null gesetzt, wie es hier für die erste Zeile der Fall ist (es gibt keine Zeile oberhalb der ersten Zeile).

Beachten Sie, dass der Standardwert Null nur für Zeilen zugewiesen wird, die nicht existieren. Die Zeilen, deren benachbarte Zeilen darüber existieren, aber mit NULLs in Aktuelle_Anzahl, werden als NULLs belassen, anstatt sie auf 0 zu ändern. Sie können dies in der Zeile mit Monat=4 sehen: obwohl die Aktuelle_Anzahl für die Zeile darüber (Monat=3) NULL ist, wird sie nicht durch eine 0 ersetzt, da die vorherige Zeile existiert und nur zufällig eine NULL in Aktuelle_Anzahl enthält.

Verwendung von LAG() und LEAD() mit Partitionen

Fahren wir mit demselben Beispiel fort, aber jetzt untersuchen wir einen Fall, in dem wir PARTITION BY in der OVER Klausel benötigen. Unten sehen Sie den nächsten Teil der Tabelle Verkaufsprodukt mit einem weiteren Produkt mit der ID=2.

Die Abfrage:

SELECT Produkt_ID, Monat,
  LAG(Zaehlung,1,0) OVER(PARTITION BY Produkt_ID ORDER BY Monat) AS Vorherige_Zählung,
  Zaehlung AS Aktuelle_Anzahl,
  Zaehlung - LAG(Zaehlung,1,0) OVER(PARTITION BY Produkt_ID ORDER BY Monat) AS Unterschied
FROM Verkaufsprodukt;

gibt das Ergebnis zurück:

Wir haben mehrere Produkte in dieser Tabelle. Um die Differenzen zwischen den aktuellen Verkäufen und den vorherigen Verkäufen separat für jedes Produkt zu berechnen, geben wir PARTITION BY vor ORDER BY in der Klausel OVER an.

Sie können eine Spalte oder eine Liste von Spalten in PARTITION BY benennen. Hier verwenden wir die Spalte Produkt_ID, um die Datensätze in Partitionen zu unterteilen und dann innerhalb jeder Partition nach Monat zu sortieren. Das Ergebnis ist, dass jede Partition mit Monat 1 beginnt und mit Monat 4 endet.

PARTITION BY ähnelt GROUP BY insofern, als es die gleichen Werte zusammenfasst. Im Gegensatz zu GROUP BY werden jedoch bei PARTITION BY die ursprünglichen Zeilen nicht zu einer einzigen Zeile zusammengefasst, sondern bleiben zugänglich. Mehr über den Unterschied zwischen PARTITION BY und ORDER BY erfahren Sie im Artikel "Was ist der Unterschied zwischen einem GROUP BY und einem PARTITION BY?".

LAG- und LEAD-Funktionen sind sehr nützlich!

Positionale Funktionen wie LAG() und LEAD() sind in vielen Situationen nützlich. Sie werden häufig bei der Erstellung von Berichten verwendet, da sie sich auf die Zeilen oberhalb oder unterhalb beziehen können, wie wir in diesen Beispielen gesehen haben. Ich hoffe, dass dieser Artikel Ihnen hilft, Ihr SQL-Wissen über Fensterfunktionen zu erweitern. Lesen Sie mehr über positionale Funktionen in den Artikeln "Common SQL Fensterfunktionen: Positionale Funktionen " von Aldo Zelen und "Wann benutze ich Fensterfunktionen?" von Tihomir Babic. Wenn Sie mehr über Fensterfunktionen erfahren möchten, probieren Sie unseren interaktiven Kurs "Fensterfunktionen" auf der LearnSQL.de Plattform.