Last Non Empty mit SQL

PDF Download

Den letzten nicht leeren Wert (LastNonEmpty) in einer Datentabelle zu ermitteln, ist eine häufige Aufgabe in OLAP-Modellen, z. B. wenn es um Währungskurse geht, welche sporadisch erfasst werden, aber auf Tagesbasis zur Berechnung herangezogen werden müssen. Wenn die MDX-Funktion LastNonEmpty nicht zur Verfügung steht oder nicht genutzt werden kann, muss eine alternative Lösung gefunden werden. Wie dies relational mit der Funktion LEAD() (verfügbar ab SQL-Server 2012) aber auch ohne diese Funktion z. B. in SQL-Server 2008R2 gelöst werden kann, zeigt dieser Blog.         

Last Non Empty mit SQL

Wozu braucht man den letzten nicht leeren Wert? Ein häufiger Anwendungsfall ist die Nutzung von Währungsumrechnungen. Meist werden Währungskurse monatlich oder pro Quartal erfasst, aber die umzurechnenden Daten liegen auf Tagesbasis vor. Wird der Währungskurs auf den Erfassungstag des entsprechenden Monats geschrieben, enthalten alle anderen Tage des Monats keine Währungskursdaten, sodass man hier den letzten nicht leeren Wert verwenden muss.

Ein anderes Beispiel sind Kosten, die pro Jahr oder in unregelmäßigen Zeitabständen definiert werden. Sie können sich unterjährig ändern oder sie bleiben das gesamte Jahr gültig. Um diese Kosten in Berechnungen auf Quartals-, Monats-, Kalenderwochen- oder Tagesbasis nutzen zu können, muss der letzte gültige Wert verwendet werden.

In den meisten OLAP-Modellen wird hierfür die MDX-Aggregatfunktion LastNonEmpty eingesetzt, welche in der Enterprise-Edition von SQL-Server enthalten ist. Wer nicht über diese Edition verfügt und beispielsweise die Standard-Edition einsetzt und trotzdem den letzten nicht leeren Wert einer Datentabelle abrufen möchte, muss Alternativen finden.

Eine Lösung mithilfe von MDX findet man unter: http://www.ssas-info.com/analysis-services-articles/50-mdx/2333-getting-the-last-non-empty-value                                    Eine Lösung auf Basis von SQL auf der Ebene der relationalen Datenbank wird im Folgenden vorgestellt.

1 Letzter nicht leerer Wert mithilfe der Funktion LEAD()

Ab der SQL-Server Version 2012 stehen die Funktionen LAG(), welche den Vorgänger und LEAD(), welche den Nachfolger eines Elements in einer Datentabelle ermittelt, zur Verfügung.

In diesem Blog wird die Funktion Lead() verwendet, die an dieser Stelle kurz vorgestellt werden soll:

LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
Scalar expression – die numerische Spalte einer Datenbanktabelle, deren Nachfolger ermittelt werden soll.
Offset – der Abstand des Nachfolgerwertes zum aktuellen Wert, immer ein ganzzahliger nicht negativer Wert.
Default – Der Wert, der zurückgeben werden soll, wenn es keinen Nachfolger gibt oder dieser NULL ist.
OVER
partition_by_clause – Angabe der Tabellenspalten, nach denen unterteilt werden soll.
order_by_clause – Angabe der Tabellenspalten nach denen sortiert werden soll.

Anhand eines Beispiels soll die Ermittlung des letzten nicht leeren Werts demonstriert werden.

Dafür wird auf der bekannten Demodatenbank Chair oder einer anderen Datenbank, die eine Tabelle mit Perioden wie die hier verwendete Tabelle T_Import_Periode_manuell enthält, mit dem folgenden SQL-Statement die Tabelle T_S_AVG_Costs erstellt und mit Werten gefüllt:

Die neu erstellte Tabelle enthält Kosten für verschiedene Produkte (hier nur durch die entsprechenden ProduktIDs dargestellt) und Monate. Die Kosten ändern sich in unregelmäßigen Abständen und sind nur für die Monate eingetragen, an denen sich der Wert tatsächlich ändert. Um auch in den Monaten, für welche keine Werte vorhanden sind, mit den Kosten rechnen zu können, werden mit folgendem SQL-Statement die Werte für die fehlenden Monate ergänzt:


Mit der ersten WITH_Klausel „CTE“ wird die Zeitspanne ermittelt, in welcher ein Kostenwert für ein Produkt gültig ist. Hier wird die Funktion LEAD() eingesetzt, um zu jedem Periodenwert pro Produkt den entsprechenden Nachfolger zu ermitteln. Gibt es keinen Nachfolger, gibt die Funktion den Wert „0“ zurück (siehe Abbildung 1, Spalte „NextPeriod“).


Abbildung 1: Ergebnis des Selects mit der Funktion LEAD()

Mit der nächsten WITH-Klausel „CT_Periode“ werden aus der Tabelle T_Import_Periode_manuell alle erforderlichen Perioden selektiert. Die Einschränkung auf Werte ab dem Jahr 2017 erfolgt hier um die Anzahl der Datensätze im Beispiel der Übersichtlichkeit halber zu minimieren.

Die dritte WITH-Klausel „CT_MaxPeriod“ ermittelt den letzten Wert pro Produkt, für den ein Kostenwert existiert.

Mit dem sich an die WITH-Klauseln anschließenden SELECT-Befehl werden die Werte pro Produkt und Monat bis zum Ende des Jahres, in welchem sich der letzte Kostenwert befindet, zusammengestellt. So erhält man für jeden Monat pro Produkt die korrekten Kostenwerte.


Abbildung 2 Select-Ergebnis für ProductID = 123

2 Letzter nicht leerer Wert ohne Einsatz der Funktion LEAD()

Nun mag mancher Leser denken, schön und gut, aber ich setze noch SQL-Server 2008R2 ein und kann daher die SQL-Funktion LEAD() nicht verwenden. Für alle die dies betrifft, gibt es auch eine Lösung ohne LEAD().

Verwendet wird hier ebenfalls die unter Punkt 1 erstellte und befüllte Tabelle T_S_AVG_Costs.

Danach setzten wir folgendes SQL-Statement ein:

Mit der ersten WITH-Klausel „CT_Costs“ wird auch in diesem Beispiel die Zeitspanne ermittelt, in welcher ein Kostenwert für ein Produkt gültig ist, wobei dieser Select keinen Datensatz zurückgibt, wenn es keinen weiteren Folgewert gibt. Im Vergleich zu dem Ergebnis in Abbildung1 fehlen hier die Werte für den letzten eingetragenen Wert mit NextPeriod = 0.


Abbildung 3: Select-Ergebnis ohne Funktion LEAD()

Die WITH-Klausel „CT_Periode“ selektiert wie im Beispiel in Punkt 1 alle Monatswerte ab 2017.

Im anschließenden SELECT-Befehl werden die Werte pro Produkt und Monat bis zum Ende des Jahres, in welchem sich der letzte Kostenwert befindet, zusammengestellt. Die Werte für die letzte gültige Periode müssen aus der Tabelle T_S_AVG_Costs ermittelt werden, da sie in „CT_Costs“ nicht enthalten sind. Diese Daten werden anschließend bis zum Jahresende fortgeschrieben.

Dieser erneute Zugriff auf die Datentabelle T_S_AVG_Costs ist der Nachteil bei der Selektion ohne die Verwendung der Funktion Lead(). Die im Beispiel verwendeten Tabelle enthält nur wenige Datensätze, was in der Praxis meist nicht der Fall ist. So kann der erneute Zugriff auf die Datentabelle Performanceeinbußen nach sich ziehen.

 

3 Fazit

Sind Daten nicht für alle Periodenelemente vorhanden und die MDX-Aggregatfunktion LastNonEmpty nicht verfügbar, können die Daten für die fehlenden Perioden per SQL fortgeschrieben werden. Das erhöht zwar die Anzahl der Zeilen in der entsprechenden Selektion, ermöglicht es aber ebenfalls mit dem jeweils korrekten Wert zu rechnen.

Ein weiterer Vorteil der Berechnung in der relationalen Datenbank ist, dass die Daten im Würfel bereits zur Verfügung stehen und nicht per MDX zur Laufzeit ermittelt werden.

Schreibe einen Kommentar