全历史连接

Question

目前，我正在试图找出历史化表之间的连接，在其中，我希望同步两个时间线。例如，我有以下两个表：

A
ID      Value   FROM        TO
1       5       01.01.2018  31.03.2018
1       6       31.03.2018  08.04.2018

B       A_FK    Value   FROM        TO
1       1       50      01.02.2018  01.04.2018
2       1       51      04.04.2018  10.04.2018

作为基线，我想取表A和表B的时间线，包括空值，以便我知道，对于这些时间，没有合适的值。所需的结果应该如下所示：

C
Value_A    Value_B   FROM        TO
5          NULL      01.01.2018  01.02.2018
5          50        01.02.2018  31.03.2018
6          50        31.03.2018  01.04.2018
6          NULL      01.04.2018  04.04.2018
6          51        04.04.2018  08.04.2018

你能帮我做这个吗？我开始，但可能无法对错的历史-这里我的尝试：

with a as (SELECT *
 FROM (VALUES (1,5,'01.01.2018','31.03.2018')
         , (1,6,'31.03.2018','08.04.2018')
   ) A (ID, VALUE, FROM, TO)),
b as (
SELECT *
  FROM (VALUES (1,1,50,'01.02.2018','01.04.2018')
             , (2,1,51,'04.04.2018','10.04.2018')
       ) A (ID,A_FK, VALUE, FROM, TO)
)
select 
a.value as value_a,
b.value as value_b,
max(a.from,b.from) as from,
min(a.to,b.to) as to
from a
left outer join b on 
a.id = b.a_fk and
a.from < b.to and
a.to > b.from;

正如你所看到的，它是对齐的，但不是我期望的那样。

谢谢你的帮助。

Answer 1

因此，正如我在评论中所建议的，在我自己的回答中使用该技术，您可以解决您的问题。

这里有一个解决办法。

测试数据：

create table a (
  id integer,
  value integer,
  dtfrom date,
  dtto date
);

create table b(
  id integer,
  a_fk integer,
  value integer,
  dtfrom date,
  dtto date
);

insert into a values 
   (1, 5, '2018-01-01', '2018-03-31'), 
   (1, 6, '2018-03-31', '2018-04-08');
insert into b values 
   (1, 1, 50, '2018-02-01', '2018-04-01'), 
   (2, 1, 51, '2018-04-04', '2018-04-10');

此解决方案的诀窍部分是生成不存在于任何表(如01.01.2018-01.02.2018和01.02.2018-31.03.2018 )中的日期间隔，因此要做到这一点，必须将所有可用日期作为一个表，因此我创建了一个名为timmings的视图，以使其更容易：

create or replace view timmings as
  select a.dtfrom dt from a inner join b on a.id=b.a_fk
  union
  select a.dtto from a inner join b on a.id=b.a_fk
  union
  select b.dtfrom from a inner join b on a.id=b.a_fk
  union
  select b.dtto from a inner join b on a.id=b.a_fk;

在此之后，您需要一个查询来生成所有可用的句点(开始和结束)，因此它将是：

select t1.dt as start,
      (select min(t2.dt) 
         from timmings t2 
        where t2.dt>t1.dt) as dend
 from timmings t1
order by start;

这将导致(与您的样本数据)：

  start          dend
01/01/2018    01/02/2018
01/02/2018    31/03/2018
31/03/2018    01/04/2018
01/04/2018    04/04/2018
04/04/2018    08/04/2018
08/04/2018    10/04/2018
10/04/2018    null

这样，您可以使用它从与句点相交的表a中获取所有可用值：

select a.id, a.value, tm.start, tm.dend
  from (select t1.dt as start,
              (select min(t2.dt) 
                 from timmings t2 
                where t2.dt>t1.dt) as dend
         from timmings t1) tm
      left join a on tm.start >= a.dtfrom and tm.dend <= a.dtto 
 where a.id is not null
 order by tm.start;

其结果是：

id   value    start         end
 1     5    01/01/2018   01/02/2018
 1     5    01/02/2018   31/03/2018
 1     6    31/03/2018   01/04/2018
 1     6    01/04/2018   04/04/2018
 1     6    04/04/2018   08/04/2018

最后，使用LEFT JOIN表对其进行b：

 select x.value as valueA,
        b.value as valueB,
        x.start as "from",
        x.dend as "to"
   from (select a.id, a.value, tm.start, tm.dend
          from (select t1.dt as start,
                      (select min(t2.dt) 
                         from timmings t2 
                        where t2.dt>t1.dt) as dend
                 from timmings t1) tm
              left join a on tm.start >= a.dtfrom and tm.dend <= a.dtto 
         where a.id is not null
        ) x 
      left join b on b.a_fk = x.id
                 and b.dtfrom <= x.start
                 and b.dtto >= x.dend
   order by x.start;

这将给你你想要的结果：

valueA   valueB     start       end
 5        null   01/01/2018  01/02/2018
 5        50     01/02/2018  31/03/2018
 6        50     31/03/2018  01/04/2018
 6        null   01/04/2018  04/04/2018
 6        51     04/04/2018  08/04/2018

请参阅这里的最终解决方案：http://sqlfiddle.com/#!9/36418e/1，它是MySQL，但是由于它都是SQL ANSI，所以在DB2中工作得很好。

Answer 2

约翰·马恩帕写了一篇关于"与日期范围的乐趣“的优秀博客文章。

其次，如果您有机会影响DDL，我建议您仔细查看Db2时态表--它们具有完全的SQL支持( Travel )--查找详细信息这里