时间戳

首页 > 解决方案 > 检查两个无限区间是否重叠

sql-server - 检查两个无限区间是否重叠

问题描述

我有一个表，其中存储了有关任务执行间隔的信息。每个任务都有一个起始年份和执行间隔。例如从 2020 年开始，间隔 = 2 年表示任务将在 2020、2022、2024 年执行，依此类推。

如何检查表中是否存在重叠间隔？

ID  |   StartYear |    Interval
____|_____________|___________
1   |   2020      |    3
2   |   2019      |    4
3   |   2020      |    5
4   |   2019      |    2
5   |   2020      |    2

结果：任务1和2重叠（2023年第一次），任务2和3重叠（2035年第一次），任务1和3重叠（2020年第一次，然后2035年），任务4和5不重叠（一个总是在偶数年，另一个在不均匀年）等等。

所以在数学上，我想知道如何检查两个无限集是否不相交。我的问题是，我什至不知道如何用我在 MS-SQL 中的数据来表示一个无限集。

最终结果将是一个查询（或函数），您可以将起始年份和间隔作为返回所有重叠条目的参数。

示例 1

给定 2020 年的起始年份和 2 的间隔，要比较的集合将是 2020,2022,2024,2026 等。这就是为什么在结果中没有选择 ID 2 和 4，因为它们都从不平衡的年份开始，并且间隔是偶数，意味着执行年份总是不均匀的年份，不在2020、2022、2024等的比较集中。

Declare @CompareStart int = 2020;
Declare @CompareInterval int = 2;

SELECT * FROM MYTABLE WHERE OVERLAPPING(@CompareStart, @CompareInterval)

结果将是：

ID  |   StartYear |    Interval
____|_____________|___________
1   |   2020      |    3
3   |   2020      |    5
5   |   2020      |    2

示例 2

对于 2019 年的起始年份和 3 的间隔，结果集将是 ID 为 2（2019 年首次点击）、3（2025 年首次点击）、4（2019 年首次点击）和 5（2022 年首次点击）的行。

Declare @CompareStart int = 2019;
Declare @CompareInterval int = 3;

SELECT * FROM MYTABLE WHERE OVERLAPPING(@CompareStart, @CompareInterval)

结果将是：

ID  |   StartYear |    Interval
____|_____________|___________
2   |   2019      |    4
3   |   2020      |    5
4   |   2019      |    2
5   |   2020      |    2

标签： sql-server

解决方案

鉴于此示例数据：

CREATE TABLE #t(ID int PRIMARY KEY, StartYear int, Interval int);

INSERT #t(ID, StartYear, Interval) VALUES
(1,2020,3),
(2,2019,4),
(3,2020,5),
(4,2019,2),
(5,2020,2);

我们可以使用两个递归 CTE，一个用于基于起始年份和定义的比较间隔的完整比较日期集，另一个用于基于其起始年份和执行间隔为每个 ID 获取表中的所有日期。一旦我们有了这两组，我们就可以在它们相交的地方加入它们。使用输入参数并取消注释该FirstHit_ForSanityCheck行以确保对于任何给定的年份/间隔，第一个“命中”被正确识别。

-- input params
DECLARE @StartYear       int = 2019, 
        @CompareInterval int = 3;
        
-- local variables
DECLARE @LimitOfRange int = 100; -- to keep recursion <= 100!

-- recursive CTE to derive full set of comparison dates
;WITH CompareDates(Lvl, TheYear) AS
(
  SELECT Lvl = 0, @StartYear
  UNION ALL
  SELECT Lvl + 1, @StartYear + (Lvl + 1)*@CompareInterval
  FROM CompareDates
  WHERE Lvl < @LimitOfRange / @CompareInterval
),
-- to derive set of execution dates for each ID
TableDates AS
(
  SELECT ID, Lvl = 0, TheYear = StartYear, Interval
  FROM #t
  UNION ALL
  SELECT ID, Lvl + 1, TheYear + (Lvl + 1)*Interval, Interval
  FROM TableDates
  WHERE Lvl < @LimitOfRange / Interval
)
SELECT td.ID, 
       TheYear = MIN(td.TheYear), 
       td.Interval
       --, FirstHit_ForSanityCheck = MIN(cd.TheYear)
FROM CompareDates AS cd
INNER JOIN TableDates AS td
ON cd.TheYear = td.TheYear
GROUP BY td.ID, td.Interval
ORDER BY td.ID;

输出：

ID   StartYear   Interval
--   ---------   --------
2    2019        4
3    2020        5
4    2019        2
5    2020        2

工作小提琴。

推荐阅读