从连续范围获取第一个和最后一个值

Question

我想获取连续enter_day的第一个enter_date和最后一个leave_date，以及每个id的leave_day值。给定此示例数据：

+-----+------------+------------+-----------+-----------+
| id  | enter_date | leave_date | enter_day | leave_day |
+-----+------------+------------+-----------+-----------+
| 111 | 2016-07-29 | 2016-12-01 |         1 |        75 |
| 111 | 2016-12-02 | 2017-01-13 |        76 |        95 |
| 111 | 2017-01-17 | 2017-06-02 |        96 |       181 |
| 222 | 2016-07-29 | 2016-12-02 |         1 |        76 |
| 222 | 2017-01-30 | 2017-06-02 |       105 |       181 |
| 333 | 2016-08-01 | 2017-06-02 |         1 |       180 |
+-----+------------+------------+-----------+-----------+

我想要以下结果：

+-----+------------+------------+
| id  | enter_date | leave_date |
+-----+------------+------------+
| 111 | 2016-07-29 | 2017-06-02 |
| 222 | 2016-07-29 | 2016-12-02 |
| 222 | 2017-01-30 | 2017-06-02 |
| 333 | 2016-08-01 | 2017-06-02 |
+-----+------------+------------+

我想要 ID 111 的一条记录，因为任何enter_day和之前的leave_day之间都没有间隙。

我想要 ID 222 的两条记录，因为存在间隙(第 75 天到第 104 天)。

编辑：到目前为止，我所拥有的，没有给我ID 111的正确leave_date：

with cte as (
select a.id, a.enter_date, a.leave_date, b.enter_date next_ed, b.leave_date next_ld
from #tbl a
join #tbl b on b.id = a.id and b.enter_day = a.leave_day + 1
)
select id, min(enter_date) enter_date, max(leave_date) leave_date
from cte
group by id
union
select a.id, a.enter_date, a.leave_date
from #tbl a
left join #tbl b on b.id = a.id and b.enter_day = a.leave_day + 1
left join cte c on c.id = a.id and c.next_ed = a.enter_date and c.next_ld = a.leave_date
where b.id is null and c.id is null
order by 1,3

Answer 1

下面是范围的间隙和岛屿示例。

我使用了一个临时计数表，但实际的数字/计数也可以解决问题。

如果您只运行内部查询，您将很快看到 6 行的示例数据如何分解为 514 行。 然后是应用分组聚合以获得最终结果的小问题。

例

Declare @YourTable Table ([id] int,[enter_date] date,[leave_date] date,[enter_day] int,[leave_day] int)
Insert Into @YourTable Values
(111,'2016-07-29','2016-12-01',1,75)
,(111,'2016-12-02','2017-01-13',76,95)
,(111,'2017-01-17','2017-06-02',96,181)
,(222,'2016-07-29','2016-12-02',1,76)
,(222,'2017-01-30','2017-06-02',105,181)
,(333,'2016-08-01','2017-06-02',1,180)
Select ID
,[enter_date] = min([enter_date])
,[leave_date] = max([leave_date])
From (
Select *
,Grp = N - Row_Number() over (Partition By ID Order by N)
From @YourTable A
Join (
Select Top (Select max([leave_day]-[enter_day])+1 From @YourTable)
N=-1+Row_Number() Over (Order By (Select Null))
From  master..spt_values n1,master..spt_values n2
) B on B.N between [enter_day] and [leave_day]
) A 
Group By [ID],Grp
Order By [ID],min([enter_date])

返回

ID  enter_date  leave_date
111 2016-07-29  2017-06-02
222 2016-07-29  2016-12-02
222 2017-01-30  2017-06-02
333 2016-08-01  2017-06-02