ASOF JOIN in ClickHouse - time-series analytics of stock market data - example tables and data

asof.sql

CREATE TABLE quotes
(
    symbol String,
    time DateTime,
    price Decimal(9, 3)
)
ENGINE = MergeTree
ORDER BY (symbol, time);


INSERT INTO quotes
SELECT 
    symbol,
    time,
    randUniform(20.5, 10):: Decimal (9, 3) as price
FROM (
    SELECT
        toDateTime((toInt32(toDateTime('2023-03-01 11:00:00')) + number * 10)) AS time,
        arrayJoin(['ABC', 'DEF', 'EFG']) as symbol
    FROM numbers(10))
ORDER BY symbol, time;


CREATE TABLE trades
(
    symbol String,
    time DateTime,
    volume UInt32
)
ENGINE = MergeTree
ORDER BY (symbol, time);


INSERT INTO trades
SELECT
    symbol,
    time,
    rand() % 1000 as volume
FROM (
    SELECT
       toDateTime((toInt32(toDateTime('2023-03-01 11:00:00')) + rand() % ((number + 1) * 10))) AS time,
       arrayJoin(['ABC', 'DEF', 'EFG']) as symbol
    FROM numbers(10))
ORDER BY symbol, time;


SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time;