已经讨论了有缝隙和岛屿的时间跨度的计算. 但是,目前存在的不同时间跨度模式又如何呢?
目前的数据是时间跨度和连续时间跨度之间的差距的组合.它们有不同的形式可供 Select .
ID
1具有5个时间跨度,其中4个位于从2000-08-08
到2019-03-31
的第一个时间跨度内,但是对于ID
2,时间跨度以这样的方式存在,即下一行中的下一个时间跨度落在下一行的结束日期内.ID 3
在第一个结束日期和下一个开始日期之间存在差距.
我的代码只适用于ID
、2和3.
我怎么才能找到ID
1的时间跨度呢?
在这种情况下,我的代码不能工作.
问题出现在ID
1中,因为代码不能通过1次迭代来解决它,因为数据以不同的方式呈现.
id | startdate | enddate |
---|---|---|
1 | 2000-08-08 | 2019-03-31 |
1 | 2007-06-08 | 2007-09-09 |
1 | 2008-02-08 | 2011-08-04 |
1 | 2012-08-01 | 2012-09-20 |
1 | 2018-02-01 | 2019-03-31 |
2 | 2013-02-18 | 2019-03-28 |
2 | 2018-04-12 | 2020-07-28 |
3 | 2015-01-11 | 2015-04-02 |
3 | 2016-02-08 | 2021-11-22 |
我的代码是:
WITH cte1 AS (
SELECT
id,
startdate,
enddate,
CASE
WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
ELSE 1
END AS new_grp
FROM tab1
), cte2 AS (
SELECT
cte1.*,
SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
FROM cte1
)
SELECT
id,
MIN(startdate) AS startdate,
MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;
但是,当我想要推导出ID
1的时间跨度时,它确实需要4次迭代--所以classic 的间隙和孤岛方法是不可行的.
我期望的是:
id | startdate | enddate |
---|---|---|
1 | 2000-08-08 | 2019-03-31 |
2 | 2013-02-18 | 2020-07-28 |
3 | 2015-01-11 | 2015-04-02 |
3 | 2016-02-08 | 2021-11-22 |