得到一个日期表,某人属于这样的特定类别:
drop table if exists #category
create table #category (personid int, categoryid int, startdate datetime, enddate datetime)
insert into #category
select * from
(
select 1 Personid, 1 CategoryID, '01/04/2010' StartDate, '31/07/2016' EndDate union
select 1 Personid, 5 CategoryID, '07/08/2016' StartDate, '31/03/2019' EndDate union
select 1 Personid, 5 CategoryID, '01/04/2019' StartDate, '01/04/2019' EndDate union
select 1 Personid, 5 CategoryID, '02/04/2019' StartDate, '11/08/2019' EndDate union
select 1 Personid, 4 CategoryID, '12/08/2019' StartDate, '03/11/2019' EndDate union
select 1 Personid, 5 CategoryID, '04/11/2019' StartDate, '22/03/2020' EndDate union
select 1 Personid, 5 CategoryID, '23/03/2020' StartDate, NULL EndDate union
select 2 Personid, 1 CategoryID, '01/04/2010' StartDate, '09/04/2015' EndDate union
select 2 Personid, 4 CategoryID, '10/04/2015' StartDate, '31/03/2018' EndDate union
select 2 Personid, 4 CategoryID, '01/04/2018' StartDate, '31/03/2019' EndDate union
select 2 Personid, 4 CategoryID, '01/04/2019' StartDate, '23/06/2019' EndDate union
select 2 Personid, 4 CategoryID, '24/06/2019' StartDate, NULL EndDate
) x
order by personid, startdate
我试着压缩它,所以我得到这个:
PersonID | categoryid | 起始日期 | 结束日期 | 1 | 1 | 01/04/2010 | 31/07/2016 | 1
---|---|---|---|
5 | 07/08/2016 | 11/08/2019 | |
4 | 12/08/2019 | 03/11/2019 | |
5 | 04/11/2019 | 空 | |
1 | 01/04/2010 | 09/04/2015 | |
4 | 01/04/2015 | 空 |
这是一种缺口和孤岛问题。但是,如果您的数据完全平铺(没有间隙),就像您的示例数据一样,那么您就可以完全不进行任何聚合—这应该是最有效的方法:
select personid, categoryid, startdate,
dateadd(day, -1, lead(startdate) over (partition by personid order by startdate)) as enddate
from (select c.*,
lag(categoryid) over (partition by personid order by startdate) as prev_categoryid
from #category c
) c
where prev_categoryid is null or prev_categoryid <> categoryid;
where
子句只选择类别发生变化的行。然后lead()
获得下一个开始日期,并为您想要的enddate
减去1。