Wie man die Differenz zwischen zwei Zeilen in SQL berechnet

Die Berechnung der Differenz zwischen zwei Zeilen in SQL kann eine schwierige Aufgabe sein. Es ist möglich - und es gibt mehr als einen Weg, es zu tun. In diesem Artikel erkläre ich, wie man die SQL-Fensterfunktionen LEAD() und LAG() verwendet, um die Differenz zwischen zwei Zeilen in derselben Tabelle zu ermitteln.

Ich betrachte diesen Artikel gerne als "Happy Hour" - Sie können zwei Themen (Berechnung der Differenz zwischen zwei Zeilen und SQL-Fensterfunktionen) in nur einem Artikel kennenlernen. Fangen wir gleich damit an!

Wenn Sie Fensterfunktionen lernen wollen, schauen Sie sich unseren interaktiven Kurs Fensterfuktionen an. Wenn Sie sie bereits kennen und das Schreiben von Abfragen mit Fensterfunktionen üben möchten, empfehle ich Ihnen unsere Fensterfuktionen Übungseinheit mit 100 praktischen Übungen.

Finden Sie die Differenz zwischen zwei Werten in derselben Zeile

Um eine Differenz zu berechnen, benötigen Sie zwei Element. Um eine Differenz in SQL zu berechnen, benötigen Sie zwei Datensätze. Sie können die Differenz zwischen zwei Spalten im selben Datensatz berechnen, wie ich gleich zeigen werde. Das ist sehr einfach. Ich werde mich jedoch hauptsächlich darauf konzentrieren, die Differenz zwischen zwei Werten der gleichen Spalte in verschiedenen Datensätzen zu ermitteln.

Lassen Sie uns zunächst über unsere Daten sprechen. Wir werden eine Datenbank mit zwei Tabellen verwenden, wie sie von der Regierung einer imaginären Provinz verwendet werden, um einige Sozialprogramme zu definieren. Die erste Tabelle ist housing, die Daten über die Anzahl der Personen enthält, die ein Haus mieten, besitzen oder benötigen. Werfen Sie einen Blick darauf:

Tabelle housing

Um einen Plan für den Bau von Häusern für Menschen, die sie benötigen, zu entwerfen, möchte die Regierung einige Daten über die Wohnungsprobleme in verschiedenen Städten erhalten. Angenommen, die Regierung möchte wissen, wie viele Menschen in jeder Stadt kein Haus besitzen. Die Frage lautet dann:

SELECT 	
    city,
    total_population, 
    total_population - population_owning_house AS people_not_owning_house
FROM housing

In dieser Abfrage ist es klar, dass Sie eine Differenz anhand von zwei verschiedenen Spalten im selben Datensatz berechnen. Als Nächstes berechnen Sie die Unterschiede anhand von zwei Datensätzen.

Berechnen der Differenz zwischen zwei Werten in derselben Spalte

Normalerweise beziehen sich alle Operationen, die wir in einer SQL-Abfrage durchführen, auf den aktuellen Datensatz. Aber in diesem Fall benötigen wir einen zweiten Datensatz. Das ist der knifflige Teil. Wir werden eine Technik verwenden, die die Fensterfunktionen LAG() und LEAD() nutzt, um Daten aus einem anderen Datensatz zu erhalten. Wenn Sie tiefer einsteigen wollen, empfehle ich Ihnen, diesen Artikel über Fensterfunktionen zu lesen, der ausführliche Erklärungen zu Fensterfunktionen mit vielen Beispielen enthält.

Nehmen wir an, Sie möchten für eine bestimmte Stadt ("Bronzepolis") die Anzahl der Personen ermitteln, die im vergangenen Jahr ein Haus benötigten. Schauen wir uns die Abfrage an:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house) AS previous_year,
  population_needing_house - LAG(population_needing_house)
    OVER (ORDER BY year ) AS difference_previous_year
FROM housing
WHERE city = ‘Bronzepolis’
ORDER BY year

Im blauen Text sehen Sie die Berechnung des SQL-Deltas zwischen zwei Zeilen. Um eine Differenz zu berechnen, benötigen Sie ein Paar von Datensätzen. Diese beiden Datensätze sind "der aktuelle Datensatz" und "der Datensatz des Vorjahres". Diesen Datensatz erhalten Sie mit der Fensterfunktion LAG(). Mit dieser Funktion können Sie Daten aus dem vorherigen Datensatz abrufen (auf der Grundlage eines Ordnungskriteriums, das hier "ORDER BY year" lautet).

Unten sehen Sie das Ergebnis dieser Abfrage. Die roten Pfeile zeigen, dass der Wert der Funktion LAG() denselben Wert für "population_needing_house" liefert wie der Datensatz des Vorjahres. Die Spalte rechts zeigt das Ergebnis der Differenz zwischen dem aktuellen Jahr und den Vorjahren.

Bei der nächsten Abfrage werden wir die Analyse der Wohnungsprobleme auf alle Städte ausweiten. Für jede Stadt wollen wir in der Spalte population_needing_house die Differenz zwischen dem aktuellen Jahr und dem Vorjahr ermitteln.

Zunächst wird die Bedingung city = ‘Bronzepolis’ entfernt. Da Sie die Werte für jede Stadt berechnen möchten, benötigen Sie eine Möglichkeit, die Daten in Gruppen aufzuteilen. Deshalb fügen Sie die Klausel PARTITION BY city zu LAG() hinzu. Mit PARTITION BY city können Sie alle Datensätze für dieselbe Stadt im selben Fenster verarbeiten

Sie werden LAG() erneut verwenden, um die Differenz zwischen der Anzahl der Personen, die in diesem Jahr eine Wohnung benötigen, und der des Vorjahres zu berechnen. Schauen wir uns die Abfrage an:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS previous_year,
  population_needing_house - LAG(population_needing_house)
   OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year
FROM housing
ORDER BY city, year

In blauer Schrift ist angegeben, wie die Differenz berechnet wird. Hier ist eine einfache englische Version des Vorgangs:

difference_previous_year = 
  population_needing_house in current record 
  - population_needing_house in the previous year’s record

Wo:

population_needing_house im aktuellen

population_needing_house

und

Population_needing_house im Vorjahresdatensatz erhält man durch die Funktion LAG(population_needing_house) OVER (PARTITION BY city ORDER BY year )

Sie haben also die Klausel PARTITION verwendet, um eine Menge mit allen Datensätzen für dieselbe Stadt zu erstellen. Dann wird die Klausel ORDER BY verwendet, um alle diese Datensätze nach Jahr zu ordnen. Schließlich wird die Funktion LAG() verwendet, um den Wert population_needing_house aus dem vorherigen Datensatz zu erhalten

Eine weitere SQL-Fensterfunktion, LEAD(), ähnelt LAG(). Sie gibt jedoch den nächsten

Wenn Sie sich die beiden vorherigen Abfragen ansehen, werden Sie sehen, dass wir das gleiche Muster zur Berechnung der Differenz verwenden: Subtraktion des vorherigen Spaltenwerts vom aktuellen Wert mit LAG() (oder vom nächsten Datensatz mit LEAD())

Die SQL-Fensterfunktionen sind sehr leistungsfähig. Beispiele aus verschiedenen Geschäftsbereichen, wie Finanzen, Vertrieb und Handel finden Sie in diesem Artikel über die Verwendung von SQL-Fensterfunktionen

Berechnen der Differenz zwischen Datumswerten in SQL

In den vorherigen Beispielen haben Sie das Delta zwischen zwei Zeilen anhand von numerischen Spaltenwerten berechnet. Jetzt zeige ich Ihnen, wie Sie die Differenz zwischen zwei Werten des Datums-Datentyps berechnen können.

Lassen Sie uns zunächst den Datentyp des Ergebnisses betrachten. Wenn Sie die Differenz zwischen zwei Datumswerten berechnen, ist das Ergebnis kein Datum. Es handelt sich um ein Intervall, das die Anzahl der Tage zwischen den beiden Daten darstellt.

Angenommen, es gibt eine Tabelle mit dem Namen hospital_statistics, in der Statistiken für die drei Krankenhäuser der Provinz gespeichert sind. Nachfolgend sehen Sie ein Beispiel für die Tabellendaten:

Tabelle hospital_statistics

Nehmen wir nun an, es gibt eine seltene Krankheit namens MDLR. Die Regierung möchte untersuchen, wie oft ein Patient mit MDLR in eines der Krankenhäuser eingeliefert wird. Sie haben um einen Bericht mit den Spalten day, hospital name, number of cases und days_since_the_last_case gebeten. Wir werden SQL verwenden, um den Bericht zu erstellen:

SELECT
  day,
  hospital_name,
  number_of_patients,
  day - LAG(day) OVER (ORDER BY day) 
     AS days_since_last_case
FROM hospital_statistics
WHERE illness_name = 'MDLR'
ORDER BY day

Sie sehen das gleiche Muster, das wir zuvor verwendet haben. Jetzt wird es verwendet, um die Tage seit dem letzten Fall zu berechnen. Der einzige Unterschied besteht darin, dass Sie eine Differenz zwischen zwei Datumsangaben anstelle von numerischen Werten berechnen.

Wie Sie in den Ergebnissen sehen, ist die Spalte days_since_last_case ein ganzzahliger Wert, der eine Anzahl von Tagen darstellt.

Mit SQL können Sie auch Differenzen zwischen timestamp Werten berechnen. Und es gibt noch weitere interessante Rechenoperationen, die Sie auf datumsbezogene Datentypen anwenden können. Wenn Sie tiefer einsteigen wollen, empfehle ich Ihnen den Artikel How to Analyze Time Series COVID 19 Data with SQL Fensterfuktionen.

Finden der Differenz zwischen nicht aufeinanderfolgenden Datensätzen

Bis jetzt haben Sie die Unterschiede zwischen zusammenhängenden Datensätzen anhand bestimmter Ordnungskriterien berechnet. In manchen Fällen müssen Sie auch die Differenz zwischen nicht zusammenhängenden Datensätzen berechnen.

Kehren wir als Beispiel zur ersten Abfrage zurück. Angenommen, wir möchten eine weitere Spalte hinzufügen, die die Anzahl der Fälle in den letzten zwei Jahren anzeigt. Glücklicherweise gibt es bei LAG() und LEAD() einen optionalen Parameter, der angibt, wie viele Datensätze vor/nach dem aktuellen Datensatz übersprungen werden sollen. Standardmäßig ist dieser Parameter 1 (d.h. "verwende den nächsten/vorherigen Datensatz"), aber Sie können ihn auf einen anderen Wert setzen. Mit diesem neuen Parameter lautet die Abfrage also wie folgt:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS one_year_before,
  LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS two_years_before,
  population_needing_house - LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year,
  population_needing_house - LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS difference_last_two_years
FROM housing
ORDER BY 1, 2 DESC

In Blau sehen Sie die Berechnung der Differenz zwischen zwei nicht aufeinanderfolgenden Datensätzen. Im nächsten Bild ist die neue Spalte Difference last two years nur für die Werte von 2019 zu sehen, da 2019 das einzige Jahr ist, das Daten für zwei vorhergehende Jahre enthält. Die roten Linien verbinden die Ergebnisse der Funktion LAG() (Beschaffung von Daten für 2017 und Anzeige der gleichen Daten im Datensatz 2017). Sie können sehen, dass beide Linien den gleichen Wert haben.

Wir haben gezeigt, wie wichtig LAG() und LEAD() sind, aber es gibt noch viele weitere Fensterfunktionen in SQL. Lesen Sie diesen Artikel über Fensterfunktionen,um mehr über sie zu erfahren.

Mehr über SQL-Berechnungen und Fensterfuktionen

In diesem Artikel haben Sie gelernt, wie Sie die Differenz zwischen aufeinanderfolgenden Zeilen mit Hilfe von SQL berechnen können. Sie haben auch gelernt, wie Sie LAG() und LEAD() verwenden, um die Differenz zwischen nicht aufeinander folgenden Zeilen zu ermitteln. Obwohl es auch andere Möglichkeiten gibt, diese Differenz zu berechnen, z.B. mit einem Self-Join, sind diese Methoden sehr nützlich.

Wenn Sie Windows-Funktionen (oder auch SQL) häufig verwenden möchten - oder wenn Sie einfach nur Ihr Wissen verbessern wollen - ist dieser vollständige Fensterfuktionen Spickzettel eine ausgezeichnete Ressource.

Um Windows-Funktionen durch Praxis zu lernen, probieren Sie unseren Fensterfuktionen Kurs aus. In diesem Artikel können Sie mehr über den Kurs erfahren.