ASOF JOIN keyword
ASOF JOIN is a powerful SQL keyword that allows you to join two time-series tables.
It is a variant of the JOIN
keyword and shares
many of its execution traits.
This document will demonstrate how to utilize them, and link to other relevant JOIN context.
JOIN overview
The JOIN operation is broken into three components:
- Select clause
- Join clause
- Where clause
This document will demonstrate the JOIN clause, where the other keywords demonstrate their respective clauses.
Visualized, a JOIN operation looks like this:
-
selectClause
- see the SELECT reference docs for more information. -
joinClause
ASOF JOIN
with an optionalON
clause which allows only the=
predicate: -
whereClause
- see the WHERE reference docs for more information.
In addition, the following are items of import:
-
Columns from joined tables are combined in a single row.
-
Columns with the same name originating from different tables will be automatically aliased into a unique column namespace of the result set.
-
Though it is usually preferable to explicitly specify join conditions, QuestDB will analyze
WHERE
clauses for implicit join conditions and will derive transient join conditions where necessary.
Execution order
Join operations are performed in order of their appearance in a SQL query.
Read more about execution order in the JOIN reference documentation.
ASOF JOIN
ASOF JOIN
joins two time-series on their timestamp, using the following
logic: for each row in the first time-series,
- consider all timestamps in the second time-series earlier or equal to the first one
- choose the latest such timestamp
Example
Let's use an example with two tables:
trades
: trade events on a single stockorder_book
: level-1 order book snapshots for that stock
trades
data:
timestamp | price | size |
---|---|---|
08:00:00.007140 | 175.97 | 400 |
08:00:00.609618 | 178.55 | 400 |
08:00:00.672131 | 176.09 | 400 |
08:00:00.672147 | 176.03 | 400 |
08:00:01.146931 | 175.45 | 400 |
08:00:01.495188 | 177.90 | 400 |
08:00:01.991977 | 175.35 | 400 |
08:00:01.991991 | 175.36 | 400 |
08:00:02.039451 | 175.36 | 400 |
08:00:02.836413 | 175.55 | 400 |
08:00:03.447858 | 176.79 | 400 |
08:00:04.782191 | 181.00 | 15 |
08:00:05.408871 | 175.77 | 400 |
08:00:06.007145 | 176.52 | 400 |
08:00:06.740159 | 184.00 | 1 |
08:00:07.593841 | 175.75 | 400 |
08:00:10.310291 | 176.38 | 29 |
08:00:10.550535 | 175.86 | 400 |
08:00:10.761790 | 175.94 | 400 |
08:00:12.046660 | 176.15 | 400 |
08:00:12.897624 | 176.62 | 400 |
08:00:13.838193 | 176.51 | 25 |
08:00:15.125509 | 176.17 | 400 |
08:00:16.727077 | 176.48 | 400 |
08:00:18.813886 | 176.68 | 400 |
08:00:22.180535 | 176.05 | 400 |
08:00:25.125634 | 176.16 | 400 |
08:00:26.117889 | 176.33 | 1 |
08:00:26.184839 | 176.52 | 400 |
08:00:26.185102 | 176.41 | 25 |
order_book
data:
timestamp | bid_price | bid_size | ask_price | ask_size |
---|---|---|---|---|
08:00:00 | 176.47 | 5542 | 176.82 | 13054 |
08:00:01 | 176.33 | 4744 | 176.6 | 8404 |
08:00:02 | 176.07 | 136 | 176.76 | 4946 |
08:00:03 | 176.07 | 84 | 176.75 | 2182 |
08:00:04 | 176.07 | 112 | 176.59 | 2734 |
08:00:05 | 176.38 | 212 | 176.5 | 6966 |
08:00:06 | 176.33 | 176 | 176.52 | 8174 |
08:00:07 | 176.33 | 276 | 176.67 | 7345 |
08:00:08 | 176.33 | 48 | 176.67 | 1600 |
08:00:09 | 176.35 | 66 | 176.67 | 2400 |
08:00:10 | 176.36 | 695 | 176.38 | 20698 |
08:00:11 | 176.35 | 98 | 176.59 | 2800 |
08:00:12 | 176.48 | 104 | 176.59 | 4040 |
08:00:13 | 176.48 | 165 | 176.38 | 6035 |
08:00:14 | 176.35 | 56 | 176.38 | 720 |
08:00:15 | 176.35 | 119 | 176.38 | 1530 |
08:00:16 | 176.35 | 133 | 176.38 | 3710 |
08:00:18 | 176.35 | 84 | 176.38 | 1880 |
08:00:19 | 176.35 | 14 | 176.38 | 180 |
08:00:20 | 176.35 | 14 | 176.38 | 180 |
08:00:21 | 176.35 | 112 | 176.38 | 1440 |
08:00:22 | 176.35 | 133 | 176.38 | 1710 |
08:00:25 | 176.35 | 122 | 176.38 | 3929 |
08:00:26 | 176.35 | 300 | 176.37 | 6952 |
08:00:28 | 176.07 | 28 | 176.37 | 496 |
We want to join each trade event to the relevant order book snapshot. All we have to write is
trades ASOF JOIN order_book
and we get this result:
timestamp | price | size | timestamp1 | bid_price | bid_size | ask_price | ask_size |
---|---|---|---|---|---|---|---|
08:00:00.007140 | 175.97 | 400 | 08:00:00 | 176.47 | 5542 | 176.82 | 13054 |
08:00:00.609618 | 178.55 | 400 | 08:00:00 | 176.47 | 5542 | 176.82 | 13054 |
08:00:00.672131 | 176.09 | 400 | 08:00:00 | 176.47 | 5542 | 176.82 | 13054 |
08:00:00.672147 | 176.03 | 400 | 08:00:00 | 176.47 | 5542 | 176.82 | 13054 |
08:00:01.146931 | 175.45 | 400 | 08:00:01 | 176.33 | 4744 | 176.60 | 8404 |
08:00:01.495188 | 177.90 | 400 | 08:00:01 | 176.33 | 4744 | 176.60 | 8404 |
08:00:01.991977 | 175.35 | 400 | 08:00:01 | 176.33 | 4744 | 176.60 | 8404 |
08:00:01.991991 | 175.36 | 400 | 08:00:01 | 176.33 | 4744 | 176.60 | 8404 |
08:00:02.039451 | 175.36 | 400 | 08:00:02 | 176.07 | 136 | 176.76 | 4946 |
08:00:02.836413 | 175.55 | 400 | 08:00:02 | 176.07 | 136 | 176.76 | 4946 |
08:00:03.447858 | 176.79 | 400 | 08:00:03 | 176.07 | 84 | 176.75 | 2182 |
08:00:04.782191 | 181.00 | 15 | 08:00:04 | 176.07 | 112 | 176.59 | 2734 |
08:00:05.408871 | 175.77 | 400 | 08:00:05 | 176.38 | 212 | 176.50 | 6966 |
08:00:06.007145 | 176.52 | 400 | 08:00:06 | 176.33 | 176 | 176.52 | 8174 |
08:00:06.740159 | 184.00 | 1 | 08:00:06 | 176.33 | 176 | 176.52 | 8174 |
08:00:07.593841 | 175.75 | 400 | 08:00:07 | 176.33 | 276 | 176.67 | 7345 |
08:00:10.310291 | 176.38 | 29 | 08:00:10 | 176.36 | 695 | 176.38 | 20698 |
08:00:10.550535 | 175.86 | 400 | 08:00:10 | 176.36 | 695 | 176.38 | 20698 |
08:00:10.761790 | 175.94 | 400 | 08:00:10 | 176.36 | 695 | 176.38 | 20698 |
08:00:12.046660 | 176.15 | 400 | 08:00:12 | 176.48 | 104 | 176.59 | 4040 |
08:00:12.897624 | 176.62 | 400 | 08:00:12 | 176.48 | 104 | 176.59 | 4040 |
08:00:13.838193 | 176.51 | 25 | 08:00:13 | 176.48 | 165 | 176.38 | 6035 |
08:00:15.125509 | 176.17 | 400 | 08:00:15 | 176.35 | 119 | 176.38 | 1530 |
08:00:16.727077 | 176.48 | 400 | 08:00:16 | 176.35 | 133 | 176.38 | 3710 |
08:00:18.813886 | 176.68 | 400 | 08:00:18 | 176.35 | 84 | 176.38 | 1880 |
08:00:22.180535 | 176.05 | 400 | 08:00:22 | 176.35 | 133 | 176.38 | 1710 |
08:00:25.125634 | 176.16 | 400 | 08:00:25 | 176.35 | 122 | 176.38 | 3929 |
08:00:26.117889 | 176.33 | 1 | 08:00:26 | 176.35 | 300 | 176.37 | 6952 |
08:00:26.184839 | 176.52 | 400 | 08:00:26 | 176.35 | 300 | 176.37 | 6952 |
08:00:26.185102 | 176.41 | 25 | 08:00:26 | 176.35 | 300 | 176.37 | 6952 |
Using ON
for matching column value
The tables in the above example are just about one stock; in reality the same
table covers many stocks, and you want the results not to get mixed between
them. This is what the ON
clause is for -- you can point out the key (ticker)
column and get results separate for each key.
Here's the trades table expanded to include two stocks, and a new symbol
column:
timestamp | symbol | price | size |
---|---|---|---|
08:00:00.007168 | AAPL | 176.91 | 400 |
08:00:00.834205 | AAPL | 175.93 | 400 |
08:00:00.988111 | AAPL | 176.47 | 100 |
08:00:01.199577 | AAPL | 175.46 | 400 |
08:00:01.495172 | AAPL | 177.95 | 400 |
08:00:01.538683 | GOOG | 175.82 | 400 |
08:00:01.555565 | AAPL | 176.33 | 25 |
08:00:02.006636 | GOOG | 150.0 | 10 |
08:00:02.039451 | AAPL | 175.36 | 400 |
08:00:02.460454 | GOOG | 175.45 | 400 |
08:00:03.012909 | GOOG | 175.5 | 1 |
08:00:03.494927 | GOOG | 185.0 | 5 |
08:00:03.524212 | AAPL | 175.48 | 400 |
08:00:04.648333 | AAPL | 175.66 | 400 |
08:00:04.943421 | GOOG | 175.48 | 400 |
08:00:05.884890 | AAPL | 176.54 | 28 |
08:00:05.961856 | GOOG | 175.66 | 400 |
08:00:06.589806 | GOOG | 175.65 | 400 |
08:00:06.740159 | AAPL | 184.0 | 1 |
08:00:07.342978 | GOOG | 176.55 | 400 |
08:00:07.345877 | AAPL | 176.73 | 400 |
08:00:10.419065 | AAPL | 176.41 | 400 |
08:00:11.636237 | AAPL | 176.69 | 400 |
08:00:11.683078 | GOOG | 176.67 | 400 |
08:00:13.650868 | AAPL | 176.52 | 124 |
08:00:13.650880 | AAPL | 176.59 | 124 |
08:00:14.055762 | AAPL | 176.66 | 400 |
08:00:14.083022 | GOOG | 176.81 | 400 |
08:00:15.088091 | GOOG | 176.52 | 400 |
08:00:15.125494 | AAPL | 176.12 | 400 |
08:00:15.147691 | GOOG | 176.54 | 400 |
Order book, similarly extended with the symbol
column:
timestamp | symbol | bid_price | bid_size | ask_price | ask_size |
---|---|---|---|---|---|
08:00:00 | AAPL | 176.47 | 5542 | 176.82 | 13054 |
08:00:01 | GOOG | 130.32 | 7516 | 130.9 | 25652 |
08:00:01 | AAPL | 176.33 | 4744 | 176.6 | 8404 |
08:00:02 | GOOG | 130.59 | 9046 | 130.68 | 9264 |
08:00:02 | AAPL | 176.07 | 136 | 176.76 | 4946 |
08:00:03 | GOOG | 130.34 | 4086 | 130.82 | 12676 |
08:00:03 | AAPL | 176.07 | 84 | 176.75 | 2182 |
08:00:04 | GOOG | 130.29 | 350 | 130.79 | 8780 |
08:00:04 | AAPL | 176.07 | 112 | 176.59 | 2734 |
08:00:05 | GOOG | 130.29 | 182 | 130.68 | 6060 |
08:00:05 | AAPL | 176.38 | 212 | 176.5 | 6966 |
08:00:06 | GOOG | 130.48 | 394 | 130.65 | 6828 |
08:00:06 | AAPL | 176.33 | 176 | 176.52 | 8174 |
08:00:07 | GOOG | 130.52 | 366 | 130.61 | 21260 |
08:00:07 | AAPL | 176.33 | 276 | 176.67 | 7345 |
08:00:08 | GOOG | 130.48 | 480 | 130.76 | 13032 |
08:00:08 | AAPL | 176.33 | 48 | 176.67 | 1600 |
08:00:09 | GOOG | 130.48 | 216 | 130.74 | 6458 |
08:00:09 | AAPL | 176.35 | 66 | 176.67 | 2400 |
08:00:10 | GOOG | 130.48 | 72 | 130.74 | 2400 |
08:00:10 | AAPL | 176.36 | 695 | 176.38 | 20698 |
08:00:11 | GOOG | 130.51 | 1236 | 130.52 | 26596 |
08:00:11 | AAPL | 176.35 | 98 | 176.59 | 2800 |
08:00:12 | GOOG | 130.5 | 378 | 130.68 | 22000 |
08:00:12 | AAPL | 176.48 | 104 | 176.59 | 4040 |
08:00:13 | GOOG | 130.6 | 174 | 130.68 | 5200 |
08:00:13 | AAPL | 176.48 | 165 | 176.38 | 6035 |
08:00:14 | GOOG | 130.6 | 138 | 130.62 | 8616 |
08:00:14 | AAPL | 176.35 | 56 | 176.38 | 720 |
08:00:15 | GOOG | 130.6 | 394 | 130.52 | 9374 |
And here's the ASOF JOIN query with the ON
clause added:
SELECT t.timestamp, t.symbol, price, size, bid_price, bid_size, ask_price, ask_size
FROM trades t ASOF JOIN order_book ON (symbol);
Result:
timestamp | symbol | price | size | bid_price | bid_size | ask_price | ask_size |
---|---|---|---|---|---|---|---|
08:00:00.007168 | AAPL | 176.91 | 400 | 176.47 | 5542 | 176.82 | 13054 |
08:00:00.834205 | AAPL | 175.93 | 400 | 176.47 | 5542 | 176.82 | 13054 |
08:00:00.988111 | AAPL | 176.47 | 100 | 176.47 | 5542 | 176.82 | 13054 |
08:00:01.199577 | AAPL | 175.46 | 400 | 176.33 | 4744 | 176.60 | 8404 |
08:00:01.495172 | AAPL | 177.95 | 400 | 176.33 | 4744 | 176.60 | 8404 |
08:00:01.538683 | GOOG | 175.82 | 400 | 130.32 | 7516 | 130.90 | 25652 |
08:00:01.555565 | AAPL | 176.33 | 25 | 176.33 | 4744 | 176.60 | 8404 |
08:00:02.006636 | GOOG | 150.00 | 10 | 130.59 | 9046 | 130.68 | 9264 |
08:00:02.039451 | AAPL | 175.36 | 400 | 176.07 | 136 | 176.76 | 4946 |
08:00:02.460454 | GOOG | 175.45 | 400 | 130.59 | 9046 | 130.68 | 9264 |
08:00:03.012909 | GOOG | 175.50 | 1 | 130.34 | 4086 | 130.82 | 12676 |
08:00:03.494927 | GOOG | 185.00 | 5 | 130.34 | 4086 | 130.82 | 12676 |
08:00:03.524212 | AAPL | 175.48 | 400 | 176.07 | 84 | 176.75 | 2182 |
08:00:04.648333 | AAPL | 175.66 | 400 | 176.07 | 112 | 176.59 | 2734 |
08:00:04.943421 | GOOG | 175.48 | 400 | 130.29 | 350 | 130.79 | 8780 |
08:00:05.884890 | AAPL | 176.54 | 28 | 176.38 | 212 | 176.50 | 6966 |
08:00:05.961856 | GOOG | 175.66 | 400 | 130.29 | 182 | 130.68 | 6060 |
08:00:06.589806 | GOOG | 175.65 | 400 | 130.48 | 394 | 130.65 | 6828 |
08:00:06.740159 | AAPL | 184.00 | 1 | 176.33 | 176 | 176.52 | 8174 |
08:00:07.342978 | GOOG | 176.55 | 400 | 130.52 | 366 | 130.61 | 21260 |
08:00:07.345877 | AAPL | 176.73 | 400 | 176.33 | 276 | 176.67 | 7345 |
08:00:10.419065 | AAPL | 176.41 | 400 | 176.36 | 695 | 176.38 | 20698 |
08:00:11.636237 | AAPL | 176.69 | 400 | 176.35 | 98 | 176.59 | 2800 |
08:00:11.683078 | GOOG | 176.67 | 400 | 130.51 | 1236 | 130.52 | 26596 |
08:00:13.650868 | AAPL | 176.52 | 124 | 176.48 | 165 | 176.38 | 6035 |
08:00:13.650880 | AAPL | 176.59 | 124 | 176.48 | 165 | 176.38 | 6035 |
08:00:14.055762 | AAPL | 176.66 | 400 | 176.35 | 56 | 176.38 | 720 |
08:00:14.083022 | GOOG | 176.81 | 400 | 130.60 | 138 | 130.62 | 8616 |
08:00:15.088091 | GOOG | 176.52 | 400 | 130.60 | 394 | 130.52 | 9374 |
08:00:15.125494 | AAPL | 176.12 | 400 | 176.35 | 56 | 176.38 | 720 |
08:00:15.147691 | GOOG | 176.54 | 400 | 130.60 | 394 | 130.52 | 9374 |
Timestamp considerations
ASOF
join can be performed only on tables or result sets that are ordered by
time. When a table is created with a
designated timestamp the order of records
is enforced and the timestamp column name is in the table metadata. ASOF
join
uses this timestamp column from metadata.
ASOF
join does not check timestamp order, if data is not in chronological
order, the join result is non-deterministic.
In case tables do not have a designated timestamp column, but data is in chronological order, timestamp columns can be specified at runtime:
SELECT *
FROM (a timestamp(ts))
ASOF JOIN (b timestamp (ts));
SPLICE JOIN
Want to join all records from both tables?
SPLICE JOIN
is a full ASOF JOIN
.
Read the JOIN reference for more information on SPLICE JOIN.