Как пересечь историю при соединении таблиц?

Question

[SergeySafaryanc]

Есть две таблицы:

Table1(id, param1, param2, date_from, date_to):
(1, 'z', 55, '01.05.2010 12:30:20', '17.05.2010 13:10:14'),
(1, 'c', null, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

Table2(id, param3, date_from, date_to):
(1, 15, '01.04.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 35, '02.05.2010 13:10:15', '01.01.9999 00:00:00');
Необходимо чтобы при объединении 2х таблиц были поля содержащие историю изменения каждого параметра. То есть:

Select(id, param1, param2, param3, date_from, date_to):
(1, null, null, 10, '01.04.2010 12:30:20', '01.05.2010 12:30:19'),
(1, 'z', 55, 15, '01.05.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 'z', 55, 35, '02.05.2010 13:10:15', '17.05.2010 13:10:14'),
(1, 'c', null, 35, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, 35, '18.01.2010 04:13:16', '01.01.9999 00:00:00');
Там где записи из двух таблиц не пересекались во времени(или часть времени) должны выводится null.

Пока что я сделал:

SELECT a.id, param1, param2, param3, a.dfrom as afrom, a.dto as ato, b.dfrom as bfrom, b.dto as bto
FROM Table1 a LEFT JOIN Table2 b ON a.id = b.id
UNION
SELECT id, null as param1, null as param2, param3, null as afrom, null as bfrom, dfrom, dto
FROM Table2
И дальше не понимаю как действовать...

Answer 1

[Rsa97]

Поскольку временны́е интервалы у вас не совпадают, то задачу гораздо проще решить на клиенте, а не в MySQL.
Запрос, теоретически, тоже можно составить, но он будет монструозным. Там будет UNION нескольких (на первый взгляд, шести) подзапросов, каждый из которых JOIN'ом выбирает данные в зависимости от перекрытия временны́х интервалов строк из обоих таблиц.
Ну и дату надо перевести в DATETIME, иначе ещё добавятся расходы на постоянную конвертацию.

Comments

[SergeySafaryanc]

Здравствуйте, решил последовать вашему совету.

WITH dates_from AS (
SELECT id, date_from FROM Table1
UNION
SELECT id, date_to + interval '1 second' FROM Table1
UNION
SELECT id, date_from FROM Table2
UNION
SELECT id, date_to + interval '1 second' FROM Table2
),
dates_to AS (
SELECT id, date_to FROM Table1
UNION
SELECT id, date_from - interval '1 second' FROM Table1
UNION
SELECT id, date_to FROM Table2
UNION
SELECT id, date_from - interval '1 second' FROM Table2
),
ranges AS (
SELECT df.id, date_from, MIN(date_to) AS date_to
FROM dates_from df
JOIN dates_to dt ON dt.id = df.id AND dt.date_to > df.date_from
GROUP BY df.id, date_from
)
SELECT r.id,
t1.param1, t1.param2, t2.param3,
r.date_from, r.date_to
FROM ranges r
LEFT JOIN Table1 t1 ON t1.id = r.id AND t1.date_from <= r.date_to and t1.date_to >= r.date_from
LEFT JOIN Table2 t2 ON t2.id = r.id AND t2.date_from <= r.date_to and t2.date_to >= r.date_from
ORDER BY r.id, r.date_from

Но всё равно что то не выходит(

[Rsa97]

SergeySafaryanc, Потому что вариантов гораздо больше. Интервалы могут частично перекрываться, полностью включаться один в другой, может быть интервал, который есть в одной таблице, но отсутствует в другой. И вам придётся разбивать всё на подинтервалы.
Вот здесь каждая пунктирная линия - отдельный вариант взаимодействия интервалов. Итого девять вариантов, для каждого из которых надо написать своё условие разбиения.