There are at least two approaches here, one that is unscalable using pure Standard SQL, and a second scalable solution that utilizes a helper function created with BigQuery's JavaScript UDF functionality.
Unscalable Solution
I had exactly this same problem with BigQuery using tick data organized much like yours is. You can do an inequality join in BigQuery, but this will not scale for even a few months worth of US equities data consisting of a few hundred names or more. In other words you might think you could try:
-- Example level 1 quotes table.
WITH l1_quotes AS (
SELECT TIMESTAMP('2020-01-09T14:30:00.023') as time, 'XYZ' as sym, 16.22 as bid, 16.25 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.023') as time, 'XYZ' as sym, 16.21 as bid, 16.27 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.030') as time, 'XYZ' as sym, 16.20 as bid, 16.28 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'XYZ' as sym, 16.22 as bid, 16.26 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.048') as time, 'XYZ' as sym, 16.23 as bid, 16.28 as ask
),
-- Example trades table.
trades AS (
SELECT TIMESTAMP('2020-01-9T14:30:00.023') as time, 'MMM' as sym, 16.23 as price, 75 as quantity
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'MMM' as sym, 16.24 as price, 50 as quantity
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'MMM' as sym, 16.25 as price, 100 as quantity
),
-- Get all level 1 quotes <= each trade for each day using an inequality join.
inequality_join as (
SELECT
t.time as time,
t.sym,
t.price,
t.quantity,
l.time as l1_time,
l.sym as l1_sym,
l.bid,
l.ask,
l.ask - l.bid as spread
FROM l1_quotes as l
JOIN trades as t
ON EXTRACT(DATE FROM l.time) = EXTRACT(DATE FROM t.time)
AND l.time <= t.time
)
-- Use DISTINCT and LAST_VALUE to get the latest l1 quote asof each trade.
SELECT DISTINCT
time, sym, price, quantity,
LAST_VALUE(bid) OVER (latest_asof_time) as bid,
LAST_VALUE(ask) OVER (latest_asof_time) as ask,
LAST_VALUE(spread) OVER (latest_asof_time) as spread
FROM inequality_join
WINDOW latest_asof_time AS (
PARTITION BY time, sym, l1_sym
ORDER BY l1_time
ROWS between unbounded preceding and unbounded following
);
This will solve your example problem, but it will not scale in practice. Notice in the above that the join hinges on having one equality join condtion by date and one inequality join condition by l1_quotes.time <= trades.time. The inequality portion will ultimately generate all l1_quotes entries whose timestamp is at or before the timestamp of each trade entry. Although this is not quite a full CROSS JOIN, and although it gets partitioned by date thanks to the equality join piece, it becomes a very large join in practice. For the US equity database I was working with, BigQuery was unable to do the ASOF JOIN using this technique even after letting it run for many hours. You can play around with shortening the time window of the equality condition, e.g. EXTRACT an hour, minute, etc., but the smaller you make that window the more you run the risk of not getting a prior level 1 quote for some entries, particularly for illiquid symbols.
Scalable Solution
So how do you ultimately solve this in a scalable way in BigQuery? Well, one idiomatic approach in BigQuery is to leverage one or more helper JavaScript UDF's when Standard SQL cannot do what you need.
Here is a JavaScript UDF that I wrote to solve this problem. It will generate an array of tuples of X,Y timestamps representing X ASOF Y.
CREATE TEMPORARY FUNCTION asof_join(x ARRAY<TIMESTAMP>, y ARRAY<TIMESTAMP>) RETURNS ARRAY<STRUCT<x TIMESTAMP, y TIMESTAMP>> LANGUAGE js AS """
function js_timestamp_sort(a, b) { return a - b }
x.sort(js_timestamp_sort);
y.sort(js_timestamp_sort);
var epoch = new Date(1970, 0, 1);
var results = [];
var j = 0;
for (var i = 0; i < y.length; i++) {
var curr_res = {x: epoch, y: epoch};
for (; j < x.length; j++) {
if (x[j] <= y[i]) {
curr_res.x = x[j];
curr_res.y = y[i];
} else {
break;
}
}
if (curr_res.x !== epoch) {
results.push(curr_res);
j--;
}
}
return results;
""";
The idea is to pull out the l1_quotes timstamps (X) and the trades timestamps (Y) into two separate arrays. Then we use the above function to generate the array of X,Y ASOF tuples. We will then UNNEST this array of tuples to convert them back to an SQL table, and finally we join them back with the l1_quotes and then with the trades. Here is the query that achieves this. In your query editor you will want to paste in the JavaScript UDF above and this query following it:
-- Example level 1 quotes table.
WITH l1_quotes AS (
SELECT TIMESTAMP('2020-01-09T14:30:00.023') as time, 'XYZ' as sym, 16.22 as bid, 16.25 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.023') as time, 'XYZ' as sym, 16.21 as bid, 16.27 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.030') as time, 'XYZ' as sym, 16.20 as bid, 16.28 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'XYZ' as sym, 16.22 as bid, 16.26 as ask
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.048') as time, 'XYZ' as sym, 16.23 as bid, 16.28 as ask
),
-- Example trades table.
trades AS (
SELECT TIMESTAMP('2020-01-9T14:30:00.023') as time, 'MMM' as sym, 16.23 as price, 75 as quantity
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'MMM' as sym, 16.24 as price, 50 as quantity
UNION ALL SELECT TIMESTAMP('2020-01-9T14:30:00.041') as time, 'MMM' as sym, 16.25 as price, 100 as quantity
),
-- Extract distinct l1 quote times (use DISTINCT to reduce the size of the array for each day since we don't need duplicates for the UDF script to work).
distinct_l1_times as (
SELECT DISTINCT time
FROM l1_quotes
),
arrayed_l1_times AS (
SELECT
ARRAY_AGG(time) as times,
EXTRACT(DATE FROM time) as curr_day
FROM trades
GROUP BY curr_day
),
-- Do the same for trade times.
distinct_trade_times AS (
SELECT DISTINCT time
FROM trades
),
arrayed_trade_times AS (
SELECT
ARRAY_AGG(time) as times,
EXTRACT(DATE FROM time) as curr_day
FROM trades
GROUP BY curr_day
),
-- Use the handy asof_join JavaScript UDF created above.
asof_l1_trade_time_tuples AS (
SELECT
asof_join(arrayed_l1_times.times, arrayed_trade_times.times) as asof_tuples,
arrayed_l1_times.curr_day as curr_day
FROM arrayed_l1_times
JOIN arrayed_trade_times
USING (curr_day)
),
-- UNNEST the array of l1 quote/trade time asof tuples. Date grouping is no longer needed and dropped here.
unnested_times AS (
SELECT
a.x as l1_time,
a.y as trade_time
FROM
asof_l1_trade_time_tuples , UNNEST(asof_l1_trade_time_tuples.asof_tuples) as a
)
-- Join back the l1 quote and trade information for the final result. As before, use DISTINCT and LAST_VALUE to
-- eliminate duplicates that arise from repeated timestamps.
SELECT DISTINCT
u.trade_time as time,
t.price as price,
t.quantity as quantity,
LAST_VALUE(l.bid) OVER (latest_asof_time) as bid,
LAST_VALUE(l.ask) OVER (latest_asof_time) as ask,
LAST_VALUE(l.ask - l.bid) OVER (latest_asof_time) as spread,
FROM unnested_times as u
JOIN l1_quotes as l
ON u.l1_time = l.time
JOIN trades as t
ON u.trade_time = t.time
WINDOW latest_asof_time AS (
PARTITION BY t.time, t.sym, l.sym
ORDER BY l.time
ROWS between unbounded preceding and unbounded following
)
The above query using the JavaScript UDF runs in a few minutes on my database as opposed to never finishing after many hours using the unscalable approach. You can even store the asof_join JavaScript UDF as a permanent UDF in your dataset for use in other contexts.
I agree that Google should consider implementing an ASOF JOIN, particularly since timeseries analysis using distributed, columnar OLAP's like BigQuery is becoming much more prevalent. While it's achievable in BigQuery with the approach described here, it would be a lot simpler to do this with a single SELECT statement and a built-in ASOF JOIN construct.
