在T中查找最大的时间重叠
在T中查找最大的时间重叠
提问于 2015-03-24 13:45:16
我正在Server 2008 R2上尝试这样做。
我有一张四栏的桌子：
parent_id INT
child_id INT
start_time TIME
end_time TIME您应该将子进程视为为父程序运行的子进程。所有这些子进程每天运行一次，每个子进程在给定的时间范围内运行。我希望根据其子进程的时间间隔，为每个父进程找到最大的时间间隔，也就是说，我想知道所有子进程都在运行的时间间隔最长。每天重复每一次时间间隔意味着，即使儿童的时间间隔为午夜(即23:00-10:00)，它也可能与只在上午运行的儿童(即07:00-09:00)重叠，因为即使他们不重叠在“第一天”，也会在随后的所有日子重叠。
输出应该如下所示：
parent_id INT
start_time TIME
end_time TIME
valid BIT如果发现重叠，则为valid = 1，如果没有重叠，则为valid = 0。
一些重要的信息：
- 一个时间间隔可以跨越午夜，即
start_time = 23:00和end_time = 03:00，时间间隔为4小时。 - 两个时间间隔可能重叠在两个不同的地方，即
start_time1 = 13:00，end_time1 = 06:00，start_time2 = 04:00，end_time2 = 14:00。这将使最大重叠时间为04:00-06:00=2小时。 - 对于给定父的子代来说，可能没有共同的重叠，在这种情况下，该父父的输出将是
start_time = NULL、end_time = NULL和valid = 0。 - 如果一个子间隔持续一整天，那么
start_time = NULL和end_time = NULL。选择这样做是为了避免将重叠时间定为00:00-24:00，而不是一天(23:00-0-0 4:00)，而不是一天(23:00-0 4:00)。 - 如果时间间隔由父级的所有子级共享，则重叠仅为重叠。
- 一个孩子的时间不能超过24小时。
举个例子：
parent_id child_id start_time end_time
1 1 06:00 14:00
1 2 13:00 09:00
1 3 07:00 09:00
2 1 12:00 17:00
2 2 09:00 11:00
3 1 NULL NULL
3 2 23:00 04:00
4 1 NULL NULL
4 2 NULL NULL
10 1 06:11 14:00
10 2 06:00 09:00
10 3 05:00 08:44
11 1 11:38 17:00
11 2 09:02 12:11这些数据将产生以下结果集：
parent_id start_time end_time valid
1 07:00 09:00 1
2 NULL NULL 0
3 23:00 04:00 1
4 NULL NULL 1
10 06:11 08:44 1
11 11:38 12:11 1父级的重叠是其所有子级共享的时间间隔。因此，父母亲10的重叠是通过查找所有3个孩子共享时间的重叠来发现的:子代1 (06:11-14:00)和2 (06:00-09:00)从06:11到09:00重叠。然后，将此重叠时间间隔应用于子3 (05:00- 08:44 )，这将提供06:11至08:44的重叠，因为该间隔是所有3个孩子共享共同时间的唯一间隔。
我希望这是合理的。
我可以用游标来做这件事，但是我真的更喜欢避免游标。在没有游标的情况下，我一直在绞尽脑汁地思考该如何做这件事，但我做得不够。没有游标就行了吗？
编辑:扩展第4条的文本，解释让一整天为空，而不是00:00至00:00的决定。编辑:用另外两种情况扩展了示例。新案例有父ID 10和11。编辑:插入解释如何找到父10的重叠。编辑:澄清了第3条。增加了第5条和第6条。详细说明了这一切。
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-03-24 15:59:19
根据你的问题，我认为你的产出应该是：
parent_id start_time end_time valid
1 07:00 09:00 1
2 NULL NULL 0
3 23:00 04:00 1
4 NULL NULL 1
10 06:11 08:44 1
11 11:38 12:11 1下面是一个基于集合的解决方案：
DECLARE @Times TABLE
(
parent_id INT
,child_id INT
,start_time TIME
,end_time TIME
);
INSERT INTO @Times
VALUES
(1, 1, '06:00', '14:00')
,(1, 2, '13:00', '09:00')
,(1, 3, '07:00', '09:00')
,(2, 1, '12:00', '17:00')
,(2, 2, '09:00', '11:00')
,(3, 1, NULL, NULL)
,(3, 2, '23:00', '04:00')
,(4, 1, NULL, NULL)
,(4, 2, NULL, NULL)
,(10, 1, '06:11', '14:00')
,(10, 2, '06:00', '09:00')
,(10, 3, '05:00', '08:44')
,(11, 1, '11:38', '17:00')
,(11, 2, '09:02', '12:11');
DECLARE @Parents TABLE
(
parent_id INT PRIMARY KEY
,ChildCount INT
)
INSERT INTO @Parents
SELECT
parent_id
,COUNT(DISTINCT child_id) AS ChildCount
FROM
@Times
GROUP BY
parent_id
DECLARE @StartTime DATETIME2 = '00:00'
DECLARE @MinutesInTwoDays INT = 2880
DECLARE @Minutes TABLE(ThisMinute DATETIME2 PRIMARY KEY);
WITH
MinutesCTE AS
(
SELECT
1 AS MinuteNumber
,@StartTime AS ThisMinute
UNION ALL
SELECT
NextMinuteNumber
,NextMinute
FROM MinutesCTE
CROSS APPLY (VALUES(MinuteNumber+1,DATEADD(MINUTE,1,ThisMinute))) NextDates(NextMinuteNumber,NextMinute)
WHERE
NextMinuteNumber <= @MinutesInTwoDays
)
INSERT INTO @Minutes
SELECT ThisMinute FROM MinutesCTE M OPTION (MAXRECURSION 2880);
DECLARE @SharedMinutes TABLE
(
ThisMinute DATETIME2
,parent_id INT
,UNIQUE(ThisMinute,parent_id)
);
WITH TimesCTE AS
(
SELECT
Times.parent_id
,Times.child_id
,CAST(ISNULL(Times.start_time,'00:00') AS datetime2) AS start_time
,
DATEADD
(
DAY
,
CASE
WHEN Times.end_time IS NULL THEN 2
WHEN Times.start_time > Times.end_time THEN 1
ELSE 0
END
,CAST(ISNULL(Times.end_time,'00:00') AS datetime2)
) as end_time
FROM
@Times Times
UNION ALL
SELECT
Times.parent_id
,Times.child_id
,DATEADD(DAY,1,CAST(Times.start_time as datetime2)) AS start_time
,DATEADD(DAY,1,CAST(Times.end_time AS datetime2)) AS end_time
FROM
@Times Times
WHERE
start_time < end_time
)
--Get minutes shared by all children of each parent
INSERT INTO @SharedMinutes
SELECT
M.ThisMinute
,P.parent_id
FROM
@Minutes M
JOIN
TimesCTE T
ON
M.ThisMinute BETWEEN start_time AND end_time
JOIN
@Parents P
ON T.parent_id = P.parent_id
GROUP BY
M.ThisMinute
,P.parent_id
,P.ChildCount
HAVING
COUNT(DISTINCT T.child_id) = P.ChildCount
--get results
SELECT
parent_id
,CAST(CASE WHEN start_time = '1900-01-01' AND end_time = '1900-01-02 23:59' THEN NULL ELSE start_time END AS TIME) AS start_time
,CAST(CASE WHEN start_time = '1900-01-01' AND end_time = '1900-01-02 23:59' THEN NULL ELSE end_time END AS TIME) AS end_time
,valid
FROM
(
SELECT
P.parent_id
,MIN(ThisMinute) AS start_time
,MAX(ThisMinute) AS end_time
,CASE WHEN MAX(ThisMinute) IS NOT NULL THEN 1 ELSE 0 END AS valid
FROM
@Parents P
LEFT JOIN
@SharedMinutes SM
ON P.parent_id = SM.parent_id
GROUP BY
P.parent_id
) Results您可能会发现，您在问题中概述的迭代算法会更有效。但是，如果采用这种方法，我将使用WHILE循环而不是游标。
Stack Overflow用户
发布于 2015-03-24 15:49:58
这可能是实现所需结果的一种非常冗长的方法，但它适用于给定的数据集，尽管它应该使用更大的数据进行测试。
我只是将表加入到parent_id匹配和child_id不同的表中，以获得可能重叠的所有时间组合，然后在过滤和分组输出之前执行一些DATEDIFF来计算差异。
如果需要，您可以隔离地运行下面的代码来进行测试和调整：
-- setup initial table
CREATE TABLE #OverlapTable
(
[parent_id] INT ,
[child_id] INT ,
[start_time] TIME ,
[end_time] TIME
);
-- insert dummy data
INSERT INTO #OverlapTable
( [parent_id], [child_id], [start_time], [end_time] )
VALUES ( 1, 1, '06:00', '14:00' ),
( 1, 2, '13:00', '09:00' ),
( 1, 3, '07:00', '09:00' ),
( 2, 1, '12:00', '17:00' ),
( 2, 2, '09:00', '11:00' ),
( 3, 1, NULL, NULL ),
( 3, 2, '23:00', '04:00' ),
( 4, 1, NULL, NULL ),
( 4, 2, NULL, NULL );
-- insert all combinations into a new temp table #Results with overlap calculations
SELECT *
INTO #Results
FROM ( SELECT t1.parent_id ,
t1.start_time ,
t1.end_time ,
t2.start_time AS t2_start_time ,
t2.end_time AS t2_end_time ,
CASE WHEN t1.start_time IS NULL
AND t1.end_time IS NULL THEN 0
WHEN t1.start_time BETWEEN t2.start_time
AND t2.end_time
THEN DATEDIFF(HOUR, t1.start_time, t2.end_time)
WHEN t1.end_time BETWEEN t2.start_time AND t2.end_time
THEN DATEDIFF(HOUR, t2.start_time, t1.end_time)
ELSE NULL
END AS Overlap
FROM #OverlapTable t1
INNER JOIN #OverlapTable t2 ON t2.parent_id = t1.parent_id
AND t2.child_id != t1.child_id
) t
-- SELECT * FROM #Results -- this shows intermediate results
-- filter and group results with the largest overlaps and handle other cases
SELECT DISTINCT
r.parent_id ,
CASE WHEN r.Overlap IS NULL THEN NULL
ELSE CASE WHEN r.start_time IS NULL THEN r.t2_start_time
ELSE r.start_time
END
END start_time ,
CASE WHEN r.Overlap IS NULL THEN NULL
ELSE CASE WHEN r.end_time IS NULL THEN r.t2_end_time
ELSE r.end_time
END
END end_time ,
CASE WHEN r.Overlap IS NULL THEN 0
ELSE 1
END Valid
FROM #Results r
WHERE EXISTS ( SELECT parent_id ,
MAX(Overlap)
FROM #Results
WHERE r.parent_id = parent_id
GROUP BY parent_id
HAVING MAX(Overlap) = r.Overlap
OR ( MAX(Overlap) IS NULL
AND r.Overlap IS NULL
) )
DROP TABLE #Results
DROP TABLE #OverlapTable希望这能有所帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29234284
复制相关文章
相似问题
腾讯云开发者
