Time series data is not always perfectly aligned. Clocks may be slightly off, or there may be a delay between cause and effect. This can make connecting two sets of ordered data challenging. AsOf joins are a tool for solving this and other similar problems.
One of the problems that AsOf joins are used to solve is
finding the value of a varying property at a specific point in time.
This use case is so common that it is where the name came from:
Give me the value of the property as of this time.
More generally, however, AsOf joins embody some common temporal analytic semantics, which can be cumbersome and slow to implement in standard SQL.
Let’s start with a concrete example.
Suppose we have a table of stock
prices with timestamps:
We have another table containing portfolio
holdings at various points in time:
To load these tables to DuckDB, run:
CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv'; CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv';
We can compute the value of each holding at that point in time by finding the most recent price before the holding’s timestamp by using an AsOf Join:
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;
This attaches the value of the holding at that time to each row:
It essentially executes a function defined by looking up nearby values in the
Note also that missing
ticker values do not have a match and don’t appear in the output.
Because AsOf produces at most one match from the right hand side, the left side table will not grow as a result of the join, but it could shrink if there are missing times on the right. To handle this situation, you can use an outer AsOf Join:
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;
As you might expect, this will produce
NULL prices and values instead of dropping left side rows
when there is no ticker or the time is before the prices begin.
So far we have been explicit about specifying the conditions for AsOf,
but SQL also has a simplified join condition syntax
for the common case where the column names are the same in both tables.
This syntax uses the
USING keyword to list the fields that should be compared for equality.
AsOf also supports this syntax, but with two restrictions:
- The last field is the inequality
- The inequality is
>=(the most common case)
Our first query can then be written as:
SELECT ticker, h.when, price * shares AS value FROM holdings h ASOF JOIN prices p USING(ticker, when);
Be aware that if you don’t explicitly list the columns in the
the ordering field value will be the probe value, not the build value.
For a natural join, this is not an issue because all the conditions are equalities,
but for AsOf, one side has to be chosen.
Since AsOf can be viewed as a lookup function,
it is more natural to return the “function arguments” than the function internals.
For implementation details, see the blog post “DuckDB’s AsOf joins: Fuzzy Temporal Lookups”.