Выполнение внешнего объединения для временных таблиц SQL - Как получить каждое временное состояние?

У меня есть 2 таблицы SQL (E1, E2), которые связаны вместе с отношением родитель-ребенок (E1 является родительским). Каждый раз, когда выполняется обновление записи, создается новая запись (временная / историзация).

Я хотел бы получить, используя запрос левого соединения (дочерний элемент не должен существовать), все различные состояния их отношений в течение времени жизни записей

Sample E1:
Id     StartDate     EndDate       Value
----------------------------------------------------
1      2017-11-01    2017-11-05    Initial E1 (Day 1)
1      2017-11-05    2017-11-14    E1 (update Day 5)
1      2017-11-14    9999-12-31    E1 (Update Day 14)

Sample E2:
Id     StartDate     EndDate       Value                 E1_Id
---------------------------------------------------------------
1      2017-11-03    2017-11-12    Initial E2 (Day 3)    1
1      2017-11-12    9999-12-31    E2 (Day 12)           1

Я (наивно) ожидал получить каждое состояние отношения, используя запрос левого соединения, где "ON" объединяет идентификатор, StartDate и EndDate. Однако я не получаю ни одной строки для временного интервала, где ребенок не существует (я ожидаю запись для временного интервала с 1 по 3 ноября).

select *
from e1
left join e2 on  e1.id = e2.E1_Id 
and e1.StartDate < e2.EndDate 
and e1.EndDate >= e2.StartDate


Id  StartDate   EndDate Value                       Id  StartDate   EndDate Value                 E1_Id
-------------------------------------------------------------------------------------------------------------------------
1   2017-11-01  2017-11-05  Initial  E1    (Day 1)  1   2017-11-03  2017-11-12  Initial E2 (Day 3)        1  temporal validity: 3-5 nov
1   2017-11-05  2017-11-14  E1 (update Day 5)          1    2017-11-03  2017-11-12  Initial E2 (Day 3)  1    temporal validity: 5-12 nov
1   2017-11-05  2017-11-14  E1 (update Day 5)          1    2017-11-12  9999-12-31  E2 (Day 12)        1     temporal validity: 12-14 nov
1   2017-11-14  9999-12-31  E1 (Update Day 14)         1    2017-11-12  9999-12-31  E2 (Day 12)        1     temporal validity: from 14 nov


As the first 1 records, I was expecting this extra record
Id  StartDate   EndDate Value                       Id  StartDate   EndDate Value                 E1_Id
-------------------------------------------------------------------------------------------------------------------------
1   2017-11-01  2017-11-03  Initial  E1    (Day 1)  1   <Null>  <NULL>      <NULL>          <NULL>   temporal validity: 1-3 nov.
…..

[Отредактировал ожидаемый конец (был 5 ноября и должен быть 3 ноября)]. Идея состоит в том, чтобы отслеживать в выходном запросе каждое изменение, которое мы имеем в отношении parent-child

У кого-нибудь есть идея правильно получить информацию?

Спасибо Хосе

Источник

1 ответ

Похоже, вы думаете о двух типах детей:

  • Те в E2, которые содержат StartDate записи E1, и
  • Те в E2, которые содержат EndDate записи E1

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

В этом случае вы можете создать два отдельных запроса для этих двух разных случаев, а затем взять объединение результатов:

select    * 
from      e1 
left join e2 
       on e1.id = e2.E1_Id 
      and e1.StartDate between e2.StartDate and e2.EndDate
union
select    * 
from      e1 
left join e2 
       on e1.id = e2.E1_Id 
      and e1.EndDate between e2.StartDate and e2.EndDate
where     e1.EndDate < '9999-12-31'
order by 2, 6;

Последнее условие в особой дате, вероятно, то, что вы хотите, потому что эта дата действительно означает "бесконечность", поэтому она не должна содержаться ни в каком периоде. Но попробуйте также запрос без этого условия, чтобы увидеть, что вы получите от него.

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