问如何根据日期值在变化缓慢的维度类型2的中间插入行

EN

SQL> CREATE TABLE yourTable AS (
  2      SELECT '1234' AS Prod_Id, date '2020-03-10' AS Begin_Version_dt, date '2020-04-09' AS End_version_dt FROM dual UNION ALL
  3      SELECT '1234', date '2020-04-10', date '2020-05-10' FROM dual UNION ALL
  4      SELECT '1234', date '2020-05-11', date '9999-12-31' FROM dual
  5  );

Table created.

SQL>
SQL> CREATE TABLE prod_table as
  2  (SELECT 1234 AS PROD_ID, DATE '2020-05-15' AS TRANS_DATE FROM DUAL);

Table created.

SQL> SELECT * FROM YOURTABLE ORDER BY BEGIN_VERSION_DT;

PROD BEGIN_VER END_VERSI
---- --------- ---------
1234 10-MAR-20 09-APR-20
1234 10-APR-20 10-MAY-20
1234 11-MAY-20 31-DEC-99

SQL> select * from prod_table;

   PROD_ID TRANS_DAT
---------- ---------
      1234 15-MAY-20

SQL> INSERT INTO yourTable Y
  2  SELECT PROD_ID, TRANS_DATE, TRANS_DATE FROM PROD_TABLE;

1 row created.

SQL>
SQL>
SQL> UPDATE YOURTABLE YY
  2     SET END_VERSION_DT = COALESCE(
  3  (SELECT LD_BEGIN_DT
  4    FROM (SELECT Y.BEGIN_VERSION_DT,
  5     Y.PROD_ID,
  6     LEAD(Y.BEGIN_VERSION_DT)
  7  OVER (PARTITION BY Y.PROD_ID
  8  ORDER BY Y.BEGIN_VERSION_DT) - 1 AS LD_BEGIN_DT
  9    FROM YOURTABLE Y) Y
 10    WHERE YY.PROD_ID = Y.PROD_ID
 11  AND YY.BEGIN_VERSION_DT = Y.BEGIN_VERSION_DT),
 12  DATE '9999-12-31');

4 rows updated.

SQL> SELECT * FROM YOURTABLE ORDER BY BEGIN_VERSION_DT;
PROD BEGIN_VER END_VERSI
---- --------- ---------
1234 10-MAR-20 09-APR-20
1234 10-APR-20 10-MAY-20
1234 11-MAY-20 14-MAY-20
1234 15-MAY-20 31-DEC-99
SQL>

SELECT
    Prod_Id,
    Begin_Version_dt,
    COALESCE(End_Version_dt,
             LEAD(Begin_Version_dt, 1, date '9999-12-31')
             OVER (PARTITION BY Prod_Id ORDER BY Begin_Version_dt)) AS End_Version_dt
FROM yourTable
ORDER BY
    Prod_Id,
    Begin_Version_dt;