Back to questions
Middle+
4
Есть система, которая опрашивает датчики температуры, влажности и давления, расположенные в помещениях и сохраняет значения в таблицу. Показания с датчиков снимаются раз в день. Не в каждом помещении могут быть установлены все три типа датчиков, кроме того из-за сбоев некоторые датчики могут не предоставить информацию. Необходимо написать запрос, который выведет агрегированную информацию о показаниях датчиков всех типов по каждой комнате на каждый день. Структура данных Поле Тип Описание date timestamp Дата сбора показания room_id int Идентификатор комнаты, где расположен датчик sensor_type int Тип датчика (1 - датчик температуры, 2 - датчик влажности, 3 - датчик давления) value int Значение, полученное с датчика Пример 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 ('[phone]', 1, 1, 25), ('[phone]', 1, 2, 15), ('[phone]', 2, 1, 27), ('[phone]', 2, 2, 12), ('[phone]', 2, 3, 940), ('[phone]', 1, 1, 27), ('[phone]', 1, 2, 45), ('[phone]', 1, 3, 950), ('[phone]', 2, 1, 25), ('[phone]', 2, 2, 30), ('[phone]', 2, 3, 940); SELECT * FROM t_sensor_data;
Companies where asked
Core outstaff
Answer from AI
sobes.tech AI
Для агрегирования показаний по каждой комнате и дате с разделением по типам датчиков удобно использовать агрегатные функции с условной агрегацией (CASE WHEN). Это позволит вывести в одной строке значения температуры, влажности и давления, если они есть, иначе NULL.
Пример запроса:
SELECT
date::date AS date, -- если нужно только дата без времени
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;
Объяснение:
date::date— преобразуем timestamp к дате без времени, чтобы группировать по дню.MAX(CASE WHEN sensor_type = X THEN value END)— условно выбираем значение для каждого типа датчика. MAX используется, чтобы агрегировать, при этом если данных нет, будет NULL.- Группируем по дате и комнате.
Такой запрос покажет по каждой комнате и дате значения всех трёх типов датчиков, если они были сняты.