uweziegenhagen.de

Schauen wir uns das Forward Filling an. Je nach Version des SQL Servers funktioniert eine Version oder leider nicht. 🙂

Mit SQL Server 2022 kann man den folgenden Code nutzen, um einen weiteren CTE zu bauen:

Filled AS (
    SELECT
        ContNo,
        MonthEnd,
        Amount,
        last_value(Amount) IGNORE NULLS
        OVER (
            PARTITION BY ContNo
            ORDER BY MonthEnd
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ForwardFilledAmount
    FROM Combined
)
SELECT * FROM Filled

Versionen von SQL Server vor 2022 unterstützen leider „ignore nulls“ nicht, hier kann man dann folgenden Code nutzen:

Filled AS (
    SELECT
        c.ContNo,
        c.MonthEnd,
        c.Amount,
        ca.ForwardFilledAmount
    FROM Combined c
    OUTER APPLY (
        SELECT TOP 1 Amount AS ForwardFilledAmount
        FROM Combined c2
        WHERE c2.ContNo = c.ContNo
          AND c2.MonthEnd <= c.MonthEnd
          AND c2.Amount IS NOT NULL
        ORDER BY c2.MonthEnd DESC
    ) ca
)
SELECT * FROM Filled

OUTER APPLY funktioniert dabei so ähnlich wie ein LEFT JOIN. Der Unterschied besteht darin, dass beim OUTER APPLY die rechte Seite von der linken Seite abhängt. In einem weiteren Artikel zum Thema werde ich noch ein paar Beispiele dazu zeigen. CROSS APPLY ist ähnlich, hier ist es aber kein Äquivalent zum LEFT JOIN, sondern zum INNER JOIN. Auch dazu mehr in einem der weiteren Artikel.

Wir kennen jetzt das Problem, nun können wir uns an die Lösung wagen! In diesem Blogbeitrag werden wir alles vorbereiten, was wir für das anschließende „Forward Filling“ der Daten brauchen.

Im ersten Schritt bestimmen wir für die einzelnen Verträge die Monatsendwerte von Start des jeweiligen Vertrags bis zu dessen Ende. Dazu nutze ich den den folgenden rekursiven CTE (Common Table Expression)

WITH MonthSequence AS (
    SELECT
        c.ContNo,
        EOMONTH(c.StartDate) AS MonthEnd,
        c.EndDate
    FROM Contracts c
    UNION ALL
    SELECT
        ms.ContNo,
        EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)),
        ms.EndDate
    FROM MonthSequence ms
    WHERE EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)) <= ms.EndDate
)

Das erzeugt uns die folgende Sequenz:

Als nächstes suchen wir uns die End-of-Month der Cashflows raus, die wir im nächsten Schritt mit den End-of-Month aus Schritt 1 kombinieren werden.

WITH CashflowBuckets AS (
    SELECT
        ContNo,
        EOMONTH(CashflowDate) AS MonthEnd,
        Amount
    FROM Cashflows
)
SELECT * FROM CashflowBuckets

Die Kombination ist dann recht einfach und gibt uns die Liste aller End-of-Months aus mit den dazugehörigen Cashflows.

WITH MonthSequence AS (
    SELECT
        c.ContNo,
        EOMONTH(c.StartDate) AS MonthEnd,
        c.EndDate
    FROM Contracts c
    UNION ALL
    SELECT
        ms.ContNo,
        EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)),
        ms.EndDate
    FROM MonthSequence ms
    WHERE EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)) <= ms.EndDate
)
,CashflowBuckets AS (
    SELECT
        ContNo,
        EOMONTH(CashflowDate) AS MonthEnd,
        Amount
    FROM Cashflows
)
,Combined AS (
   SELECT
        ms.ContNo,
        ms.MonthEnd,
        cb.Amount
    FROM MonthSequence ms
    LEFT JOIN CashflowBuckets cb
        ON ms.ContNo = cb.ContNo
        AND ms.MonthEnd = cb.MonthEnd
)
 
SELECT * FROM Combined

In gelb sind die Einträge markiert, die wir im finalen Schritt mit den jeweils letzten gültigen Cashflow-Werten befüllen müssen.

Ich halte mich schon recht erfahren im Umgang SQL, kürzlich bin ich aber an einer Ecke von SQL vorbeigekommen, die ich auch noch nicht kannte. Dabei handelt es sind um die „OUTER APPLY“ bzw. „CROSS APPLY“ Operatoren.

Um die Lösung herzuleiten betrachten wir zuerst das Problem:

Gegeben seien Kreditverträge mit verschiedenen Zahlplänen. Ein Vertrag zahlt vielleicht monatlich, einer zahlt quartalsweise. Ein Vertrag hat dabei ein Startdatum und ein Enddatum sowie verschiedene Cashflows.

Hier ein paar Testdaten mit den entsprechenden Tabellen, die dazugehörigen SQL-Statements folgen später.

Cashflows

Cashflows

Das Problem ist jetzt, wie kann man eine monatliche Übersicht pro Vertrag bekommen, bei der in den Monaten, wo es kein Cashflow gab, einfach der letzte Cashflow angezeigt wird? Das Endergebnis soll wie folgt aussehen:

Im nächsten Teil fangen wir dann mit den SQL Statements an.

Hier eine Übersicht zum Errechnen von Datumswerten in T-SQL

Stackoverflow (https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) hatte gestern interessanten Code für mich, um den Rowcount aller Tabellen in einer MS SQL Server DB zu ermitteln:


SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0;

Hier ein cleveres Beispiel aus dem Internet (Quelle habe ich leider nicht mehr) dafür, wie man mit pandas einfach SQL Inserts erzeugen kann. In der Datei Daten.csv finden sich die einzufügenden Daten zusammen mit den entsprechenden Spaltennamen der Datenbanktabelle.

Über df.columns bekommen wir dann beim Insert die benötigten Spaltennamen aus dem DataFrame geliefert, über das Tuple der Zeilenwerte row.values die einzufügenden Werte.

import pandas as pd
 
df = pd.read_csv('Daten.csv', sep=';', decimal=',')
 
with open('Statements2.sql', 'w') as o:
    for index, row in df.iterrows():
        o.write('INSERT INTO aaaaaa('+ str(', '.join(df.columns))+ ') VALUES '+ str(tuple(row.values))+';\n')

Bei einem kleinen Projekt, an dem ich arbeite, fiel mir auf, dass fehlerhafte — d.h. syntaktisch richtige, aber inhaltlich falsche — SQLite Updates im Nirvana verschwinden. Danke Google und Stackexchange war die Lösung wie folgt: fehlerhafte SQL-Updates erzeugen keine Exception, hier muss man nach dem Update den rowcount prüfen, um die Anzahl der aktualisierten Zeilen zu checken. Das folgende Programm illustriert das ganze:

import sqlite3
 
conn = sqlite3.connect(':memory:')
conn.execute('create table if not exists mytable(id INTEGER PRIMARY KEY \
             AUTOINCREMENT, status TEXT, prio TEXT)')
 
today = '2020-08-06'
cursor = conn.cursor()
task = "Foobar"
 
cursor.execute('insert into mytable(status, prio) values (?, ?)',
               [task, today])
conn.commit()
 
my_id = 1 # For my_id = 1 it works, for any other number rowcount is 0
prio = 'A'
cursor = conn.cursor()
cursor.execute('UPDATE mytable SET prio = ? WHERE id = ?', [prio, my_id])
conn.commit()
print(cursor.rowcount)
 
try:
    cursor = conn.cursor()
    cursor.execute('select * from mytable')
    results = cursor.fetchall()
    for entry in results:
        print(entry)
except Exception as err:
    print('Listing failed: %s\nError: %s' % str(err))
 
finally:
    cursor.close()

In diesem Artikel geht es um die 3. Normalform. 3. Normalform bedeutet, dass die Daten

• in der 2. Normalform sind und
• kein Nichtschlüsselattribut transitiv von einem Kandidatenschlüssel abhängt.

Diese transitive Abhängigkeit erklärt man am besten an einem Beispiel:

CD

• Aus der CD-ID folgt der Interpret oder die Interpretin
• Aus dem Interpreten können wir nicht auf die CD schließen, denn jeder Interpret oder jede Interpretin kann mehr als eine CD veröffentlichen.
• Aus dem Interpreten folgt aber das Geburtsjahr

Damit hängt das Geburtsjahr (ein Nichtschlüsselattribut) transitiv von der CD-ID (dem Schlüssel/Schlüsselkandidaten) ab. Transitiv bedeutet formell ausgedrückt: „Eine zweistellige Relation R heißt transitiv, wenn aus a R b und b R c stets a R c folgt.“

Wie löst man die transitive Abhängigkeit auf? Indem man weitere Tabellen erstellt. In unserem Beispiel erstellen wir eine Interpreten-Tabelle und verweisen in der CD-Tabelle nur noch auf den entsprechenden Schlüssel.

Interpret

CD

Titel

Im ersten Artikel dieser Reihe hatten wir uns die 1. Normalform angeschaut, in diesem Artikel soll es um die 2. Normalform gehen.

Die Wikipedia sagt zur 2. Normalform: „Eine Relation ist genau dann in der zweiten Normalform, wenn die erste Normalform vorliegt und kein Nichtprimärattribut (Attribut, das nicht Teil eines Schlüsselkandidaten ist) funktional von einer echten Teilmenge eines Schlüsselkandidaten abhängt.“

Das hört sich etwas sperrig an, am besten betrachten wir unser Beispiel, das wir in die 1. Normalform gebracht hatten, die Schlüsselspalten seien CD ID und Tracknummer.

Wir erkennen, dass viele Informationen redundant sind, was leicht zu Dateninkonsistenzen führen kann. Ändern wir beispielsweise in einer Zeile den Albumtitel und nur den, so haben wir zwei unterschiedliche Albentitel für ein und das selbe Album. Außerdem hängen Albumtitel, Interpret und Erscheinungsjahr nur vom Schlüssel CD-ID ab, nicht von der Track-ID.

Man löst diese Probleme und Redundanzen auf, indem man die Daten auf zwei Tabellen aufteilt, CD und Titel.

CD

Titel

Am besten lernt man, wenn man lehrt! Da ich mich momentan intensiver mit Themen wie Data Warehouse, Data Vault, 3NF und Dimensional Modelling beschäftige, versuche ich in den Artikeln dieser Reihe für mich ein wenig Licht in das Begriffsdunkel zu bringen. Quellen dieser Artikel sind die entsprechenden Wikipedia-Artikel, Google Ergebnisse und die 7. Auflage des Buchs „Fundamentals of Database Systems“ von Elmasri und Navathe, das es bei Amazon für knapp 45 Euro gibt.

In diesem Artikel soll es um die Normalformen gehen, siehe dazu den Wikipedia-Artikel für die Grundlagen.

Betrachten wir als Beispiel einen Satz Musikdaten, bestehend jeweils aus Interpret, Albumtitel, Erscheinungsjahr, Geburtsjahr des Interpreten, Titelliste:

Gabi Mustermann - Gabi singt, 2000, 1963, {1. Gabi singt laut, 2. Gabi singt leise, 3. Gabi singt weiter}
Max Mustermann - Debütalbum, 2001, 1960, {1. Von der Liebe, 2. Vom Leben, 3. Vom Ableben, 4. Duett mit Gabi} 

Diese Daten werden wir im Folgenden in die entsprechenden Normalformen bringen.

1NF

Für die 1. Normalform gilt (laut Wikipedia): Jedes Attribut der Relation muss einen atomaren/atomischen Wertebereich haben, und die Relation muss frei von Wiederholungsgruppen sein.

• Atomar heißt, dass zusammengesetzte, mengenwertige oder geschachtelte Wertebereiche (also relationenwertige Attributwertebereiche) nicht erlaubt sind. Der Wertebereich keines Attributs einer Relation in 1NF kann in weitere (sinnvolle) Teilbereiche aufgespaltet werden.
• Frei von Wiederholungsgruppen bedeutet, dass Attribute, die gleiche oder gleichartige Information enthalten, in eine andere Relation ausgelagert werden müssen.

Angewandt auf unser Beispiel bedeutet

• atomar, dass wir sinnvoll aufteilbar Attribute wie Interpret-Albumtitel in entsprechende einzelne Attribute aufteilen müssen.
• frei von Wiederholungsgruppen, dass die Liste der Lieddaten auf einzelne Zeilen verteilt werden muss.

Für unsere Musikdaten ergibt sich daher die folgende 1NF: