Объединить результаты таблицы в столбцы

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

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

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

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);
INSERT INTO table2(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:05:00', 42);

Можно ли объединить все "известные данные в определенное время" из всех таблиц в одном запросе? Что-то вроде:

SELECT update_time, t1_val, t2_val
FROM combined_output
WHERE start_time = '2004-10-19 08:00:00'
AND end_time = '2004-10-19 12:00:00'

Который дал бы результат:

time                       t1_val    t2_val
'2004-10-19 09:00:00'      1.23      null
'2004-10-19 10:03:00'      1.23      23
'2004-10-19 10:03:30'      1.23      null
'2004-10-19 10:05:00'      1.25      42

Небольшое объяснение:

В 09:00:00 мы знали, что table1 имеет значение 1,23. Никакое значение не присутствовало в table2, таким образом, значение из этого должно быть нулевым.

В 10:03:00 table2 получил 23 добавленных. Значение 1.23 в таблице1 по-прежнему является последним известным значением из таблицы1, поэтому оно все равно должно присутствовать в выходных данных.

10:03:30 как указано выше.

10:05:00 и table1, и table2 получили новые значения, но запрос возвращает только одну строку в выводе, содержащую оба новых значения в t1_val и t2_val.

На самом деле не критично отфильтровывать возможные значения до запрошенного временного диапазона. Если для table2 было бы установлено значение в 08:59:00, то это не повредит, если это значение будет показано в t2_val в первой строке примера, даже если оно не оптимально.

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

Источник

3 ответа

Решение

Я нашел решение, сочетающее функцию с выбором.

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

DROP FUNCTION last_known_values(timestamp without time zone,integer,text);
CREATE OR REPLACE FUNCTION public.last_known_values(
    IN time_to_check timestamp without time zone,
    IN id1 integer,
    IN id2 text)
  RETURNS TABLE(checked_time timestamp without time zone, id1 integer, id2 text, t1_val numeric, t2_val int) AS
$BODY$

SELECT time_to_check AS time, id1, id2,
(
  SELECT table1.val AS t1_val from table1
  WHERE $1 >= table1.update_time
  AND table1.id1 = $2
  AND table1.id2 = $3
  ORDER BY table1.update_time DESC
  LIMIT 1
),
(
  SELECT table2.val AS t2_val from table2
  WHERE $1 >= table2.update_time
  AND table2.id1 = $2
  AND table2.id2 = $3
  ORDER BY table2.update_time DESC
  LIMIT 1
)

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

Затем я использую эту функцию с любым диапазоном меток времени, фильтруя таким образом, чтобы выбирались только метки времени, присутствующие в таблице1 или таблице2 (..tableN):

SELECT last_known_values.* FROM (
    SELECT DISTINCT update_time 
    FROM (
        SELECT update_time 
        FROM table1
        WHERE update_time BETWEEN '2004-10-19 08:00:00' AND '2004-10-19 12:00:00'
        AND table1.id1 = 1
        AND table1.id2 = 'ident 1'

        UNION
        SELECT update_time 
        FROM table2
        WHERE update_time BETWEEN '2004-10-19 08:00:00' AND '2004-10-19 12:00:00'
        AND table2.id1 = 1
        AND table2.id2 = 'ident 1'
    ) t
    ORDER BY update_time ASC 
) times_to_fetch, last_known_values(times_to_fetch.update_time, 1, 'ident 1'::text);

Дает результат:

"2004-10-19 09:00:00"    1    "ident 1"    1.23    (null)
"2004-10-19 10:03:00"    1    "ident 1"    1.23    23
"2004-10-19 10:03:30"    1    "ident 1"    1.23    (null)
"2004-10-19 10:05:00"    1    "ident 1"    1.25    42
Источник

Я бы порекомендовал создать представление, которое объединяет все данные, тогда вы можете запросить представление по мере необходимости.

Создать вид:

create view combined_output as select * from table1 union all 
                               select * from table2 union all 
                               ...
                               select * from tableN;

Запустить запрос:

SELECT update_time, t1_val, t2_val
FROM combined_output
WHERE update_time between '2004-10-19 08:00:00' and '2004-10-19 12:00:00'

Предостережение: я не пробовал ничего из этого.

Источник

Если таблицы связаны с внешним ключом, это можно сделать с помощью оператора соединения.
Из того, как это выглядит из ваших таблиц, нет FK, так что используйте Union. Это предоставит вам много данных, однако.

Источник
Другие вопросы по тегам