sql - 如何从重叠日期中获取单独的时间间隔

Question

我有一个包含开始和结束日期的表格，如下所示

drop table if exists #Temp
select * 
into #Temp
from (values (1, 1, '2011-10-09', '2011-10-20'),
             (2, 1, '2011-10-14', '2011-10-30'),
             (3, 1, '2012-05-21', '2012-05-25')
     ) as value (id, userId, d1, d2)

这给了我这个初始表:

id  userId    d1            d2
1   1         2011-10-09    2011-10-20
2   1         2011-10-14    2011-10-30
3   1         2012-05-21    2012-05-25

问题:我想要实现的是从这 2 个重叠日期中获取 3 个间隔。如何实现？

select * 
from 
    #Temp t1
    join #Temp t2
        on t1.userId = t2.userId
        and t1.id != t2.id
where
    t1.d1 <= t2.d2
    and t1.d2 >= t2.d1
order by t1.d1

给我

id  userId    d1            d2              id  userId    d1            d2
1   1         2011-10-09    2011-10-20      2   1         2011-10-14    2011-10-30
2   1         2011-10-14    2011-10-30      1   1         2011-10-09    2011-10-20  

我不确定如何从这里继续。

我想要实现的是下表，其中重叠被分开。

预期结果:

userId  d1          d2              
1       2011-10-09  2011-10-14
1       2011-10-14  2011-10-20
1       2011-10-20  2011-10-30
1       2012-05-21  2012-05-25

Answer 1

一种方法是对数据进行逆透视，然后对日期进行排序，然后再次对数据进行透视，排除一些记录。

WITH dataSource AS
(
    select id, userId, d1
    from #Temp t1
    UNION ALL
    select id, userId, d2
    from #Temp t1
), DataSourceOrdered AS
(
    SELECT 
        id, userId, d1,
        row_number () OVER (PARTITION BY userId ORDER By d1) as rowid
    FROM dataSource DS1
)
SELECT *
FROM DataSourceOrdered ds1
INNER JOIN DataSourceOrdered ds2
    ON ds1.userId = ds2.[userId]
    and ds1.[rowid] + 1 = ds2.[rowid]
    and exists (
        SELECT 1 
        FROM #Temp x 
        WHERE ds1.[d1] >= x.d1 and ds1.[d1] < x.d2 and ds1.userId = x.userId
    )

它可能需要根据您的真实数据进行一些更改，但对于当前的数据，它似乎可以工作。