Time Series Processing with Streaming SQL
ASOF joins match each row from one stream with the most recent row from another stream that has a timestamp at or before the first row's timestamp. This is critical for financial data (matching trades with the latest quote), IoT (matching readings with the latest calibration), and any use case where you need "the value as of this time."
How ASOF Joins Work
Trade stream: T1(10:01, $100) T2(10:03, $105) T3(10:07, $98)
Quote stream: Q1(10:00, bid=99) Q2(10:02, bid=104) Q3(10:05, bid=97)
ASOF join result:
T1(10:01) matched with Q1(10:00) — latest quote at or before 10:01
T2(10:03) matched with Q2(10:02) — latest quote at or before 10:03
T3(10:07) matched with Q3(10:05) — latest quote at or before 10:07
ASOF Join in RisingWave
CREATE MATERIALIZED VIEW trades_with_quotes AS
SELECT t.trade_id, t.symbol, t.price as trade_price, t.trade_time,
q.bid, q.ask, t.price - q.bid as spread
FROM trades t
ASOF JOIN quotes q ON t.symbol = q.symbol AND t.trade_time >= q.quote_time;
Use Cases
| Use Case | Left Stream | Right Stream | Match |
| Trading | Trades | Quotes | Latest quote at trade time |
| IoT | Sensor readings | Calibration events | Latest calibration |
| Currency | Transactions | Exchange rates | Rate at transaction time |
| Inventory | Orders | Price changes | Price when ordered |
Frequently Asked Questions
What is the difference between ASOF join and temporal join?
ASOF join matches by nearest timestamp (<=). Temporal join matches by a versioned table's validity period. ASOF is simpler and more common. Both are supported in RisingWave and Flink.
Do all streaming databases support ASOF joins?
No. RisingWave and Flink support ASOF joins. ksqlDB does not. This is a significant differentiator for financial and IoT workloads.
