Middle+

There is a system that polls temperature, humidity, and pressure sensors located in rooms and saves the readings in a table. Sensor readings are taken once a day. Not all three types of sensors may be installed in each room, and some sensors may not provide data due to failures. You need to write a query that outputs aggregated information about sensor readings of all types for each room per day. Data structure Field Type Description date timestamp Reading date room_id int Room identifier where the sensor is located sensor_type int Sensor type (1 - temperature sensor, 2 - humidity sensor, 3 - pressure sensor) value int Value received from the sensor Example CREATE TABLE t_sensor_data ( date TIMESTAMP, room_id INT, sensor_type INT, value INT ); INSERT INTO t_sensor_data (date, room_id, sensor_type, value) VALUES ('2023-01-01', 1, 1, 25), ('2023-01-01', 1, 2, 15), ('2023-01-01', 2, 1, 27), ('2023-01-01', 2, 2, 12), ('2023-01-01', 2, 3, 940), ('2023-01-01', 1, 1, 27), ('2023-01-01', 1, 2, 45), ('2023-01-01', 1, 3, 950), ('2023-01-01', 2, 1, 25), ('2023-01-01', 2, 2, 30), ('2023-01-01', 2, 3, 940); SELECT * FROM t_sensor_data;

sobes.tech AI

Answer from AI

To aggregate readings for each room and date with separation by sensor types, it is convenient to use aggregate functions with conditional aggregation (CASE WHEN). This allows displaying temperature, humidity, and pressure values in one row, if available, otherwise NULL.

Example query:

SELECT
  date::date AS date,  -- if only the date without time is needed
  room_id,
  MAX(CASE WHEN sensor_type = 1 THEN value END) AS temperature,
  MAX(CASE WHEN sensor_type = 2 THEN value END) AS humidity,
  MAX(CASE WHEN sensor_type = 3 THEN value END) AS pressure
FROM t_sensor_data
GROUP BY date::date, room_id
ORDER BY date, room_id;

Explanation:

date::date — converts timestamp to date without time to group by day.
MAX(CASE WHEN sensor_type = X THEN value END) — conditionally selects the value for each sensor type. MAX is used to aggregate, and if no data is present, NULL will be returned.
Group by date and room.

This query will show the values of all three sensor types for each room and date, if they were recorded.

Previous Next