SQL. Запрос на вывод самых актуальных значений

Question

Имеются две таблицы (Версия сервера: 10.6.16-MariaDB-0ubuntu0.22.04.1 - Ubuntu 22.04) со следующим содержимым:

Таблица sensors

sensor_id	sensor_adress
1	03173311a0ff
2	xxxxxxxxxxxx

...и таблица measurements

measurement_id	sensor_id	temperature_value	measurement_time
1	1	18.567	2024-03-10 19:00:30
2	2	19.123	2024-03-10 19:00:40
3	1	20.0	2024-03-10 20:00:30
4	2	22.5	2024-03-10 20:00:40

Задача: Вывести самую актуальную температуру (temperature_value) по всем датчикам и адрес сенсора (sensor_adress), т.е. что-то в этом роде:

sensor_adress	temperature_value
03173311a0ff	20.0
xxxxxxxxxxxx	22.5

Пробовал составить SQL-запрос, получилось вот что:

SELECT `sensors`.`sensor_adress`, `measurements`.`temperature_value`
FROM `measurements` INNER JOIN `sensors`
ON `sensors`.`sensor_id` = `measurements`.`sensor_id`
WHERE `measurements`.`measurement_time` = (SELECT MAX(`measurement_time`) FROM `measurements`)
ORDER BY `sensors`.`sensor_id` ASC;

Как мне улучшить подзапрос

SELECT MAX(`measurement_time`) FROM `measurements`

чтобы была выборка не по максимальному TIMESTAMP, а именно по самому актуальному для каждого датчика?

Answer 1

Для решения подобных задач обычно имеются следующие варианты:

использование производной таблицы/общего табличного выражения с группировкой для поиска максимального значения для сенсора, и джойн этого добра на саму таблицу данных. Примерно так:
```
SELECT s.address, d.temperature_value
FROM sensors AS s 
INNER JOIN (
   SELECT sensor_id, max(id) as id
   FROM measurements
   GROUP BY sensor_id
) AS m ON (m.sensor_id = s.id)
INNER JOIN measurements AS d ON (d.id = m.id)

# ---- либо аналогичный CTE
WITH m AS (
    SELECT sensor_id, MAX(id) AS id
    ....
)
SELECT 
FROM sensors AS s 
INNER JOIN m ....
```
тип джойнов тут будет определять, что первично, список имеющихся данных или сенсоров.

Оконные функции и нумерация строк

WITH data AS (
   SELECT sensor_id
       , temperature_value
       , ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY id DESC) AS rn
   FROM measurements AS m
) 
SELECT s.address, data.temperature_value
FROM data 
INNER JOIN sensors AS s ON (s.id = data.sensor_id)
WHERE rn = 1

Коррелирующий подзапрос,который возвращает единственное значение температуры для сенсора:

SELECT s.address
    , (SELECT temperature_value
       FROM measurements AS m
       WHERE m.sensor_id = s.id
       ORDER BY id DESC 
       LIMIT 1) AS t
FROM sensors AS s

Если же кроме одного значения надо вытащить и другие данные из строк, то подзапрос уходит в джойн

 SELECT s.address, m.temperature_value
 FROM sensors AS s 
 LEFT JOIN measurements AS m ON (
       m.id = (SELECT MAX(id) 
               FROM measurements AS m 
               WHERE m.sensor_id = s.id)
 )

Для вашего случая и желаемого результата, видимо самым простым будет вариант под номером 3.

БЛОГ НА HUSL

SQL. Запрос на вывод самых актуальных значений

Ответы (1 шт):