从重叠间隔创建完整的历史时间线

Oracle安装程序 以下内容:

CREATE TABLE Table1 ( code, "FROM", "TO", hours ) AS
SELECT 1, DATE '2013-05-01', DATE '2013-09-30', 37 FROM DUAL UNION ALL
SELECT 1, DATE '2013-05-01', DATE '2014-02-28', 10 FROM DUAL UNION ALL
SELECT 1, DATE '2013-10-01', DATE '9999-12-31',  5 FROM DUAL;

查询 以下内容:

SELECT *
FROM   (
  SELECT code,
         dt AS "FROM",
         LEAD( dt ) OVER ( PARTITION BY code ORDER BY dt ASC, value DESC, ROWNUM ) AS "TO",
         hours
  FROM   (
    SELECT code,
           dt,
           SUM( hours * value ) OVER ( PARTITION BY code ORDER BY dt ASC, VALUE DESC ) AS hours,
           value
    FROM   table1
    UNPIVOT ( dt FOR value IN ( "FROM" AS 1, "TO" AS -1 ) )
  )
)
WHERE  "FROM" + 1 < "TO";

结果 以下内容:

CODE FROM       TO         HOURS
---- ---------- ---------- -----
   1 2013-05-01 2013-09-30    47
   1 2013-10-01 2014-02-28    15
   1 2014-02-28 9999-12-31     5