Wie hat sich Ihr Unternehmen in diesem Jahr im Vergleich zum letzten Jahr entwickelt? Und im Jahr davor? Erfahren Sie, wie Sie mit SQL die Unterschiede von Jahr zu Jahr und von Monat zu Monat berechnen können.

Jahresvergleiche sind ein beliebtes und effektives Mittel, um die Leistung verschiedener Arten von Unternehmen zu bewerten. Unterschiede von Jahr zu Jahr sind leicht zu verstehen - z. B. ist es einfach zu verstehen, dass Ihr Umsatzwachstum im Vergleich zum Vorjahr 35 % beträgt. Die Berechnung dieser Kennzahl in SQL ist jedoch nicht so einfach!

In diesem Artikel erfahren Sie, wie Sie mit SQL die Unterschiede von Jahr zu Jahr und von Monat zu Monat berechnen können.

Sind Sie bereit, in Ihre SQL-Kenntnisse zu investieren? Los geht's!

Was sind Jahr-zu-Jahr-Kennzahlen aus geschäftlicher Sicht?

Um zu wissen, ob ein Unternehmen gut abschneidet, müssen wir normalerweise einen Vergleich auf der Grundlage von Geschäftskennzahlen durchführen. Manchmal können wir mit ähnlichen Unternehmen oder mit dem Durchschnitt mehrerer Unternehmen auf demselben Markt vergleichen. Ein wichtiger Leistungsindikator ist jedoch das periodische Wachstum: der Vergleich der aktuellen Ergebnisse Ihres Unternehmens mit der gleichen Kennzahl eines früheren Zeitraums. Dies ist der Grund für den Vergleich von Jahr zu Jahr: Sie können leicht erkennen, ob Ihr Unternehmen besser (oder schlechter) abschneidet als im letzten Jahr. Darüber hinaus können Jahresvergleiche auf verschiedene Kennzahlen (z. B. Umsatz, Gewinn, Kundenzahl) angewandt werden, um besser zu verstehen, wie sich die verschiedenen Geschäftsindikatoren entwickeln.

In der folgenden Tabelle sehen wir die Ergebnisse für 2019 und 2020:

Wenn wir eine dritte Spalte mit der Bezeichnung YOY hinzufügen, können wir die Schwankungen von Jahr zu Jahr leicht erkennen. So können wir besser verstehen, wie unsere Organisation im letzten Jahr abgeschnitten hat und welche Bereiche verbessert werden können:

In diesem Artikel werden wir erklären, wie wir die Werte in der YOY-Spalte berechnen können. Beginnen wir mit der Basistabelle, in der wir die Metriken für 2019 und 2020 haben. In der nächsten Abbildung sehen wir diese Werte in der yearly_metrics Tabelle.

Im nächsten Abschnitt wird erklärt, wie man die Tabelle yearly_metrics Tabelle mit SQL abfragt, um die Werte in der YOY-Spalte zu erhalten. Zuvor möchte ich Ihnen den LearnSQL.de's Fensterfunktionen empfehlen, in dem Sie die Grundlagen der SQL-Fensterfunktionen lernen können. Auch der Artikel Wann verwende ich SQL Fensterfunktionen enthält viele Beispielabfragen, die Sie vielleicht hilfreich finden.

Berechnung von YOY-Kennzahlen mit SQL

Fensterfunktionen sind eine sehr leistungsfähige SQL-Funktion. Sie geben das Ergebnis der Anwendung einer Funktion (wie MAX(), AVG() oder COUNT()) auf eine Gruppe von Datensätzen (das so genannte "Fenster") in einer Tabelle zurück. Die Menge der Datensätze wird durch die Klausel OVER() definiert; diese Klausel ist für die Fensterfunktion obligatorisch. Sie können die Datensätze im Fenster auch nach verschiedenen Kriterien ordnen und dann Funktionen wie FIRST_VALUE(), LAST_VALUE(), LEAD() oder LAG() verwenden, um den Wert von bestimmten Datensätzen in Bezug auf den aktuellen Datensatz zurückzugeben.

Wenn Sie sich die Grundlagen der Fensterfunktionen ansehen möchten, empfehle ich Ihnen den Artikel Was ist die Over-Klausel? Das Verständnis von SQL-Fensterfunktionen und der OVER -Klausel wird Ihnen bei den Konzepten helfen, die wir in diesem Artikel besprechen werden.

Sehen wir uns nun ein Beispiel für eine SQL-Abfrage an, die die Einnahmen für jedes Jahr und das vorangegangene Jahr zurückgibt:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
FROM   yearly_metrics

In diesem Beispiel verwenden wir die Funktion LAG() window, um den Wert der Spalte revenue für den vorherigen Datensatz zu erhalten. Mit anderen Worten: Wenn der aktuelle Datensatz für das Jahr 2020 ist, gibt LAG(revenue) den Wert der Umsatzspalte für das Jahr 2019 zurück. Die Ergebnisse dieser Abfrage sind:

Der nächste Schritt, um den Jährlichen Wert für den Umsatz zu erhalten, ist einfach: Wir müssen nur die Differenz zwischen dem Umsatz für 2020 und dem Umsatz für 2019 berechnen. Dazu gehen wir folgendermaßen vor:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
       revenue - LAG(revenue) OVER ( ORDER BY year ) AS YOY_Difference
FROM   yearly_metrics

Wir berechnen die YOY Difference, indem wir eine arithmetische Differenz zwischen den Einnahmen im Jahr 2020 und den Einnahmen im Jahr 2019 bilden. Im folgenden Ergebnis sehen wir, dass der Datensatz für 2019 keinen YOY Difference Wert hat, weil wir keine Daten für 2018 haben. Auch der Datensatz für 2021 hat keinen konsistenten Wert in YOY Difference, weil die Daten für 2021 nicht vollständig sind.

Bevor wir zum nächsten Abschnitt übergehen, möchte ich Ihnen den Artikel Wie man die differenz zwischen zwei zeilen in SQL berechnet empfehlen, in dem die Berechnung von Differenzen mit LAG() und LEAD() näher erläutert wird.

Berechnung von Monat-zu-Monat- und Quartal-zu-Quartal-Differenzen

Im vorigen Abschnitt konnten wir keinen konsistenten Wert für YOY Difference für das Jahr 2021 berechnen, da uns keine vollständigen Ergebnisse für 2021 vorliegen. Um jedoch zu sehen, wie ein Unternehmen im laufenden Jahr abschneidet, sollten wir über einige Kennzahlen verfügen, die seine Leistung beschreiben. Gute Leistungsindikatoren können Vergleiche von Monat zu Monat oder von Quartal zu Quartal sein (z. B. ein Vergleich des ersten Quartals 2021 mit dem ersten Quartal 2020).

Bevor wir die Unterschiede von Monat zu Monat oder von Quartal zu Quartal berechnen, schauen wir uns die daily_metrics Tabelle. Sie enthält für jeden Tag einen Datensatz, der den Umsatz, die Kosten und die Anzahl der Neukunden für diesen Tag beschreibt.

Als Nächstes berechnen wir die monthly_metrics CTE (eine CTE ist ähnlich wie eine Ansicht, wird aber während der Abfrageausführung erstellt). Diese hat ein ähnliches Schema wie die yearly_metrics Tabelle, die zuvor verwendet wurde. Der SELECT zur Erstellung von monthly_metrics ist in rot dargestellt; in blau sehen wir die SQL-Abfrage, die dieses CTE wie eine normale Tabelle verwendet.

WITH monthly_metrics AS (
 SELECT 
   extract(year from day) as year,
   extract(month from day) as month,
   SUM(revenue) as revenue
 FROM daily_metrics 
 GROUP BY year, month 
)
SELECT 
  year, month, revenue,
  LAG(revenue) OVER (ORDER BY year, month) as Revenue_previous_month,
  revenue - LAG(revenue) OVER (ORDER BY year, month) as Month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

In der obigen Abfrage generieren wir zunächst die monthly_metrics CTE mit einer SELECT, die das Jahr und den Monat aus der Tagesspalte extrahiert. Dann gruppieren wir nach Jahr und Monat und berechnen die SUM der Einnahmen für jeden Monat. Die CTE monthly_metrics funktioniert wie eine reguläre Tabelle mit den Spalten year, month und revenue; wenn die Abfrage endet, wird die CTE zerstört. Sie können die Differenz von Quartal zu Quartal auf ähnliche Weise berechnen.

Danach (in blau) erhalten wir die Einnahmen für den Vormonat mit der Fensterfunktion LAG(). Dann berechnen wir die Differenz zwischen jedem Monat und dem Vormonat. Hier sehen Sie eine Teilansicht des Ergebnisses:

Es gibt noch eine andere Möglichkeit, die Differenz von Monat zu Monat zu berechnen. Anstatt mit dem Vormonat zu vergleichen, können wir auch mit demselben Monat im Vorjahr vergleichen. Für diesen Vergleich müssen wir den optionalen Offset-Parameter der Funktion LAG() verwenden, wie wir in der folgenden Abfrage sehen können:

WITH monthly_metrics AS (
SELECT EXTRACT(year from day) as year,
	 EXTRACT(month from day) as month,
       SUM(revenue) as revenue
  FROM daily_metrics 
  GROUP BY 1,2
)
SELECT year AS current_year, 
       month AS current_month, 
       revenue AS revenue_current_month, 
       LAG(year,12) OVER ( ORDER BY year, month) AS previous_year, 
       LAG(month,12) OVER ( ORDER BY year, month) AS month_comparing_with,
       LAG(revenue,12) OVER ( ORDER BY year, month) AS revenue_12_months_ago,
       revenue - LAG(revenue,12) OVER (ORDER BY year, month) AS month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

Oben haben wir die Funktion LAG() mit dem optionalen Offset-Parameter verwendet, der es uns ermöglicht, einen Spaltenwert aus einem Datensatz N Positionen vor dem aktuellen Datensatz zu erhalten. Wenn wir einen Versatz von 12 verwenden, erhalten wir den Datensatz für denselben Monat, aber im vorherigen Jahr. Unten sehen Sie das Ergebnis:

Und das war's! Jetzt wissen Sie, wie Sie mit den SQL-Fensterfunktionen Unterschiede von Monat zu Monat, Quartal zu Quartal und Jahr zu Jahr ermitteln können. Ich empfehle den KursFensterfunktionen als gute Einführung in die Arbeit mit diesen Funktionen. Wenn Sie mehr erfahren möchten, lesen Sie diesen Artikel, in dem der Kurs Fensterfunktionen beschrieben wird.

Wie geht es weiter?

Fensterfunktionen sind eine wichtige SQL-Ressource. In diesem Artikel haben wir die Fensterfunktion LAG() verwendet, um Unterschiede von Jahr zu Jahr und von Monat zu Monat zu berechnen. Tatsächlich können wir damit die Differenz zwischen beliebigen Zeiträumen berechnen - Quartale, Halbjahre, Monate oder Wochen.

In diesem Artikel haben wir uns auf Unterschiede zwischen Zeiträumen konzentriert, aber Fensterfunktionen können zur Lösung vieler verschiedener Datenprobleme eingesetzt werden. Zu guter Letzt möchte ich Ihnen noch unseren Fensterfunktionen Spickzettel ans Herz legen, den ich an einer Korkplatte vor meinem Schreibtisch hängen habe. Investieren Sie in sich selbst und erweitern Sie Ihre SQL-Kenntnisse!