AsOf-Join

Was ist ein AsOf-Join?

Zeitreihendaten sind nicht immer perfekt ausgerichtet. Uhren können leicht abweichend gehen, oder es kann eine Verzögerung zwischen Ursache und Wirkung geben. Das erschwert die Verknüpfung zweier geordneter Datensätze. AsOf-Joins sind ein Werkzeug, um dieses und ähnliche Probleme zu lösen.

Ein Problem, das AsOf-Joins lösen, ist das Ermitteln des Werts einer sich ändernden Eigenschaft zu einem bestimmten Zeitpunkt. Dieser Anwendungsfall ist so häufig, dass der Name daher stammt:

Gib mir den Wert der Eigenschaft zum Stand dieses Zeitpunkts (as of this time).

Allgemeiner verkörpern AsOf-Joins jedoch gängige Semantiken der zeitlichen Analyse, die in Standard-SQL umständlich und langsam umzusetzen sind.

Beispiel-Datensatz Portfolio

Beginnen wir mit einem konkreten Beispiel. Angenommen, wir haben eine Tabelle mit Aktien-prices und Zeitstempeln:

ticker	when	price
APPL	2001-01-01 00:00:00	1
APPL	2001-01-01 00:01:00	2
APPL	2001-01-01 00:02:00	3
MSFT	2001-01-01 00:00:00	1
MSFT	2001-01-01 00:01:00	2
MSFT	2001-01-01 00:02:00	3
GOOG	2001-01-01 00:00:00	1
GOOG	2001-01-01 00:01:00	2
GOOG	2001-01-01 00:02:00	3

Wir haben eine weitere Tabelle mit Portfolio-holdings zu verschiedenen Zeitpunkten:

ticker	when	shares
APPL	2000-12-31 23:59:30	5.16
APPL	2001-01-01 00:00:30	2.94
APPL	2001-01-01 00:01:30	24.13
GOOG	2000-12-31 23:59:30	9.33
GOOG	2001-01-01 00:00:30	23.45
GOOG	2001-01-01 00:01:30	10.58
DATA	2000-12-31 23:59:30	6.65
DATA	2001-01-01 00:00:30	17.95
DATA	2001-01-01 00:01:30	18.37

Um diese Tabellen in DuckDB zu laden, führen Sie aus:

CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv';
CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv';

Innere AsOf-Joins

Wir können den Wert jeder Position zu diesem Zeitpunkt berechnen, indem wir den jüngsten Preis vor dem Zeitstempel der Position mit einem AsOf-Join finden:

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p
       ON h.ticker = p.ticker
      AND h.when >= p.when;

Dadurch wird jeder Zeile der Wert der Position zu diesem Zeitpunkt zugeordnet:

ticker	when	value
APPL	2001-01-01 00:00:30	2.94
APPL	2001-01-01 00:01:30	48.26
GOOG	2001-01-01 00:00:30	23.45
GOOG	2001-01-01 00:01:30	21.16

Im Wesentlichen wird eine Funktion ausgeführt, die nahegelegene Werte in der Tabelle prices nachschlägt. Beachten Sie außerdem, dass fehlende ticker-Werte keine Übereinstimmung haben und nicht in der Ausgabe erscheinen.

Äußere AsOf-Joins

Weil AsOf höchstens eine Übereinstimmung von der rechten Seite liefert, wächst die linke Tabelle durch den Join nicht, sie kann aber schrumpfen, wenn auf der rechten Seite Zeitpunkte fehlen. Für diese Situation können Sie einen äußeren AsOf-Join verwenden:

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF LEFT JOIN prices p
            ON h.ticker = p.ticker
           AND h.when >= p.when
ORDER BY ALL;

Wie zu erwarten, entstehen dann NULL-Preise und -Werte, statt Zeilen der linken Seite zu verwerfen, wenn kein Ticker vorhanden ist oder der Zeitpunkt vor dem Beginn der Preise liegt.

ticker	when	value
APPL	2000-12-31 23:59:30
APPL	2001-01-01 00:00:30	2.94
APPL	2001-01-01 00:01:30	48.26
GOOG	2000-12-31 23:59:30
GOOG	2001-01-01 00:00:30	23.45
GOOG	2001-01-01 00:01:30	21.16
DATA	2000-12-31 23:59:30
DATA	2001-01-01 00:00:30
DATA	2001-01-01 00:01:30

AsOf-Joins mit dem Schlüsselwort USING

Bisher haben wir die Bedingungen für AsOf explizit angegeben, SQL hat aber auch eine vereinfachte Join-Bedingungssyntax für den häufigen Fall, dass die Spaltennamen in beiden Tabellen gleich sind. Diese Syntax verwendet das Schlüsselwort USING, um die Felder aufzulisten, die auf Gleichheit verglichen werden sollen. AsOf unterstützt diese Syntax ebenfalls, allerdings mit zwei Einschränkungen:

• Das letzte Feld ist die Ungleichung
• Die Ungleichung ist >= (der häufigste Fall)

Unsere erste Abfrage kann dann so geschrieben werden:

SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

Klarstellung zur Spaltenauswahl mit USING in ASOF-Joins

Wenn Sie das Schlüsselwort USING in einem Join verwenden, werden die in der USING-Klausel angegebenen Spalten in der Ergebnismenge zusammengeführt. Das bedeutet: Wenn Sie Folgendes ausführen:

SELECT *
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

Sie erhalten nur die Spalten h.ticker, h.when, h.shares, p.price. Die Spalten ticker und when erscheinen nur einmal, wobei ticker und when aus der linken Tabelle (holdings) stammen.

Dieses Verhalten ist für die Spalte ticker in Ordnung, weil der Wert in beiden Tabellen gleich ist. Für die Spalte when können die Werte zwischen den beiden Tabellen jedoch aufgrund der Bedingung >= im AsOf-Join abweichen. Der AsOf-Join ist so ausgelegt, dass jede Zeile der linken Tabelle (holdings) anhand der Spalte when mit der nächstgelegenen vorhergehenden Zeile der rechten Tabelle (prices) abgeglichen wird.

Wenn Sie die Spalte when aus beiden Tabellen abrufen möchten, um beide Zeitstempel zu sehen, müssen Sie die Spalten explizit auflisten statt sich auf * zu verlassen, etwa so:

SELECT h.ticker, h.when AS holdings_when, p.when AS prices_when, h.shares, p.price
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

So erhalten Sie die vollständigen Informationen aus beiden Tabellen und vermeiden mögliche Verwirrung durch das Standardverhalten des Schlüsselworts USING.

Siehe auch

Implementierungsdetails finden Sie im Blogbeitrag „DuckDB’s AsOf joins: Fuzzy Temporal Lookups“.