使用日期维度的每月快照

Question

我有一些需要带到合并表（事实）的列。我有一个变更捕获表，它捕获每天记录的变更，如下所示：

CHG_TABLE:
+--------+-------------------+-----------------------+-----------+-----------+ 
|  Key   |     Start_Date    |      End_Date         |   Value   |Record_Type|
+--------+----- -------------+-----------------------+-----------+-----------+ 
| 1      |   5/25/2019  2.05 |      12/31/9999 00.00 |    800    | Insert    | 
| 1      |   5/25/2019  2.05 |      5/31/2019  11.12 |    800    | Update    | 
| 1      |   5/31/2019 11.12 |      12/31/9999 00.00 |    900    | Insert    | 
| 1      |   5/31/2019 11.12 |      6/15/2019  12.05 |    900    | Update    | 
| 1      |   6/15/2019 12.05 |      12/31/9999 00.00 |   1000    | Insert    | 
| 1      |   6/15/2019 12.05 |      6/25/2019  10.20 |   1000    | Update    | 
| 1      |   6/25/2019 10.20 |      12/31/9999 00.00 |    500    | Insert    | 
| 1      |   6/25/2019 10.20 |      6/30/2019  11.12 |    500    | Update    | 
| 1      |   6/30/2019 11.12 |      12/31/9999 00.00 |   3000    | Insert    | 
| 1      |   6/30/2019 11.12 |      7/15/2019  1.20  |   3000    | Update    | 
| 1      |   7/15/2019  1.20 |      12/31/9999 00.00 |   7000    | Insert    |
+--------+-------------------+-----------------------+-----------+-----------+

在第一次插入期间，End_Date是时间的结束。将具有新Start_Date和Value的新记录添加到源中时，它将被捕获为新条目，并且具有相同Key的先前记录将End_Date更新为新记录的Start_Date。

DIM_DATE：
+ -------- + ------------------- + -------------------- -+
| DateKey | Month_Start_Date | Month_End_Date |
+ -------- + ----- + ------------- + -------------------- -+
| 1 | 6/1/2019 | 6/30/2019 |
| 2 | 7/1/2019 | 7/31/2019 |
+ -------- + ------------------- + -------------------- -+

我正在使用DATE维度，该维度具有Month_Start_Date和Month_End_Date，因此我很挣扎。

我想从此更改表中创建每月快照，如下所示：

RESULT:
+--------+-------------------+-----------------------+-----------+-----------+
|  Key   | Month_Start_Date  |   Month_End_Date      |Begin_Value|End_Value  |
+--------+-----+-------------+-----------------------+-----------+-----------+
| 1      |   6/1/2019        |      6/30/2019        |   800     | 500       |
| 1      |   7/1/2019        |      7/31/2019        |   500     | 3000      |
+--------+-------------------+-----------------------+-----------+-----------+

Begin_Value：最大值（结束日期）<Month_Start_Date

End_Value：最大值（End_Date）<= Month_End_Date

Begin_Value应该是上个月的最新值（不是时间的结束），End_Value应该是基于Month_End_Date的最新值。

如何显示以上结果？

Answer 1

我认为您应该重新考虑一下自己的逻辑。

如果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_value
from   dim_date dd
INNER JOIN chg_table ct ON ct.start_date BETWEEN dd.month_start_date and dd.month_end_date
GROUP 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 2, DATE'2019-06-01', DATE'2019-07-01' - INTERVAL '1' SECOND FROM DUAL UNION ALL
SELECT 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_value
from   dim_date dd
INNER JOIN chg_table ct ON ct.start_date BETWEEN dd.month_start_date and dd.month_end_date
GROUP 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_value
from   dim_date dd
CROSS JOIN  dim_person k
GROUP BY k.key, dd.month_start_date, dd.month_end_date;