Average sensor readings

tutorial

TL;DR

Run this small SQL dataset in an authorized test streaming catalog and compare the query result with the expected values. Use PGWire for both insertion and recent-data reads. Insert once, allow for the commit cycle and reconcile any uncertain write before repeating it; identifiers alone do not provide deduplication.

Input and resultAverage readings separately for each sensor

These are the actual example inputs and expected aggregates, not throughput measurements. Verify the SQL result before expanding the example.

Prerequisites

Complete the first workflow connection with permission to create tables in a test streaming catalog. Use an unused table name and run the INSERT once.

IoT sensor data

CREATE TABLE main.sensor_readings (
    event_id BIGINT,
    device_id VARCHAR,
    event_time TIMESTAMP,
    temperature DOUBLE
);

INSERT INTO main.sensor_readings VALUES
    (1, 'sensor-a', TIMESTAMP '2026-01-01 12:00:00', 20.0),
    (2, 'sensor-a', TIMESTAMP '2026-01-01 12:00:10', 22.0),
    (3, 'sensor-b', TIMESTAMP '2026-01-01 12:00:00', 18.0);

SELECT device_id, AVG(temperature) AS average_temperature
FROM main.sensor_readings
GROUP BY device_id
ORDER BY device_id;

Expected result: sensor-a averages 21.0; sensor-b averages 18.0. Use a windowed materialized view when you need stored aggregates for each time window.

If the result differs

Wait only by repeating SELECT within a bounded period. Check the selected catalog and any earlier inserts. After a lost connection, preserve the input and reconcile before retrying. See completion rules.