SQL LAG()-Funktion

Die LAG()-Funktion - eine der Fensterfunktionen von SQL - ist ein wichtiges Werkzeug für die Planung und Trendanalyse. In diesem Artikel zeige ich Ihnen, wie Sie die SQL LAG() in Ihre Abfragen anhand einiger Beispiele aus der Praxis.

SQL-Fensterfunktionen, auch bekannt als analytische Funktionen oder OVER-Funktionen, fügen der Datenanalyse eine neue Dimension hinzu. Sie ermöglichen es Ihnen, Aggregate oder Daten aus anderen Zeilen neben der aktuellen Zeile einzubeziehen.

Mit der Funktion LAG() können Sie "durch das Fenster" auf eine vorherige Zeile zurückblicken und deren Daten neben der aktuellen Zeile einbeziehen. Eine ähnliche Funktion, LEAD(), ermöglicht einen Blick in die Zukunft auf nachfolgende Zeilen. Mit den Funktionen LEAD() und LAG() können Sie ganz einfach nützliche Funktionen wie Jahresvergleiche in Ihre Berichte aufnehmen.

Wenn Sie sich ernsthaft mit der Datenanalyse befassen möchten, sollten Sie sich den Kurs von LearnSQL.de ansehen: Fensterfunktionen. Sie lernen durch das Lösen von mehr als 200 geführten interaktiven Übungen mit einer echten Datenbank, auf die Sie über Ihren Browser zugreifen. Der Kurs dauert etwa 20 Stunden. Wenn Sie ihn beendet haben, können Sie Ihre neuen Kenntnisse nutzen, um Ihre eigenen Datenanalyseprojekte voranzutreiben.

Was bewirkt die Funktion LAG()?

Mit dieser Funktion können Sie einen Spaltenwert aus einer früheren Zeile zusammen mit Daten aus der aktuellen Zeile einfügen. Sie ist besonders nützlich für die Analyse von Trends im Zeitverlauf, z. B. Vergleiche von Monat zu Monat oder Jahr zu Jahr.

Sie kann auch zur Beantwortung einer Reihe von Fragen verwendet werden. Wie groß ist der Unterschied zwischen den durchschnittlichen Ergebnissen an Schule A und Schule B? Wie stark wirkt sich die Verwendung eines anderen Rohstoffs auf die Lebensdauer eines Bauteils aus?

Syntax der Funktion LAG()

In ihrer einfachsten Form lautet die Syntax der SQL-Funktion LAG() ...

LAG(column_1) OVER (ORDER BY column_2)

... wobei ..:

• column_1 der Name der Spalte ist, die Sie aus der vorherigen Zeile einschließen möchten.
• OVER zeigt an, dass Sie eine Fensterfunktion verwenden; dieses Schlüsselwort ist obligatorisch.
• ORDER BY ist auch obligatorisch, wenn Sie LAG() verwenden. Die Reihenfolge der Zeilen muss vorhersehbar sein, sonst ist die Funktion sinnlos. Die von Ihnen gewählte Reihenfolge muss jedoch nicht mit der im endgültigen Bericht übereinstimmen.
• column_2 ist die Spalte, die Sie für die Reihenfolge der Zeilen verwenden. Sie können hier mehr als eine Spalte angeben.

SQL ordnet Ihre Daten zunächst anhand der Werte in Spalte_2. Bei jeder Zeile wird die vorherige Zeile in dieser Reihenfolge betrachtet und der Wert aus Spalte_1 abgerufen. Dieser Wert wird zusammen mit allen anderen Daten, die Sie in der aktuellen Zeile angefordert haben, eingefügt.

Grundlegendes Beispiel für die Funktion LAG()

Betrachten wir einen einfachen Monat-zu-Monat-Vergleich. Eine Tabelle namens monthly_sales enthält die folgenden Daten:

Wenn Sie die Umsätze des letzten Monats zusammen mit den Umsätzen dieses Monats sehen möchten, würde Ihre Abfrage wie folgt aussehen:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

Das Ergebnis sieht wie folgt aus:

Beachten Sie, dass der Umsatzwert des Vormonats in die Zeile des aktuellen Monats übernommen wurde:

Komplexere Beispiele

Nachdem Sie nun gelernt haben, wie man eine einfache Abfrage mit der SQL-Funktion LAG() durchführt, wollen wir uns nun einige der anderen Möglichkeiten ansehen, wie Sie diese Funktion in Ihrer Analyse verwenden können.

Verwendung von LAG() in Berechnungen

Sie werden oft die Ergebnisse von LAG() in Berechnungen verwenden wollen. Sie möchten zum Beispiel die Differenz und die prozentuale Veränderung zwischen den Monaten sehen. Ändern wir die vorherige Abfrage, um diese Berechnungen einzubeziehen.

Sie können das Ergebnis einer Funktion in Berechnungen verwenden, wie Sie es mit jeder anderen Spalte tun würden.

Die Abfrage sieht wie folgt aus:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  sales_value - LAG(sales_value) OVER (ORDER BY year, month) AS change,
  ((sales_value - LAG(sales_value) OVER (ORDER BY year, month)) * 100)
   / (LAG(sales_value) OVER (ORDER BY year, month)) AS percentage
FROM monthly_sales;

Die Ergebnisse sind:

Verwendung von LAG() mit einem Offset

In den Beispielen, die wir uns angeschaut haben, hat die Abfrage die Daten aus der vorherigen Zeile des Satzes übernommen. Dies ist das Standardverhalten. Sie können jedoch um mehr als eine Zeile zurückgehen, indem Sie einen Offset angeben.

Die Syntax für die Angabe eines Offsets lautet:

LAG(column_1, offset) OVER (ORDER BY column_2)

offset ist eine ganze Zahl, die angibt, wie viele Zeilen die Abfrage rückwärts suchen soll, um die Daten zu finden. Wenn Sie keinen Offset angeben, geht die Datenbank von einem Offset von 1 aus.

Nehmen wir an, Sie möchten die Umsätze dieses Monats, die Umsätze des letzten Monats und die Umsätze des Vormonats nebeneinander sehen. Die Abfrage lautet:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  LAG(sales_value,2) OVER (ORDER BY year, month) AS prev_month
FROM monthly_sales;

Die Ergebnisse sind:

Einschließlich eines Standards

In den betrachteten Ergebnissen gab die Funktion LAG() in der ersten Zeile NULL zurück, da es keine vorherige Zeile gab. Das ist aber nicht immer erwünscht.

Nehmen wir zum Beispiel an, ein Unternehmen wurde im Dezember 2023 eröffnet. Vielleicht möchten Sie dies zeigen, indem Sie für den Dezember eine Null in den Umsätzen des letzten Monats angeben, so dass es offensichtlich ist, dass die Umsätze in diesem Monat von Null auf 7380 gestiegen sind.

Die Null wird als Standardwert bezeichnet - ein Wert, der angezeigt wird, wenn keine Zahlen vorhanden sind.

Um einen Standardwert in Ihre Abfrage aufzunehmen, lautet die Syntax:

LAG(column_1, offset, default) OVER (ORDER BY column_2)

Beachten Sie, dass Sie immer einen Offset angeben müssen, wenn Sie einen Standardwert einschließen. Der Offset wäre 1, wenn Sie die Zeile unmittelbar vor der aktuellen Zeile betrachten wollen.

Die Abfrage für das obige Beispiel lautet:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value, 1, 0) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

Die Ergebnisse sind:

Verwendung von LAG() mit PARTITION BY

Nehmen wir an, die ursprünglichen Daten wurden erweitert, um mehr als eine Abteilung einzuschließen:

Sie würden wahrscheinlich wollen, dass die einzelnen Abteilungen in Ihrem Vergleich getrennt bleiben. Dies wird als Partitionierung bezeichnet. Die Syntax für die Aufteilung Ihrer Ergebnisse in Partitionen lautet ...

LAG(column_1) OVER (PARTITION BY column_2 ORDER BY column_3)

... wobei column_2 die Spalte ist, die Sie für die Partitionierung verwenden möchten.

Versuchen wir eine Abfrage, die den Bericht nach Abteilung aufteilt, aber die Zeilen innerhalb jeder Abteilung weiterhin nach Jahr und Monat ordnet:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS last_month
FROM monthly_sales;

Die Ergebnisse sehen wie folgt aus:

Beachten Sie, dass die Umsätze des letzten Monats in der ersten Zeile der neuen Abteilung auf NULL zurückgesetzt werden.

SQL verwendet department als primären Sortierschlüssel, da er in der Klausel PARTITION BY angegeben wurde. Innerhalb von department werden die Daten nach year und dann nach month sortiert, wie in der Klausel ORDER BY angegeben.

Bei jeder Zeile wird überprüft, ob es eine vorherige Zeile gibt, die zur gleichen department gehört. Ist dies der Fall, wird der Inhalt der Spalte sales_value aus der vorherigen Zeile abgerufen. Dieser wird in die aktuelle Zeile als last_month aufgenommen. Wenn es keine vorherige Zeile gab, wird last_month auf einen NULL-Wert gesetzt.

Verwendung von LAG()-Ergebnissen zur Sortierung eines Berichts

Die endgültige Reihenfolge des Berichts muss nicht mit der Reihenfolge übereinstimmen, die Sie in der OVER-Klausel verwenden. Sie können die normale ORDER BY Klausel am Ende Ihrer Abfrage verwenden, um eine andere Reihenfolge anzugeben.

Angenommen, Sie möchten die Ergebnisse in der Reihenfolge des Anstiegs des Verkaufswertes zwischen den Monaten anzeigen.

Ihre Abfrage könnte wie folgt aussehen:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS change
FROM monthly_sales
ORDER BY
  department, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month);

Die Ergebnisse sehen wie folgt aus:

Dies kann nützlich sein, wenn Sie sehen möchten, wann das Unternehmen schlecht und wann es gut lief. Solche Informationen können Ihnen helfen, die Ursache von Problemen zu finden.

Wo Sie mehr über die SQL-Funktion LAG() erfahren können

Wenn Sie mehr über die Funktion LAG() - und Fensterfunktionen im Allgemeinen - erfahren möchten, finden Sie hier einige Artikel, die zusätzliche Informationen bieten:

• Die Funktionen LAG() und LEAD() in SQL
• SQL-Funktion LEAD()
• Wie man in SQL Unterschiede zwischen den Jahren berechnet

Wenn Sie Ihre Kenntnisse über Fensterfunktionen auffrischen müssen, können Ihnen diese Ressourcen helfen:

• Dieses Fensterfunktionen Cheat Sheet ist ideal, um es beim Schreiben von Abfragen dabei zu haben.
• Wenn Sie neu auf dem Gebiet sind, ist es sehr hilfreich, sich diese Beispiele für Fensterfunktionen anzusehen.
• Wenn Sie auf der Suche nach einem Job sind, finden Sie hier einige Fensterfunktionen Interview-Fragen. Lernen Sie sie, und sie werden Ihnen helfen, das SQL-Interview zu meistern.

Wie ich bereits erwähnt habe, empfehle ich Ihnen den KursFensterfunktionen von LearnSQL.de, wenn Sie wirklich ein Experte werden wollen. Dort lernen Sie genau, wie Sie die Leistungsfähigkeit der SQL-Fensterfunktionen nutzen können. Und Sie gewinnen an Sicherheit, indem Sie viele praktische Übungen lösen.

Es geht nichts über die Praxis, um Ihre Kenntnisse und Fähigkeiten zu verbessern. Wenn Sie einige angeleitete Beispiele selbst durcharbeiten möchten, finden Sie hier einige Ressourcen:

• Der Artikel Fensterfunktionen Praxisübungen enthält 11 Übungen mit Lösungen.
• Das Fensterfunktionen Practice Set ist ein Lernpfad mit über 100 praktischen Übungen. Sie werden mit drei verschiedenen Datenbanken arbeiten, um zu lernen, wie man verschiedene Arten von Problemen löst.

Die LAG()-Funktion von SQL ist ein hervorragendes Werkzeug zur Analyse Ihrer Daten, um Trends schnell zu erkennen und Ihr Unternehmen wettbewerbsfähig zu halten. Als Datenanalytiker lohnt es sich, die Zeit zu investieren, um ein Experte für Fensterfunktionen zu werden!