在SQL中对具有不同开始日期和结束日期的不同日期范围进行分组
我需要帮助在Microsoft SQL Server 18.1中对重叠的日期范围进行分组。数据示例如下所示。我需要能够根据ID，名称，StartDate和EndDate分组。应该这样做，如果ID 1的日期范围与ID 1的日期范围的下一行重叠或相距小于7天，则应为它们分配相同的分组ID。如果两行之间的时间间隔大于7天，则应将它们分成两组。
数据的特点是几乎所有行的Start和EndDate都不同，因此不能按Start和EndDate进行分组。相反，我们的目标是为一个人将日期范围重叠小于7天的所有行进行分组，并通过使用MIN和MAX显示整个期间的Start-和EndDate，如"desired output window“中所示。数据集中的每个人最多可以有200行不同的日期范围，如果与数据集中该人的其他日期范围重叠，则需要对这些日期范围进行分组。
我认为解决方案需要遍历所有行，直到所有行都根据ID、名称和重叠的日期范围进行分组。情况是周期1-4匹配分组，首先需要周期1匹配周期2，然后周期1-2匹配周期3，周期1-3匹配周期4，可以是周期1的日期范围(例如01-01-2019 - 30-05-2019)晚于周期2的日期范围(例如05-02-2019 - 24-04-2019)。其中，周期1-2与周期3的比较/匹配应该是周期1-2的最大EndDate，在这种情况下表示30-05-2019。
Period 1 Period 2 Period 3 Period 4
X
X
X
X我需要帮助做一个代码，让我从步骤0-原始数据到步骤1-按重叠日期范围分组(间隔不到7天)。我尝试过CASE、LAG、LEAD、PARTITION BY和一些不同类型的循环，但还没有找到解决问题的方法。
第0步-原始数据：
ID Name StartDate EndDate
1 Peter Hanson 01-01-2018 15-02-2019
1 Peter Hanson 05-01-2019 23-02-2019
1 Peter Hanson 30-02-2019 18-04-2019
2 Eric Schmidt 05-01-2019 18-03-2019
2 Eric Schmidt 07-01-2019 25-05-2019
3 Martin Boyle 08-03-2018 12-01-2019
3 Martin Boyle 15-01-2019 17-04-2019
3 Martin Boyle 18-04-2019 12-05-2019
3 Martin Boyle 29-04-2019 31-09-2019第1步-按重叠的日期范围分组(间隔少于7天)：
ID Name StartDate EndDate Grouping
1 Peter Hanson 01-01-2018 15-02-2019 1
1 Peter Hanson 05-01-2019 23-02-2019 1
1 Peter Hanson 30-02-2019 18-04-2019 2
2 Eric Schmidt 05-01-2019 18-03-2019 3
2 Eric Schmidt 07-01-2019 25-05-2019 3
3 Martin Boyle 08-03-2018 12-01-2019 4
3 Martin Boyle 23-01-2019 17-04-2019 5
3 Martin Boyle 18-04-2019 12-05-2019 5
3 Martin Boyle 29-04-2019 31-09-2019 5步骤2-所需的输出窗口：
ID Name StartDate EndDate Grouping
1 Peter Hanson 01-01-2019 23-02-2019 1
1 Peter Hanson 30-02-2019 18-04-2019 2
2 Eric Schmidt 05-01-2019 25-05-2019 3
3 Martin Boyle 08-03-2018 12-01-2019 4
3 Martin Boyle 23-01-2019 31-09-2019 5我希望有人能帮助我完成这项任务。
回答 1
Stack Overflow用户
发布于 2019-07-21 10:21:03
您想要确定一个组从哪里开始。根据您的描述，您可以使用lag() --虽然它允许完全重叠，但累积max()更合适。
然后，这些组是开始数的累积和。。。剩下的就是聚合：
select id, name, min(startdate), max(enddate),
dense_rank() over (order by id, min(startdate)) as grouping
from (select t.*,
sum(case when prev_enddate >= dateadd(day, -7, startdate) then 0 else 1 end /*end*/
) over (partition by id order by startdate) as grp
from (select t.*,
lag(enddate) over (partition by id order by startdate) as prev_enddate
from t
) t
) t
group by id, name;https://stackoverflow.com/questions/57128997
复制相似问题
腾讯云开发者
