Объединить результаты таблицы в столбцы (сводная / перекрестная таблица?)

Question

У меня ~30 таблиц, которые получают "потоковые" данные из внешней системы. Я пытаюсь выяснить, как извлечь из таблиц последние известные значения для определенного момента времени и представить это удобным способом.

Опишем макет таблицы:

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
CREATE TABLE IF NOT EXISTS table1 (
    id1             INT NOT NULL,
    id2             TEXT NOT NULL,
    update_time     TIMESTAMP(6) NOT NULL,
    val             NUMERIC NULL,
    PRIMARY KEY (id1, id2, update_time)
)
;

CREATE TABLE IF NOT EXISTS table2 (
    id1             INT NOT NULL,
    id2             TEXT NOT NULL,
    update_time     TIMESTAMP(6) NOT NULL,
    val             INT NULL,
    PRIMARY KEY (id1, id2, update_time)
)
;

--...tableN(


INSERT INTO table1(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 09:00:00', 1.23);
INSERT INTO table1(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:05:00', 1.25);

INSERT INTO table2(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:03:00', 23);
INSERT INTO table2(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:03:30', null);

Теперь для меня оптимальным способом представления данных будет что-то вроде:

SELECT *
FROM lastknownvalues
WHERE id1 = 1
AND id2 = 'ident 1'
AND time = '2004-10-19 10:04:00'

Который вернул бы одну строку:

time                     id1    id2     table1    table2
'2004-10-19 10:04:00'    1      ident1  1.23      null

В 10:04:00 последнее известное значение в таблице 1 было 1,23 (обновление до 1,25 было произведено позже) в 10:03 таблица 2 была обновлена ​​до нуля, поэтому в результате она должна быть нулевой. Обратите внимание, что типы данных для val отличаются в таблицах.

Я боролся с сводной / кросс-таблицей, так как это выглядит достаточно близко к функциональности, которую я ищу, но я не могу понять, как это сделать, вы можете понять это?:)

Следующий вопрос:

Что если я хотел бы получить все значения в течение интервала, чтобы получить всю объединенную информацию между 2 временными метками? Например, если выбрать с помощью start_timestamp 2004-10-19 09:00:00 и end_timestamp 2004-10-19 10:04:00, получим следующий результат:

time                     id1    id2     table1    table2
'2004-10-19 09:00:00'    1      ident1  1.23      null
'2004-10-19 10:03:00'    1      ident1  1.23      23
'2004-10-19 10:03:30'    1      ident1  1.23      null

Возможный..? (Обратите внимание, что я получил данные в ~30 таблицах, чтобы объединить их с желаемым выводом выше)

Answer 1

Чтобы найти самую последнюю временную метку, сначала используйте оператор сравнения, чтобы исключить будущие временные метки. Затем упорядочите по наименьшему интервалу, вычитая возможных кандидатов на временную метку из желаемой временной метки. Чтобы получить только самый последний результат, ограничьте 1.

Чтобы получить таблицу2 val столбец для отображения со столбцами таблицы 1, вам просто нужно запустить тот же код для таблицы 2 внутри подзапроса.

SELECT update_time AS time, id1, id2, val, (
  SELECT val from table2 
  WHERE '2004-10-19 10:04:00' >= update_time
  ORDER BY '2004-10-19 10:04:00' - update_time
  LIMIT 1
)
FROM table1 WHERE '2004-10-19 10:04:00' >= update_time
ORDER BY '2004-10-19 10:04:00' - update_time
LIMIT 1;