我试着看看如何在sql中做到这一点，但由于逻辑以及您在所需输出中的顺序键重置，这对我来说是不可能的。
所以，这里是我在pl/sql中的建议

SQL> select * from table_a ;

PRODUCT_ID                     STATE                          ZIP_CD                         MODIFIED_
------------------------------ ------------------------------ ------------------------------ ---------
abc                            MN                             123                            05-MAR-20
abc                            MN                             123                            05-MAR-20
abc                            IL                             223                            05-MAR-20
abc                            OH                             333                            05-MAR-20
abc                            NY                             722                            05-MAR-20
abc                            KS                             444                            05-MAR-20
bbc                            MN                             123                            19-MAR-20
bbc                            IL                             223                            19-MAR-20
ccb                            MN                             123                            19-MAR-20
dbd                            KS                             444                            19-MAR-20

10 rows selected.

SQL> select * from table_b ;

SEQUENCE_KEY PRODUCT_ID                     STATE                          ZIP_CD                         VALID_FRO VALID_TO  L
------------ ------------------------------ ------------------------------ ------------------------------ --------- --------- -
           1 abc                            AR                             999                            05-MAR-20 05-MAR-20 N
           2 abc                            AR                             555                            05-MAR-20 05-MAR-20 N
           3 abc                            CA                             565                            05-MAR-20 05-MAR-20 N
           4 abc                            CA                             777                            05-MAR-20 31-DEC-99 Y
           5 bbc                            MN                             123                            05-MAR-20 05-MAR-20 N
           6 bbc                            MN                             666                            05-MAR-20 05-MAR-20 N
           7 bbc                            MN                             777                            19-MAR-20 31-DEC-99 Y
           8 ccb                            MN                             123                            19-MAR-20 31-DEC-99 Y

8 rows selected.

现在，我使用了这段plu-sql代码

declare 
type typ_rec_set IS  RECORD
(
 PRODUCT_ID        VARCHAR2(30 CHAR),
 STATE             VARCHAR2(30 CHAR),
 ZIP_CD            VARCHAR2(30 CHAR),
 VALID_FROM        DATE             ,
 VALID_TO          DATE             ,
 LATEST_FLAG       VARCHAR2(1 CHAR) 
);
type typ_rec_tab is TABLE OF typ_rec_set;
l_hdr_tab  typ_rec_tab;
begin
SELECT product_id
      ,state
      ,zip_cd
      ,valid_from
      ,valid_to
      ,CASE WHEN valid_to = DATE '2099-12-31' THEN 'Y' ELSE 'N' END latest_flag
      BULK COLLECT INTO l_hdr_tab
FROM
(
SELECT a.product_id
      ,a.state
      ,a.zip_cd
      ,a.modified_dt valid_from
      ,NVL(((LEAD (a.modified_dt,1) OVER (PARTITION BY a.product_id ORDER BY a.modified_dt))  - INTERVAL '1' SECOND),DATE '2099-12-31' )valid_to
      ,CASE
          WHEN ( (    b.product_id IS NOT NULL 
                  AND a.state != b.state
                  AND a.zip_cd != b.zip_cd)
                OR b.product_id IS NULL
               ) THEN
           1
          ELSE
           0
       END insert_flag
FROM   table_a a
LEFT OUTER JOIN   table_b b
ON     a.product_id = b.product_id
AND    b.latest_flag = 'Y'
WHERE  (a.modified_dt >= b.valid_from OR b.product_id IS NULL)
ORDER BY a.product_id,a.modified_dt
)
WHERE insert_flag != 0  ;
--loop
FOR i IN l_hdr_tab.first .. l_hdr_tab.last 
LOOP
    -- begin block 
    begin
        insert into table_b 
        (
         sequence_key ,
         PRODUCT_ID   ,       
         STATE        ,       
         ZIP_CD       ,       
         VALID_FROM   ,       
         VALID_TO     ,       
         LATEST_FLAG                 
        )
        values
        (
        ( select max(sequence_key)+1 from table_b ),
        l_hdr_tab(i).product_id ,
        l_hdr_tab(i).state ,
        l_hdr_tab(i).zip_cd ,
        l_hdr_tab(i).valid_from ,
        l_hdr_tab(i).valid_to ,
        l_hdr_tab(i).latest_flag 
        );
     end;
end loop;-- reset sequence base of row_number over product_id valid_from
commit;
-- reset sequence
merge into table_b t
using ( select     sequence_key ,
         PRODUCT_ID   ,       
         STATE        ,       
         ZIP_CD       ,       
         VALID_FROM   ,       
         VALID_TO     ,       
         LATEST_FLAG  ,
         row_number() over ( order by product_id,valid_from ) as new_seq 
          from table_b ) s
on ( s.rowid = t.rowid ) 
when matched then 
  update set t.sequence_key = s.new_seq where t.sequence_key != s.new_seq ;
commit;
exception when others then raise;
end;
/

然后我运行它

SQL> host cat proc.sql
declare
type typ_rec_set IS  RECORD
(
 PRODUCT_ID        VARCHAR2(30 CHAR),
 STATE             VARCHAR2(30 CHAR),
 ZIP_CD            VARCHAR2(30 CHAR),
 VALID_FROM        DATE             ,
 VALID_TO          DATE             ,
 LATEST_FLAG       VARCHAR2(1 CHAR)
);
type typ_rec_tab is TABLE OF typ_rec_set;
l_hdr_tab  typ_rec_tab;
begin
SELECT product_id
      ,state
      ,zip_cd
      ,valid_from
      ,valid_to
      ,CASE WHEN valid_to = DATE '2099-12-31' THEN 'Y' ELSE 'N' END latest_flag
      BULK COLLECT INTO l_hdr_tab
FROM
(
SELECT a.product_id
      ,a.state
      ,a.zip_cd
      ,a.modified_dt valid_from
      ,NVL(((LEAD (a.modified_dt,1) OVER (PARTITION BY a.product_id ORDER BY a.modified_dt))  - INTERVAL '1' SECOND),DATE '2099-12-31' )valid_to
      ,CASE
          WHEN ( (    b.product_id IS NOT NULL
                  AND a.state != b.state
                  AND a.zip_cd != b.zip_cd)
                OR b.product_id IS NULL
               ) THEN
           1
          ELSE
           0
       END insert_flag
FROM   table_a a
LEFT OUTER JOIN   table_b b
ON     a.product_id = b.product_id
AND    b.latest_flag = 'Y'
WHERE  (a.modified_dt >= b.valid_from OR b.product_id IS NULL)
ORDER BY a.product_id,a.modified_dt
)
WHERE insert_flag != 0  ;
--loop
FOR i IN l_hdr_tab.first .. l_hdr_tab.last
LOOP
    -- begin block
    begin
        insert into table_b
        (
         sequence_key ,
         PRODUCT_ID   ,
         STATE        ,
         ZIP_CD       ,
         VALID_FROM   ,
         VALID_TO     ,
         LATEST_FLAG
        )
        values
        (
        ( select max(sequence_key)+1 from table_b ),
        l_hdr_tab(i).product_id ,
        l_hdr_tab(i).state ,
        l_hdr_tab(i).zip_cd ,
        l_hdr_tab(i).valid_from ,
        l_hdr_tab(i).valid_to ,
        l_hdr_tab(i).latest_flag
        );
     end;
end loop;-- reset sequence base of row_number over product_id valid_from
commit;
-- reset sequence
merge into table_b t
using ( select     sequence_key ,
         PRODUCT_ID   ,
         STATE        ,
         ZIP_CD       ,
         VALID_FROM   ,
         VALID_TO     ,
         LATEST_FLAG  ,
         row_number() over ( order by product_id,valid_from ) as new_seq
          from table_b ) s
on ( s.rowid = t.rowid )
when matched then
  update set t.sequence_key = s.new_seq where t.sequence_key != s.new_seq ;
commit;
exception when others then raise;
end;
/

SQL> @proc.sql

PL/SQL procedure successfully completed.

SQL> select * from table_b order by sequence_key ;

SEQUENCE_KEY PRODUCT_ID                     STATE                          ZIP_CD                         VALID_FRO VALID_TO  L
------------ ------------------------------ ------------------------------ ------------------------------ --------- --------- -
           1 abc                            AR                             999                            05-MAR-20 05-MAR-20 N
           2 abc                            NY                             722                            05-MAR-20 05-MAR-20 N
           3 abc                            CA                             777                            05-MAR-20 31-DEC-99 Y
           4 abc                            KS                             444                            05-MAR-20 05-MAR-20 N
           5 abc                            MN                             123                            05-MAR-20 05-MAR-20 N
           6 abc                            AR                             555                            05-MAR-20 05-MAR-20 N
           7 abc                            CA                             565                            05-MAR-20 05-MAR-20 N
           8 abc                            OH                             333                            05-MAR-20 05-MAR-20 N
           9 abc                            IL                             223                            05-MAR-20 31-DEC-99 Y
          10 bbc                            MN                             666                            05-MAR-20 05-MAR-20 N
          11 bbc                            MN                             123                            05-MAR-20 05-MAR-20 N

SEQUENCE_KEY PRODUCT_ID                     STATE                          ZIP_CD                         VALID_FRO VALID_TO  L
------------ ------------------------------ ------------------------------ ------------------------------ --------- --------- -
          12 bbc                            MN                             777                            19-MAR-20 31-DEC-99 Y
          13 bbc                            IL                             223                            19-MAR-20 31-DEC-99 Y
          14 ccb                            MN                             123                            19-MAR-20 31-DEC-99 Y
          15 dbd                            KS                             444                            19-MAR-20 31-DEC-99 Y

15 rows selected.

SQL>

你有什么疑问尽管告诉我。我知道我肯定错过了什么；）
更新
我意识到我在循环中有一个无用的操作，计算maxvalue为字段序列\ u键。我这里有一个更好的程序版本：

declare
type typ_rec_set IS  RECORD
(
 PRODUCT_ID        VARCHAR2(30 CHAR),
 STATE             VARCHAR2(30 CHAR),
 ZIP_CD            VARCHAR2(30 CHAR),
 VALID_FROM        DATE             ,
 VALID_TO          DATE             ,
 LATEST_FLAG       VARCHAR2(1 CHAR) 
);
type typ_rec_tab is TABLE OF typ_rec_set;
l_hdr_tab  typ_rec_tab;
r    pls_integer := 1;
vseq pls_integer;
begin
-- calculate value sequence 
select max(sequence_key) into vseq from table_b ;
SELECT product_id
      ,state
      ,zip_cd
      ,valid_from
      ,valid_to
      ,CASE WHEN valid_to = DATE '2099-12-31' THEN 'Y' ELSE 'N' END latest_flag
      BULK COLLECT INTO l_hdr_tab
FROM
(
SELECT a.product_id
      ,a.state
      ,a.zip_cd
      ,a.modified_dt valid_from
      ,NVL(((LEAD (a.modified_dt,1) OVER (PARTITION BY a.product_id ORDER BY a.modified_dt))  - INTERVAL '1' SECOND),DATE '2099-12-31' )valid_to
      ,CASE
          WHEN ( (    b.product_id IS NOT NULL 
                  AND a.state != b.state
                  AND a.zip_cd != b.zip_cd)
                OR b.product_id IS NULL
               ) THEN
           1
          ELSE
           0
       END insert_flag
FROM   table_a a
LEFT OUTER JOIN   table_b b
ON     a.product_id = b.product_id
AND    b.latest_flag = 'Y'
WHERE  (a.modified_dt >= b.valid_from OR b.product_id IS NULL)
ORDER BY a.product_id,a.modified_dt
)
WHERE insert_flag != 0  ;
--loop
FOR i IN l_hdr_tab.first .. l_hdr_tab.last 
LOOP
    -- begin block 
    vseq := vseq + r ;
    begin
        insert into table_b 
        (
         sequence_key ,
         PRODUCT_ID   ,       
         STATE        ,       
         ZIP_CD       ,       
         VALID_FROM   ,       
         VALID_TO     ,       
         LATEST_FLAG                 
        )
        values
        (
        vseq ,
        l_hdr_tab(i).product_id ,
        l_hdr_tab(i).state ,
        l_hdr_tab(i).zip_cd ,
        l_hdr_tab(i).valid_from ,
        l_hdr_tab(i).valid_to ,
        l_hdr_tab(i).latest_flag 
        );
     end;
    r := r + 1; 
end loop;-- reset sequence base of row_number over product_id valid_from
commit;
-- reset sequence
merge into table_b t
using ( select     sequence_key ,
         PRODUCT_ID   ,       
         STATE        ,       
         ZIP_CD       ,       
         VALID_FROM   ,       
         VALID_TO     ,       
         LATEST_FLAG  ,
         row_number() over ( order by product_id,valid_from ) as new_seq 
          from table_b ) s
on ( s.rowid = t.rowid ) 
when matched then 
  update set t.sequence_key = s.new_seq where t.sequence_key != s.new_seq ;
commit;
exception when others then raise;
end;
/

我会以我的理解力第一次尝试。作为插入tableb的源的游标如下所示，

SELECT product_id
      ,state
      ,zip_cd
      ,valid_from
      ,valid_to
      ,CASE WHEN valid_to = DATE '2099-12-31' THEN 'Y' ELSE 'N' END latest_flag
FROM
(
SELECT a.product_id
      ,a.state
      ,a.zip_cd
      ,a.modified_dt valid_from
      ,NVL(((LEAD (a.modified_dt,1) OVER (PARTITION BY a.product_id ORDER BY a.modified_dt))  - INTERVAL '1' SECOND),DATE '2099-12-31' )valid_to
      ,CASE
          WHEN ( (    b.product_id IS NOT NULL 
                  AND a.state != b.state
                  AND a.zip_cd != b.zip_cd)
                OR b.product_id IS NULL
               ) THEN
           1
          ELSE
           0
       END insert_flag
FROM   table_a a
LEFT OUTER JOIN   table_b b
ON     a.product_id = b.product_id
AND    b.latest_flag = 'Y'
WHERE  (a.modified_dt >= b.valid_from OR b.product_id IS NULL)
ORDER BY a.product_id,a.modified_dt
)
WHERE insert_flag != 0;

left outer join检查记录是否存在于tableb中，where子句检查最新的\u标志“y”的修改日期是否大于有效的\u from
内部case语句将告诉我们属性是否更改，如果产品id不存在，它也会将其视为第一个条目，insert\u标志将为1
根据31-12-2099的“修改日期”列，在最后一条记录的情况下，outer case语句提供有效的\u to
关于第三点我还不完全清楚，但我相信案例陈述正是我们所需要的。
最后我没有考虑性能问题。您可以考虑将其转换为pl/sql块和其他收集方法来处理块中的数据。
这里还有一个问题，如果产品id为“dbd”（这是一个新条目，在tableb中不存在）的记录在tablea中多次出现，会发生什么？

这就是数据仓库中的缓慢变化维度（scd）类型2问题（kimball方法）。你可以在这里看到一个简短的定义
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/slowlychangingdimensions.htm
对scd类型2的支持仅在owb 10gr2的企业etl选项中提供，如上述链接所述。如果这不可用并且必须使用pl/sql，那么可以检查以下方法。不幸的是，oraclepl/sql不像mssql那样提供直接的解决方案。
在oracle中实现类型2 scd