我认为您应该重新考虑一下自己的逻辑。
如果
CHG_TABLE在7月15日有“更新”记录，并且以后没有更改，则该新值应为7月的最终值。
假设（如果较大）是正确的，那么您应该完全忽略该
END_DATE列。如果可以，请将其从数据模型中删除。不用了
而是在上创建一个降序索引
CHG_TABLE.START_DATE，如下所示：
create index chg_table_n1 on chg_table (start_date desc);
然后，您应该能够像这样高效地创建快照：
select ct.key, dd.month_start_date, dd.month_end_date, ( SELECT value FROM chg_table ct2 WHERe ct2.key = ct.key AND ct2.start_date < dd.month_start_date ORDER BY ct2.start_date DESC FETCH FIRST 1 ROW onLY ) first_value, max(ct.value) keep ( dense_rank last order by ct.start_date ) last_valuefrom dim_date ddINNER JOIN chg_table ct ON ct.start_date BETWEEN dd.month_start_date and dd.month_end_dateGROUP BY ct.key, dd.month_start_date, dd.month_end_date;
希望您使用的是12.1或更高版本的
FETCH FIRST语法。否则，您需要将该部分调整为12.1之前的版本。
带有测试数据的完整示例
WITH chg_table ( key, start_date, end_date, value, record_type ) AS( SELECt 1,TO_DATE('5/25/2019 2.05','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 800, 'Insert' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('5/25/2019 2.05','MM/DD/YYYY HH24.MI'),TO_DATE('5/31/2019 11.12','MM/DD/YYYY HH24.MI'), 800, 'Update' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('5/31/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 900, 'Insert' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('5/31/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('6/15/2019 12.05','MM/DD/YYYY HH24.MI'), 900, 'Update' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/15/2019 12.05','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 1000, 'Insert' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/15/2019 12.05','MM/DD/YYYY HH24.MI'),TO_DATE('6/25/2019 10.20','MM/DD/YYYY HH24.MI'), 1000, 'Update' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/25/2019 10.20','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 500, 'Insert' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/25/2019 10.20','MM/DD/YYYY HH24.MI'),TO_DATE('6/30/2019 11.12','MM/DD/YYYY HH24.MI'), 500, 'Update' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/30/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'),3000, 'Insert' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('6/30/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('7/15/2019 1.20','MM/DD/YYYY HH24.MI'), 3000, 'Update' FROM DUAL UNIOn ALLSELECt 1,TO_DATE('7/15/2019 1.20','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'),7000, 'Insert' FROM DUAL ),dim_date ( datekey, month_start_date, month_end_date ) AS (SELECt 1, DATE'2019-05-01', DATE'2019-06-01' - INTERVAL '1' SECOND FROM DUAL UNIOn ALLSELECt 2, DATE'2019-06-01', DATE'2019-07-01' - INTERVAL '1' SECOND FROM DUAL UNIOn ALLSELECt 3, DATE'2019-07-01', DATE'2019-08-01' - INTERVAL '1' SECOND FROM DUAL )select ct.key, dd.month_start_date, dd.month_end_date, ( SELECT value FROM chg_table ct2 WHERe ct2.key = ct.key AND ct2.start_date < dd.month_start_date ORDER BY ct2.start_date DESC FETCH FIRST 1 ROW onLY ) first_value, max(ct.value) keep ( dense_rank last order by ct.start_date ) last_valuefrom dim_date ddINNER JOIN chg_table ct ON ct.start_date BETWEEN dd.month_start_date and dd.month_end_dateGROUP BY ct.key, dd.month_start_date, dd.month_end_date;+-----+------------------+----------------+-------------+------------+| KEY | MONTH_START_DATE | MONTH_END_DATE | FIRST_VALUE | LAST_VALUE |+-----+------------------+----------------+-------------+------------+| 1 | 01-MAY-19 | 31-MAY-19 | | 900 || 1 | 01-JUN-19 | 30-JUN-19 | 900 | 3000 || 1 | 01-JUL-19 | 31-JUL-19 | 3000 | 7000 |+-----+------------------+----------------+-------------+------------+
更新-假设存在DIM_PERSON
表，则不带MAX（）.. KEEP（）版本
select k.key, dd.month_start_date, dd.month_end_date, ( SELECT value FROM chg_table ct2 WHERe ct2.key = k.key AND ct2.start_date < dd.month_start_date ORDER BY ct2.start_date DESC FETCH FIRST 1 ROW onLY ) first_value, ( SELECt value FROM chg_table ct2 WHERe ct2.key = k.key AND ct2.start_date <= dd.month_end_date ORDER BY ct2.start_date DESC FETCH FIRST 1 ROW onLY ) last_valuefrom dim_date ddCROSS JOIN dim_person kGROUP BY k.key, dd.month_start_date, dd.month_end_date;
