当所有日期范围同时重叠时，获取所有重叠的日期范围

Question

我已经苦思冥想了几天……试图编写一个SQL查询，以获取所有单位同时重叠的日期范围。最好通过图形呈现。

以下是简化表格和参考图像：

UnitId  Start       End
======  ==========  ==========
1       05/01/2018  09/01/2018
1       10/01/2018  13/01/2018
2       04/01/2018  15/01/2018
2       19/01/2018  23/01/2018
3       06/01/2018  12/01/2018
3       14/01/2018  22/01/2018

期望结果：

Start       End
======      ========== 
06/01/2018  09/01/2018
10/01/2018  12/01/2018

我现在所拥有的：

DECLARE @sourceTable TABLE (UnitId int, StartDate datetime, EndDate datetime);
INSERT INTO @sourceTable VALUES
 (1, '2018-01-05', '2018-01-09')
,(1, '2018-01-10', '2018-01-13')
,(2, '2018-01-04', '2018-01-15')
,(2, '2018-01-19', '2018-01-23')
,(3, '2018-01-06', '2018-01-12')
,(3, '2018-01-14', '2018-01-22');

SELECT DISTINCT
 (SELECT max(v) FROM (values(A.StartDate), (B.StartDate)) as value(v)) StartDate
,(SELECT min(v) FROM (values(A.EndDate), (B.EndDate)) as value(v)) EndDate
FROM @sourceTable A 
JOIN @sourceTable B 
ON A.startDate <= B.endDate AND A.endDate >= B.startDate AND A.UnitId != B.UnitId

- Ondrej

---

你能展示一下你目前尝试过的SQL语句吗？这样我们可以帮助你进行优化。另外，你能否创建一个SQL Fiddle，以便我们更好地帮助你呢？ - Simon Price

这里有一些帮助... https://developer42.wordpress.com/2016/12/19/filtering-on-a-date-window/ - JohnLBevan

Answer 1

我认为这是一个“重叠区间计数”问题（这张图片应该会有所帮助）。以下是其中一种解决方法：

DECLARE @t TABLE (UnitId INT, [Start] DATE, [End] DATE);
INSERT INTO @t VALUES
(1, '2018-01-05', '2018-01-09'),
(1, '2018-01-10', '2018-01-13'),
(2, '2018-01-04', '2018-01-15'),
(2, '2018-01-19', '2018-01-23'),
(3, '2018-01-06', '2018-01-12'),
(3, '2018-01-14', '2018-01-22');

WITH cte1(date, val) AS (
    SELECT [Start], 1 FROM @t AS t
    UNION ALL
    SELECT [End], 0 FROM @t AS t
    UNION ALL
    SELECT DATEADD(DAY, 1, [End]), -1 FROM @t AS t
), cte2 AS (
    SELECT date, SUM(val) OVER (ORDER BY date, val) AS usage
    FROM cte1
)
SELECT date, MAX(usage) AS usage
FROM cte2
GROUP BY date

它将为您提供所有使用计数（可能）更改的日期列表：

date          usage
2018-01-04    1
2018-01-05    2
2018-01-06    3
2018-01-09    3
2018-01-10    3
2018-01-12    3
2018-01-13    2
2018-01-14    2
2018-01-15    2
2018-01-16    1
2018-01-19    2
2018-01-22    2
2018-01-23    1
2018-01-24    0

采用这种方法，您无需使用日历表或rCTE来生成缺失日期。将上述内容转换为范围 (2018-01-05 ... 2018-01-15, 2018-01-19 ... 2018-01-22 等) 并不是很难。

DECLARE @t TABLE (UnitId INT, [Start] DATE, [End] DATE);
INSERT INTO @t VALUES
(1, '2018-01-05', '2018-01-09'),
(1, '2018-01-10', '2018-01-13'),
(2, '2018-01-04', '2018-01-15'),
(2, '2018-01-19', '2018-01-23'),
(3, '2018-01-06', '2018-01-12'),
(3, '2018-01-14', '2018-01-22');

WITH cte1(date, val) AS (
    SELECT [Start], 1 FROM @t AS t                 -- starting date increments counter
    UNION ALL                                      
    SELECT [End], 0 FROM @t AS t                   -- we need all edges in the result
    UNION ALL                                      
    SELECT DATEADD(DAY, 1, [End]), -1 FROM @t AS t -- end date + 1 decrements counter
), cte2 AS (
    SELECT date, SUM(val) OVER (ORDER BY date, val) AS usage -- running sum for counter
    FROM cte1
), cte3 AS (
    SELECT date, MAX(usage) AS usage -- group multiple events on same date together
    FROM cte2
    GROUP BY date
), cte4 AS (
    SELECT date, usage, CASE
        WHEN usage > 1 AND LAG(usage) OVER (ORDER BY date) > 1 THEN 0
        WHEN usage < 2 AND LAG(usage) OVER (ORDER BY date) < 2 THEN 0
        ELSE 1
    END AS chg -- start new group if prev and curr usage are on opposite side of 1
    FROM cte3
), cte5 AS (
    SELECT date, usage, SUM(chg) OVER (ORDER BY date) AS grp -- number groups for each change
    FROM cte4
)
SELECT MIN(date) date1, MAX(date) date2
FROM cte5
GROUP BY grp
HAVING MIN(usage) > 1

结果：

date1         date2
2018-01-05    2018-01-15
2018-01-19    2018-01-22

- Salman A

Answer 2

你正在寻找所有单位重叠的日期范围。因此，寻找所有单位存在的开始日期和所有单位存在的结束日期，然后将它们连接起来。

我使用ROW_NUMBER将第一个开始日期与第一个结束日期相匹配，第二个开始日期与第二个结束日期相匹配，依此类推。

select s.startdate, e.enddate
from
(
  select startdate, row_number() over (order by startdate) as rn
  from @sourceTable s1
  where 
  (
    select count(*)
    from @sourceTable s2
    where s1.startdate between s2.startdate and s2.enddate
  ) = (select count(distinct unitid) from @sourceTable)
) s
join
(
  select enddate, row_number() over (order by startdate) as rn
  from @sourceTable s1
  where 
  (
    select count(*)
    from @sourceTable s2
    where s1.enddate between s2.startdate and s2.enddate
  ) = (select count(distinct unitid) from @sourceTable)
) e on e.rn = s.rn
order by s.startdate;

可能有更优雅的解决方法，但我想这个查询至少很容易理解 :-)

Rextester演示：https://rextester.com/GRRSW89045

- Thorsten Kettner