sql查询将不连续的时间序列作为连续的时间序列 [英] sql query to make a discontinous time series as continous time series

问题描述

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

I am trying to run a sql query in Oracle 11g which will transform the below given data set to the next data set.

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
----------

这样做的逻辑是开始日期1和结束日期1将是连续的.此外 start_date2 和 end date2 需要是连续的.如果在某个时刻结束 date2 与下一个 startdate2 不匹配，则需要添加一个新行，其 ID 和 enddate2 与下一个开始日期 1 相同.

The logic of this is that the start date1 and end date1 will be continuous. Also the start_date2 and end date2 needs to be continuous. If at some point end date2 is not matching with the next startdate2 , then a new row needs be added having the same id and having enddate2 as the next start date1.

非常感谢任何帮助.

推荐答案

我对 Microsoft SQL 服务器做了类似的事情.这就是我用过的:

I did something similar with Microsoft SQL server. And this is what I have used:

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

<小时>

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

---

So, if the actual grouped data is like this:

转换后的系列(具有连续的月度数据，即填充所有缺失的月份)如下所示:

The transformed series (with continuous monthly data, i.e. all missing months filled) looks like this:

这篇关于sql查询将不连续的时间序列作为连续的时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！