首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >从重叠间隔创建完整的历史时间线

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

Stack Overflow用户
提问于 2018-06-20 03:36:22
回答 1查看 65关注 0票数 1

我有下面的表格,其中包含一个代码,从,到和小时。问题是我在间隔中有重叠的日期。而不是它,我想创建一个完整的历史时间线。因此,当代码相同且存在重叠时,它应该将小时数相加,如预期结果所示。

**表**

代码语言:javascript
复制
+------+-------+--------------------------------------+
| code | from           | to             | hours      |
+------+-------+--------------------------------------+
| 1    | 2013-05-01     | 2013-09-30     | 37         |
| 1    | 2013-05-01     | 2014-02-28     | 10         |
| 1    | 2013-10-01     | 9999-12-31     | 5          |
+------+-------+--------------------------------------+

期望的结果:

代码语言:javascript
复制
+------+-------+--------------------------------------+
| code | from           | to             | hours      |
+------+-------+--------------------------------------+
| 1    | 2013-05-01     | 2013-09-30     | 47         |
| 1    | 2013-10-01     | 2014-02-28     | 15         |
| 1    | 2014-02-29     | 9999-12-31     | 5          |
+------+-------+--------------------------------------+
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-06-20 04:26:41

Oracle设置

代码语言:javascript
复制
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;

查询

代码语言:javascript
复制
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";

结果

代码语言:javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50935753

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档