SQL查询将不连续时间序列制作为连续时间序列

Question

我试图在Oracle 11g中运行一个sql查询，它将下面给定的数据集转换为下一个数据集。

id| start date1       | end date1          |   start date2      | end date2
-------------------------------------------------------------------------------------
1 | 27/02/2017 01:00:00| 27/02/2017 02:00:00| 27/02/2017 01:00:00|27/02/2017 02:00:00
-------------------------------------------------------------------------------------
2 | 27/02/2017 02:00:00| 27/02/2017 04:00:00| 27/02/2017 02:00:00|27/02/2017 03:00:00
-------------------------------------------------------------------------------------
2 | 27/02/2017 02:00:00| 27/02/2017 04:00:00| 27/02/2017 03:00:00|27/02/2017 03:30:00
-------------------------------------------------------------------------------------
 3 | 27/02/2017 04:00:00| 27/02/2017 05:00:00| 27/02/2017 04:00:00|27/02/2017 05:00:00
----------

Final dataset :

id | start date1       | end date1          | start date2        | end date2
-------------------------------------------------------------------------------------
1 | 27/02/2017 01:00:00| 27/02/2017 02:00:00| 27/02/2017 01:00:00|27/02/2017 02:00:00
-------------------------------------------------------------------------------------
2 | 27/02/2017 02:00:00| 27/02/2017 04:00:00| 27/02/2017 02:00:00|27/02/2017 03:00:00
-------------------------------------------------------------------------------------
2 | 27/02/2017 02:00:00| 27/02/2017 04:00:00| 27/02/2017 03:00:00|27/02/2017 03:30:00
-------------------------------------------------------------------------------------
2 | 27/02/2017 02:00:00| 27/02/2017 04:00:00| 27/02/2017 03:30:00|27/02/2017 04:00:00
-------------------------------------------------------------------------------------
 3 | 27/02/2017 04:00:00| 27/02/2017 05:00:00| 27/02/2017 04:00:00|27/02/2017 05:00:00
----------

这样做的逻辑是start date1和end date1将是连续的。另外start_date2和end date2需要是连续的。如果在某些时候end date2与下一个start date2不匹配，那么需要添加一个具有相同id并且具有enddate2作为下一个start date1的新行。

非常感谢您的帮助。

Answer 1

我在Microsoft SQL server上也做了类似的事情。这就是我所用的：

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results

CREATE TABLE #Results ( 
MonthYear DATE,
[Month] INT, 
[Year] INT                         
)

DECLARE @Y1 INT = 2019, @Y2 INT = 2020, @M1 INT = 1, @M2 INT = 12

WHILE @Y1 <= @Y2
BEGIN
        WHILE @M1 <= @M2
        BEGIN
            INSERT INTO #Results (MonthYear, [Month], [Year])
            VALUES(DATEFROMPARTS(@Y1,@M1,1),@M1,@Y1)
            SET @M1 = @M1+1
        END
        SET @Y1 = @Y1 + 1
END

SELECT R.MonthYear,
COUNT(T.CreatedOn) AS [Counts]
FROM #Results R
LEFT JOIN MyTable T ON DATEADD(MONTH, DATEDIFF(MONTH, 0, T.CreatedOn), 0) = R.MonthYear
GROUP BY MonthYear

所以，如果实际的分组数据是这样的：

Answer 2

例如，我进行下一个查询

with s (id,start_date1,end_date1,start_date2,end_date2) as 
(select 
1, to_date('27/02/2017 01:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 02:00:00','dd/mm/yyyy HH24:MI:SS'), 
to_date('27/02/2017 01:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 02:00:00','dd/mm/yyyy HH24:MI:SS') from dual union all
select 2, to_date('27/02/2017 02:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 04:00:00','dd/mm/yyyy HH24:MI:SS'), 
to_date('27/02/2017 02:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 03:00:00','dd/mm/yyyy HH24:MI:SS') from dual union all
select 2, to_date('27/02/2017 02:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 04:00:00','dd/mm/yyyy HH24:MI:SS'), 
to_date('27/02/2017 03:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 03:30:00','dd/mm/yyyy HH24:MI:SS') from dual union all
select 3, to_date('27/02/2017 04:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 05:00:00','dd/mm/yyyy HH24:MI:SS'), 
to_date('27/02/2017 04:00:00','dd/mm/yyyy HH24:MI:SS'), to_date('27/02/2017 05:00:00','dd/mm/yyyy HH24:MI:SS') from dual
)
select distinct
id,sd1, ed1,  sd2, ed2 from (
select id,
       to_char(sd1,'dd/mm/yyyy HH24:MI:SS') sd1,
       to_char(ed1,'dd/mm/yyyy HH24:MI:SS') ed1, 
       to_char(border,'dd/mm/yyyy HH24:MI:SS') as sd2, 
       to_char(nvl(lead(border) over (partition by sd1, ed1 order by border),ed1),'dd/mm/yyyy HH24:MI:SS') as ed2  
from (
select id,
       start_date1 as sd1,
       end_date1 as ed1 ,
       decode(id1, 0,start_date2,end_date2)   as border,
       start_date2 as sd2 ,
       end_date2 as ed2,
       id1
       from s
       join (select rownum -1 as id1 from dual connect by level <= 2) on 1=1 )) 
where sd2 < ed2;