8th Jul 2024 8 Leseminuten SQL LAG()-Funktion Jill Thornhill Fensterfunktionen SQL lernen Inhaltsverzeichnis Was bewirkt die Funktion LAG()? Syntax der Funktion LAG() Grundlegendes Beispiel für die Funktion LAG() Komplexere Beispiele Verwendung von LAG() in Berechnungen Verwendung von LAG() mit einem Offset Einschließlich eines Standards Verwendung von LAG() mit PARTITION BY Verwendung von LAG()-Ergebnissen zur Sortierung eines Berichts Wo Sie mehr über die SQL-Funktion LAG() erfahren können 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: yearmonthsales_qtysales_value 20231210007380 202418005620 202429426945 2024312701745 2024415202048 2024514001890 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: yearmonthsales_valuelast_month 2023127380NULL 2024156207380 2024269455620 2024317456945 2024420481745 2024518902048 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: yearmonthsales_valuelast_monthchangepercentage 2023127380NULLNULLNULL 2024156207380-1760-23.85 2024269455620132523.58 2024317456945-5200-74.87 202442048174530317.36 2024518902048-158-7.71 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: yearmonthsales_valuelast_monthprev_month 2023127380NULLNULL 2024156207380NULL 20242694556207380 20243174569455620 20244204817456945 20245189020481745 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: yearmonthsales_valuelast_month 20231273800 2024156207380 2024269455620 2024317456945 2024420481745 2024518902048 Verwendung von LAG() mit PARTITION BY Nehmen wir an, die ursprünglichen Daten wurden erweitert, um mehr als eine Abteilung einzuschließen: departmentyearmonthsales_qtysales_value Electrical20231210007380 Electrical202418005620 Electrical202429426945 Electrical2024312701745 Electrical2024415202048 Electrical2024514001890 Hardware20231264009000 Hardware2024140006520 Hardware20242700010300 Hardware20243800012000 Hardware20244805014000 Hardware2024560009000 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: departmentyearmonthsales_valuelast_month Electrical2023127380NULL Electrical2024156207380 Electrical2024269455620 Electrical2024317456945 Electrical2024420481745 Electrical2024518902048 Hardware2023129000NULL Hardware2024165209000 Hardware20242103006520 Hardware202431200010300 Hardware202441400012000 Hardware20245900014000 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: departmentyearmonthsales_valuechange Electrical2023127380NULL Electrical202431745-5200 Electrical202415620-1760 Electrical202451890-158 Electrical202442048303 Electrical2024269451325 Hardware2023129000NULL Hardware202459000-5000 Hardware202416520-2480 Hardware20243120001700 Hardware20244140002000 Hardware20242103003780 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! Tags: Fensterfunktionen SQL lernen