Как объединить таблицу с последним значением из таблицы временных рядов
У меня есть две таблицы в Postgres.
CREATE TABLE data (
name VARCHAR(10) PRIMARY KEY
);
CREATE TABLE timeseries (
name VARCHAR(10) NOT NULL REFERENCES data(name),
time TIMESTAMP NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (name, time)
);
Как я могу соединить таблицу временных рядов с таблицей данных таким образом, чтобы получить столбец value с последней меткой времени
@Luuk Ну, max(value) не обязательно самый последний.
@Stefanov.sm; НЕТ? Почему эта ошибка dbfiddle выдает?: dbfiddle.uk/nR8yHa-S
@Luuk, как говорится в сообщении об ошибке, потому что value не агрегируется
@Stefanov.sm: Я неправильно прочитал твой комментарий... (извините)
Ответы 3
Вы можете извлечь последние записи таблицы timeseries для каждой name в CTE, используя с индивидуальным порядком, а затем объединиться с таблицей data (предположим, что в ней больше столбцов, а приведенное выше — всего лишь иллюстрация).
with t as
(
select distinct on (name) *
from timeseries order by name, time desc
)
select * -- your expression list here
from t join data using (name);
Или, если вам не нужны дополнительные атрибуты таблицы data, просто
select distinct on (name) *
from timeseries order by name, time desc
Основной прием для такого рода задач — использование CTE с оконной функцией ROW_NUMBER:
WITH d AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC) rn
FROM timeseries
) SELECT * FROM d WHERE rn = 1;
В CTE мы получаем все строки, пронумерованные в соответствии с их меткой времени в порядке DESC, поэтому последняя строка получает номер строки = 1, после этого мы фильтруем результат CTE для строк с номером 1.
Проверьте решение здесь
Подзапрос находит максимальное значение, и затем вы можете его выбрать:
SELECT *
FROM timeseries
WHERE (name,time) IN (
SELECT t1.name,max(t1.time)
FROM timeseries t1
GROUP BY name);
см.: DBFIDDLE
Зачем вам (думаете, вам нужно) присоединиться к
data? РазвеSELECT name, MAX(time) as time, MAX(value) as value FROM timeseries GROUP BY nameнедостаточно?