TL;DR
Learn how to use ASOF Joins in RisingWave to match irregular sensor readings with the most recent device status updates. This guide walks through real SQL examples using inner and left ASOF joins—ideal for time-series data like IoT metrics. Perfect for simplifying temporal joins without exact timestamp matches.
Introduction
In stream processing, joining data from two different sources is a common task. Usually, you join data based on exact matches—like matching a User ID in one table to a User ID in another.
But what happens when you deal with time-series data? Often, timestamps don't match exactly. For example, you might have a sensor reading that happened at 08:15, but the system status update happened at 08:00. A standard join looks for an exact match and would fail here. You want to link the reading to the most recent status that happened before it.
This is where the ASOF Join comes in.
An ASOF Join matches rows based on approximate conditions. It finds the most recent row from the right side that matches the criteria relative to the left side. This is perfect for financial data (stock prices) or IoT data (sensor readings).
In this tutorial, we will walk through how to use ASOF Join in RisingWave to link sensor readings with device status updates.
1. Set Up Your Tables and Data
First, make sure you have RisingWave up and running. If you haven't started it yet, check the installation guide.
We need two streams of data.
device_status: This table tracks the health of a device (normal, warning, error). These updates happen on a schedule (e.g., every hour).sensor_readings: This table records temperature data. These readings happen at irregular times (random minutes within the hour).
Run the following SQL queries to create the table and insert the status data:
--Table for device status updates (regularly scheduled every hour)
CREATE TABLE device_status (
device_id INT,
status VARCHAR,
update_time TIMESTAMP
);
-- Insert device status data (batch data)
INSERT INTO device_status (device_id, status, update_time) VALUES
(101, 'normal', '2023-01-01 08:00:00'),
(101, 'warning', '2023-01-01 09:00:00'),
(101, 'error', '2023-01-01 10:00:00'),
(102, 'normal', '2023-01-01 08:00:00'),
(102, 'normal', '2023-01-01 09:00:00'),
(103, 'normal', '2023-01-01 08:00:00'); -- Note: Device 104 has no status data in this table
Next, let's create the sensor reading table and insert some data. Notice that the timestamps here (like 08:15:00) do not exactly match the timestamps in the status table (which are on the hour, 08:00:00).
CREATE TABLE sensor_readings (
reading_id INT,
device_id INT,
temperature FLOAT,
reading_time TIMESTAMP
);
-- Insert sensor reading data
INSERT INTO sensor_readings (reading_id, device_id, temperature, reading_time) VALUES
(1, 101, 36.2, '2023-01-01 08:15:00'),
(2, 101, 38.7, '2023-01-01 09:20:00'),
(3, 101, 41.3, '2023-01-01 10:05:00'),
(4, 102, 22.5, '2023-01-01 08:45:00'),
(5, 102, 23.1, '2023-01-01 09:30:00'),
(6, 103, 18.9, '2023-01-01 11:00:00'),
(7, 104, 25.6, '2023-01-01 09:15:00');
-- Device 104 is included here but has no matching status
2. Perform an Inner ASOF Join
Now, we want to combine these tables. We want to know: "What was the device status immediately before this temperature reading was taken?"
In an ASOF Join, you need two things in your ON clause:
An equality condition (usually matching IDs, like
device_id = device_id).An inequality condition (like
reading_time >= update_time).
Let's try an Inner ASOF Join. This will return only the rows where a match is found. If a sensor reading has no previous status update, it will be dropped.
CREATE MATERIALIZED VIEW inner_asof_join_results AS
SELECT
s.reading_id,
s.device_id,
s.temperature,
s.reading_time,
d.status,
d.update_time AS last_status_update
FROM sensor_readings s
ASOF JOIN device_status d
ON s.device_id = d.device_id
AND s.reading_time >= d.update_time;
Now, let's query the results:
SELECT * FROM inner_asof_join_results ORDER BY reading_id;
Output:
reading_id | device_id | temperature | reading_time | status | last_status_update
------------+-----------+-------------+---------------------+---------+---------------------
1 | 101 | 36.2 | 2023-01-01 08:15:00 | normal | 2023-01-01 08:00:00
2 | 101 | 38.7 | 2023-01-01 09:20:00 | warning | 2023-01-01 09:00:00
3 | 101 | 41.3 | 2023-01-01 10:05:00 | error | 2023-01-01 10:00:00
4 | 102 | 22.5 | 2023-01-01 08:45:00 | normal | 2023-01-01 08:00:00
5 | 102 | 23.1 | 2023-01-01 09:30:00 | normal | 2023-01-01 09:00:00
6 | 103 | 18.9 | 2023-01-01 11:00:00 | normal | 2023-01-01 08:00:00
Notice two things:
Reading #1 at 08:15 was correctly paired with the status from 08:00.
Reading #7 (Device 104) is missing. This is because Device 104 had no status updates in our data, so the Inner Join excluded it.
3. Perform a Left Outer ASOF Join
Sometimes, you want to keep all your data, even if a match isn't found. For example, we still want to see the temperature reading for Device 104, even if we don't know its status.
For this, we use a Left Outer ASOF Join (ASOF LEFT JOIN). It returns all rows from the left table (sensor readings). If there is no matching status, the status columns will just be empty (NULL).
CREATE MATERIALIZED VIEW left_outer_asof_join_results AS
SELECT
s.reading_id,
s.device_id,
s.temperature,
s.reading_time,
d.status,
d.update_time AS last_status_update
FROM sensor_readings s
ASOF LEFT JOIN device_status d
ON s.device_id = d.device_id
AND s.reading_time >= d.update_time;
Let's look at the results:
SELECT * FROM left_outer_asof_join_results ORDER BY reading_id;
Output:
reading_id | device_id | temperature | reading_time | status | last_status_update
------------+-----------+-------------+---------------------+---------+---------------------
1 | 101 | 36.2 | 2023-01-01 08:15:00 | normal | 2023-01-01 08:00:00
2 | 101 | 38.7 | 2023-01-01 09:20:00 | warning | 2023-01-01 09:00:00
3 | 101 | 41.3 | 2023-01-01 10:05:00 | error | 2023-01-01 10:00:00
4 | 102 | 22.5 | 2023-01-01 08:45:00 | normal | 2023-01-01 08:00:00
5 | 102 | 23.1 | 2023-01-01 09:30:00 | normal | 2023-01-01 09:00:00
6 | 103 | 18.9 | 2023-01-01 11:00:00 | normal | 2023-01-01 08:00:00
7 | 104 | 25.6 | 2023-01-01 09:15:00 | NULL | NULL
Now, reading #7 is included. The status and last_status_update are NULL because there was no previous data for Device 104 to link to.
Conclusion
ASOF Joins are a powerful tool in RisingWave for handling time-series data. They allow you to:
Connect events that happen at different times.
Find the "most recent" value relative to a specific event.
Handle missing history using Left Outer joins.
This feature simplifies complex logic that would otherwise require complicated sub-queries or custom code.
Get Started with RisingWave
Try RisingWave Today:
Download the open-sourced version of RisingWave to deploy on your own infrastructure.
Get started quickly with RisingWave Cloud for a fully managed experience.
Talk to Our Experts: Have a complex use case or want to see a personalized demo? Contact us to discuss how RisingWave can address your specific challenges.
Join Our Community: Connect with fellow developers, ask questions, and share your experiences in our vibrant Slack community.
If you’d like to see a personalized demo or discuss how this could work for your use case, please contact our sales team.
