Join Multi-temporal Tables

Page Join Temporal Tables deals with the join of N unitemporal tables whose time periods are limited by valid_from and valid_to. In Generate Bitemporal Intervals bitemporal tables with the time dimensions valid and registered are described.
On this page we describe the join of N multi-temporal tables, i.e. instead of, for example, two time dimensions valid and registered, there are any number of M time dimensions. We call here the M time dimensions date_1, date_2, … date_M.

create table  times_date_1 as
select product_id,   date_1_from  as date from product_1
union
select product_id,   date_1_to    as date from product_1
…
select product_id,   date_1_from  as date from product_N
union
select product_id,   date_1_to    as date from product_N;


create table times_date_2 as
select product_id,   date_2_from  as date from product_1
union
select product_id,   date_2_to    as date from product_1
…
select product_id,   date_2_from  as date from product_N
union
select product_id,   date_2 _to   as date from product_N;

…

create table times_date_M as
select product_id,   date_M_from  as date from product_1
union
select product_id,   date_M_to    as date from product_1
…
select product_id,   date_M_from  as date from product_N
union
select product_id,   date_M_to    as date from product_N;


create table time_slices_date_1 as
select * from
(
    select product_id,
        date as date_from,
        lead(date) over(partition by product_id  order by date)
             as date_to
    from times_date_1
) foo
where date_to is not null;
…
create table time_slices_date_M as
select * from
(
    select product_id,
    date as date_from,
    lead(date) over(partition by product_id order by date)
         as date_to
    from times_date_M
) foo
where date_to is not null;


create table product_join as
select a1.product_id, 
    a1.date_from, a1.date_to, … aM.date_from, aM.date_to,
    b1.attr_1, b1.attr_2, … bN.attr_1, bN.attr_2, …
from time_slices_date_1 a1 join time_slices_date_2 a2
        on a1.product_id = a2.product_id
    … join … time_slices_date_M aM
        on a1.product_id = aM.product_id
    left outer join product_1 b1 on a1.product_id = b1.product_id
        and (a1.date_from, a1.date_to)       overlaps
            (b1.date_1_from, b1.date_1_to)
    …
        and (aM.date_from, aM.date_to)       overlaps
            (b1.date_M_from, b1.date_M_to)
    …
    left outer join product_N bN on a1.product_id = bN.product_id
        and (a1.date_from, a1.date_to)       overlaps
            (bN.date_1_from, bN.date_1_to)
    …
        and (aM.date_from, aM.date_to)       overlaps
            (bN.date_M_from, bN.date_M_to)
where coalesce(b1.product_id, b2.product_id, …, bN.product_id) 
    is not null;