解决日期范围的连续性问题（间隙和孤岛）

Question

我有一张表格，其中列X是给定起始和结束期间内物品的价格。

X    START_DATE     END_DATE
------------------------------    
1    01-01-2014     01-01-2016
2    01-04-2014     01-05-2014
3    01-07-2014     01-08-2014

然而，第一条目录定义了一个更长的时间范围，只有当物品价格未定义为每月一次时，才应予考虑，类似于第二和第三条记录，或者当某个范围缺失时。现在期望的输出是：

X    START_DATE      END_DATE
------------------------------    
1    01-01-2014     01-04-2014
2    01-04-2014     01-05-2014
1    01-05-2014     01-07-2014
3    01-07-2014     01-08-2014
1    01-08-2014     01-01-2016

我该如何实现这个目标？

- TharunRaja

---

你能否请发一下你目前尝试过的查询语句？ - mohan111

如果起始日期的年份等于结束日期的年份，则返回起始日期，否则使用以下公式生成新的开始日期：DATEADD(MM,DATEDIFF(MM,0,LAG(END_DATE,1) OVER (ORDER BY END_DATE))+1,0)。CASE中的条件只是暂时解决我的问题。 - TharunRaja

仔细观察日期和价格。不知道这是否很困难。 - TharunRaja

你发布的价格是从哪里来的？你只提供了开始和结束日期。 - mohan111

如果考虑日期范围的一般情况，要在单个查询中解决这个问题可能相当困难——这是仅有的几种情况之一，其中“最小工作量”解决方案实际上是使用游标并遍历数据。例如，您没有说明是否可能稍后添加一个日期范围为例如01-01-2000至01-01-2999的情况。此外，用于决定哪个日期范围“获胜”的规则是什么——始终是较短的日期范围、最高价格等吗？ - SlimsGhost

Answer 1

检查这个，如果您满意，请+1。

-- Data Samples
declare @X table ( Price int, datefrom datetime, dateto datetime)
insert @X values ( 1, '1.1.2014','1.1.2016'),(2,'1.4.2014','1.5.2014'),(3,'1.7.2014','1.8.2014');

-- Check samples
select * from @X;

-- Query
with Dat as ( 
            select datefrom from @X
            union
            select dateto from @X 
)
, Periods as ( 
            select datefrom,dateto = LEAD(datefrom,1) over (order by datefrom) 
            from Dat
)
,val as ( select Pr.*,P.*
            from Periods P 
            cross apply ( select top 1 Price from @X
                        where P.datefrom between datefrom and dateto - 0.000001
                        order by DATEDIFF(day,datefrom,dateto)
            ) Pr

)

select * from val

输出

Price       datefrom                dateto
----------- ----------------------- -----------------------
1           2014-01-01 00:00:00.000 2016-01-01 00:00:00.000
2           2014-04-01 00:00:00.000 2014-05-01 00:00:00.000
3           2014-07-01 00:00:00.000 2014-08-01 00:00:00.000

(3 row(s) affected)

Price       datefrom                dateto
----------- ----------------------- -----------------------
1           2014-01-01 00:00:00.000 2014-04-01 00:00:00.000
2           2014-04-01 00:00:00.000 2014-05-01 00:00:00.000
1           2014-05-01 00:00:00.000 2014-07-01 00:00:00.000
3           2014-07-01 00:00:00.000 2014-08-01 00:00:00.000
1           2014-08-01 00:00:00.000 2016-01-01 00:00:00.000

(5 row(s) affected)

- DimaSUN

---

我认为只有当您有一个长周期和一些短周期时，它才能正常工作。否则，您应该更改cte的val部分中的条件。 - DimaSUN

Answer 2

;WITH cte AS (
    SELECT *
    FROM (VALUES
    (1, '2014-01-01', '2016-01-01'),
    (2, '2014-04-01', '2014-05-01'),
    (3, '2014-07-01', '2014-08-01')
    ) as t(X, [START_DATE], [END_DATE])
)
,dates AS (
SELECT  ROW_NUMBER() OVER ( ORDER BY d.[Date] ) as r,
        d.[Date]
FROM (
    SELECT c.START_DATE as [Date] FROM cte c
    UNION 
    SELECT c.END_DATE as [Date] FROM cte c
) as d)

SELECT  MAX(c.X) AS X,
        d.[Date] AS [START_DATE],
        d1.[Date] AS [END_DATE]
FROM dates d 
INNER JOIN dates d1 ON d.r = d1.r-1
LEFT JOIN cte c ON d.[Date] BETWEEN c.START_DATE and c.END_DATE AND d1.[Date] BETWEEN c.START_DATE and c.END_DATE
GROUP BY d.[Date], d1.[Date]

结果：

X           START_DATE END_DATE
----------- ---------- ----------
1           2014-01-01 2014-04-01
2           2014-04-01 2014-05-01
1           2014-05-01 2014-07-01
3           2014-07-01 2014-08-01
1           2014-08-01 2016-01-01

(5 row(s) affected)

但是，如果您添加了一些新的范围，这将覆盖另一个范围（例如(6, '2014-07-15', '2015-08-01')），则应更改该查询。

- gofr1