Алгоритм "Натягивание" (Pull Story)

      Часто встречает ситуация когда надо от истории по 2 и более ключам перейти к истории по меньшему количеству ключей. Для получения новой истории используется так называемый метод "натягивания"  (pull story). Смысл в том что мы сначало строим временную шкалу в нашем примере hist_table_time по нужному ключу. Потом соединяем временную шкалу с таблицей на момент времени (т.е. на  eff_dttm из шкалы времени) и получаем нужные нам  поля различными аггрегирующими функциями (в нашем примере это MAX)

      Данный алгоритм может применяться и для натягивания нескольких таблиц на общую временную шкалу которую можно получить через UNION. 

WITH 
  --таблица с историей и ключом из двух полей 
  hist_table AS 
             (SELECT 1 AS key1, 1 key2, DATE '2010-01-01' AS eff_dttm, DATE '2011-01-01' AS exp_dttm FROM dual UNION
              SELECT 1 AS key1, 1 key2, DATE '2011-01-01' AS eff_dttm, DATE '2999-12-31' AS exp_dttm FROM dual UNION
              SELECT 1 AS key1, 3 key2, DATE '2010-01-01' AS eff_dttm, DATE '2999-12-31' AS exp_dttm FROM dual UNION
              SELECT 2 AS key1, 4 key2, DATE '2012-01-01' AS eff_dttm, DATE '2999-12-31' AS exp_dttm FROM dual),
                 
  --временная шкала с ключом из одного поля               
  hist_table_time AS
            (SELECT DISTINCT
                  key1,
                  eff_dttm,
                  NVL(LEAD(eff_dttm) OVER(PARTITION BY key1 ORDER BY eff_dttm),DATE '2999-12-31') AS exp_dttm
              FROM
                  hist_table)
   --  
    SELECT 
      t.key1,
      MAX(t.key2) AS  val2,
      tm.eff_dttm,
      tm.exp_dttm
    FROM 
      hist_table t,
      hist_table_time tm
    WHERE 
      t.key1=tm.key1
      AND tm.eff_dttm >= t.eff_dttm
      AND tm.eff_dttm < t.exp_dttm
      AND tm.eff_dttm < tm.exp_dttm
    GROUP BY  
      t.key1,
      tm.eff_dttm,
      tm.exp_dttm;

В результате получим:

	KEY1	VAL2	EFF_DTTM	EXP_DTTM
2	1	3	01.01.2010	01.01.2011
1	1	3	01.01.2011	31.12.2999
3	2	4	01.01.2012	31.12.2999