使用SQL查找一组区间的数学补码

Question

我正在思考以下需要通过SQL解决的问题。设有一个自然数的区间a，b，以及一个(有限)的区间A集合，这些区间A都是a，b的子集。我们要确定补集auf A，即一组使得A+B= a，b以及A和B是成对不交的区间B。

例如:给定a，b= 2017年的天数("all days")，以及间隔3-6，4，4，4-7和11(“可能的天数”)。现在产生间隔jan-feb，aug-oct和dec (“不可能的天数”)。所有间隔分别为。应该通过开始日期和结束日期来定义。

我尝试了以下方法。制作2017年的日历，并检查每天是否包含在这两个间隔中。从这些天开始，构建相应的间隔。到目前为止，它看起来很复杂，我开始认为这种解决方案方法在SQL中有点不走运。但也许这只是我的实现。你认为如何？你可能知道更好的方法吗？

法兰克福的问候，

约翰尼斯

Answer 1

只要你是在一个已知的固定范围内工作，那么你就可以很容易地找到在任何当前区间之外的候选对象。然后根据日期顺序将它们配对：

declare @RangeStart date
declare @RangeEnd date
select @RangeStart = '20170101',@RangeEnd = '20171231'

declare @intervals table (
    StartAt date not null,
    EndAt date not null
)
insert into @intervals (StartAt,EndAt) values
('20170301','20170630'),
('20170401','20170430'),
('20170401','20170731'),
('20171101','20171130')

;With Starts as (
    select
        @RangeStart as StartDT
    where
        not exists (select * from @intervals i where @RangeStart between i.StartAt and i.EndAt) --Start outside an interval
    union all
    select
        DATEADD(day,1,i1.EndAt)
    from
        @intervals i1
            left join
        @intervals i2
            on
                DATEADD(day,1,i1.EndAt) between i2.StartAt and i2.EndAt --No succeeding interval
    where
        i2.EndAt is null
), Ends as (
    select
        @RangeEnd as EndDT
    where
        not exists (select * from @intervals i where @RangeEnd between i.StartAt and i.EndAt) --End outside an interval
    union all
    select
        DATEADD(day,-1,i1.StartAt)
    from
        @intervals i1
            left join
        @intervals i2
            on
                DATEADD(day,-1,i1.StartAt) between i2.StartAt and i2.EndAt --No preceding interval
    where
        i2.StartAt is null
), OrderedStarts as (
    select StartDT,ROW_NUMBER() OVER (ORDER BY StartDT) as rn
    from Starts where StartDT between @RangeStart and @RangeEnd
), OrderedEnds as (
    select EndDT,ROW_NUMBER() OVER (ORDER BY EndDt) as rn
    from Ends where EndDT between @RangeStart and @RangeEnd
)

select
    os.StartDT,oe.EndDT
from
    OrderedStarts os
        inner join
    OrderedEnds oe
        on
            os.rn = oe.rn

结果：

StartDT    EndDT
---------- ----------
2017-01-01 2017-02-28
2017-08-01 2017-10-31
2017-12-01 2017-12-31

也就是说，有效的开始日期是我们的范围的开始，或者是任何其他间隔的后一天，前提是它不与另一个间隔重叠。对于有效的目的也是如此。