如何在 SQL 中将日期范围与小间隙组合在一起

Question

>我有一个数据集，其中每行都有一个日期范围。如果记录重叠或间隔少于 30 天，并且它们共享相同的 ID 号，我想将记录合并到单个日期范围内。如果超过 30 天，我希望它们保持分离。如果它们重叠，我可以弄清楚该怎么做，无论差距有多大，我都可以弄清楚如何做到这一点，但我无法弄清楚如何在有限的间隙津贴下做到这一点。

因此，例如，如果我的数据如下所示：

ID        Date1           Date2
ABC       2018-01-01      2018-02-14
ABC       2018-02-13      2018-03-17
ABC       2018-04-01      2018-07-24
DEF       2017-01-01      2017-06-30
DEF       2017-10-01      2017-12-01

我希望它像这样出来：

ID        Date1           Date2
ABC       2018-01-01      2018-07-24
DEF       2017-01-01      2017-06-30
DEF       2017-10-01      2017-12-01

ABC 的三个日期范围合并在一起，因为它们要么重叠，要么间隔小于 30 天。DEF 的两个日期范围保持独立，因为它们之间的间隔大于 30 天。

我正在使用Microsoft SSMS。

Answer 1

您可以确定新周期的开始位置。 对于一般问题，我会选择not exists. 然后，您可以使用累积总和分配组：

select id, sum(is_start) over (partition by id order by datestart) as grp
from (select t.*,
(case when not exists (select 1
from t t2
where t2.id = t.id and
t2.date1 >= dateadd(day, -30, t1.date1) and
t2.date2 < dateadd(day, 30, t1.date2)
)
then 1 else 0
end) as is_start
from t
) t;

最后一步是聚合：

with g as (
select id, sum(is_start) over (partition by id order by datestart) as grp
from (select t.*,
(case when not exists (select 1
from t t2
where t2.id = t.id and
t2.date1 >= dateadd(day, -30, t1.date1) and
t2.date2 < dateadd(day, 30, t1.date2)
)
then 1 else 0
end) as is_start
from t
) t
)
select id, min(date1), max(date2)
from g
group by id, grp;