oracle在带有时间戳列的表上跟踪历史记录

我在Oracle 12c数据库中有2个表。表A具有来自应用程序的传入数据，该应用程序具有给定产品的修改日期时间戳，每天我们可能会在table_A中获得大约50,000行。目标是使用表A的数据，并通过使用table_A的数据作为驱动数据集插入最终目标表B (通常有数十亿行)。

仅当给定产品属性的传入数据集发生变化时，才需要在table_B中插入/更新记录。

目的是仅当其属性 (例如state和zip_cd) 发生变化时，才使用有效的时间戳来跟踪给定产品的历史/旅程。从A插入数据后，最终目标表B中的valid_fter和valid_到date字段之间应该有任何重叠的日期或间隙。将table_B视为使用Valid_date，valid_至date间隔缓慢更改尺寸以指示给定产品的更改


寻找一些关于什么是解决这个问题的最佳方法的指导。可以创建一个或多个临时/中间表来实现此目的。

样本数据存在各种各样的问题。Valid_t_t_t_t_t_ 之前的日期，重叠的日期，...这使得很难验证解决方案是否符合您的要求!

无论如何，您应该可以将其用作您正在做的事情的基础:

-合并表B与对a的查询:
-通过使用铅 (dt) 查找每个产品的下一个日期来生成有效的从/到日期
-匹配产品id和有效的日期 = 修改日期

这给出了:

create table table_a ( 
  product_id varchar2(30), state varchar2(30), zip_cd varchar2(30), modified_dt date
);
create table table_b ( 
  sequence_key number, product_id varchar2(30), state varchar2(30), zip_cd varchar2(30), valid_from date, valid_to date, latest_flag varchar2(1)
);

begin
  insert into table_a values ('abc',  'MN', '123', to_timestamp('3/5/2020 12:01:00 AM', 'mm/dd/yyyy hh:mi:ss AM'));
  insert into table_a values ('abc',  'MN', '123', to_timestamp('3/5/2020 6:01:13 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('abc',  'IL', '223', to_timestamp('3/5/2020 7:01:15 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('abc',  'OH', '333', to_timestamp('3/5/2020 6:01:16 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('abc',  'NY', '722', to_timestamp('3/5/2020 4:29:00 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('abc',  'KS', '444', to_timestamp('3/5/2020 4:31:41 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('bbc',  'MN', '123', to_timestamp('3/19/2020 2:47:08 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('bbc',  'IL', '223', to_timestamp('3/19/2020 2:50:37 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('ccb',  'MN', '123', to_timestamp('3/19/2020 2:56:24 PM', 'mm/dd/yyyy hh:mi:ss PM'));
  insert into table_a values ('dbd',  'KS', '444', to_timestamp('3/19/2020 12:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'));
  commit;
end;
/

create or replace procedure load_data as
begin

  merge into table_b b
  using ( 
    select a.*, 
           lead ( modified_dt, 1, date'2099-12-31' ) over ( 
             partition by product_id
             order by modified_dt
           ) next_date
    from   table_a a
  ) a
  on   ( b.product_id = a.product_id and b.valid_from = a.modified_dt )
  when not matched then 
    insert  ( b.product_id, b.state, b.zip_cd, b.valid_from, b.valid_to, b.latest_flag )
    values  ( a.product_id, a.state, a.zip_cd, a.modified_dt, a.next_date, case
      when next_date = date'2099-12-31' then 'Y' else 'N' end
    )
  when matched then
    update set b.valid_to = a.next_date, b.latest_flag = 'N'
    where  a.next_date <> date'2099-12-31';
    
end load_data;
/

exec load_data();
  
select * from table_b
order  by product_id, valid_from;

SEQUENCE_KEY    PRODUCT_ID    STATE    ZIP_CD    VALID_FROM              VALID_TO                LATEST_FLAG   
          abc           MN       123       05-MAR-2020 00:01:00    05-MAR-2020 16:29:00    N              
          abc           NY       722       05-MAR-2020 16:29:00    05-MAR-2020 16:31:41    N              
          abc           KS       444       05-MAR-2020 16:31:41    05-MAR-2020 18:01:13    N              
          abc           MN       123       05-MAR-2020 18:01:13    05-MAR-2020 18:01:16    N              
          abc           OH       333       05-MAR-2020 18:01:16    05-MAR-2020 19:01:15    N              
          abc           IL       223       05-MAR-2020 19:01:15    31-DEC-2099 00:00:00    Y              
          bbc           MN       123       19-MAR-2020 14:47:08    19-MAR-2020 14:50:37    N              
          bbc           IL       223       19-MAR-2020 14:50:37    31-DEC-2099 00:00:00    Y              
          ccb           MN       123       19-MAR-2020 14:56:24    31-DEC-2099 00:00:00    Y              
          dbd           KS       444       19-MAR-2020 00:00:00    31-DEC-2099 00:00:00    Y
  
insert into table_a values ( 'abc', 'TX', 654, sysdate );

exec load_data();

select * from table_b
order  by product_id, valid_from;

SEQUENCE_KEY    PRODUCT_ID    STATE    ZIP_CD    VALID_FROM              VALID_TO                LATEST_FLAG   
          abc           MN       123       05-MAR-2020 00:01:00    05-MAR-2020 16:29:00    N              
          abc           NY       722       05-MAR-2020 16:29:00    05-MAR-2020 16:31:41    N              
          abc           KS       444       05-MAR-2020 16:31:41    05-MAR-2020 18:01:13    N              
          abc           MN       123       05-MAR-2020 18:01:13    05-MAR-2020 18:01:16    N              
          abc           OH       333       05-MAR-2020 18:01:16    05-MAR-2020 19:01:15    N              
          abc           IL       223       05-MAR-2020 19:01:15    28-JUL-2020 14:54:52    N              
          abc           TX       654       28-JUL-2020 14:54:52    31-DEC-2099 00:00:00    Y              
          bbc           MN       123       19-MAR-2020 14:47:08    19-MAR-2020 14:50:37    N              
          bbc           IL       223       19-MAR-2020 14:50:37    31-DEC-2099 00:00:00    Y              
          ccb           MN       123       19-MAR-2020 14:56:24    31-DEC-2099 00:00:00    Y              
          dbd           KS       444       19-MAR-2020 00:00:00    31-DEC-2099 00:00:00    Y

注意设置latest_flag的case表达式-这假定2099年12月31日为 “无穷大”，因此标识当前值。如果您运行合并两次而中间没有任何更改，则当匹配子句中的where子句将停止此覆盖到N。