查询并发时间跨度的最大数量 [英] Query for maximum number of concurrent time spans
本文介绍了查询并发时间跨度的最大数量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
CnxStartdatetime ， CnxEnddatetime ）。每行表示信息的传输。 我试图找到基于这两个时间戳的并发传输的最大数量。我有一个工作的查询，但它既缓慢又极其麻烦。我知道必须有一个更好的方法来解决这个问题，但是不能想出来。 对于当前版本，如果我用5级并得到结果我必须返回并添加一吨SQL来测试是否有6个并发传输的实例等等。一旦查询获得7-8级别深度就会变得非常慢。
当前版本的代码段：
select
t1.id，t2.id， t3.id，t4.id，t5.id，t6.id，t7.id，t8.id，t9.id，t10.id
FROM
dbo.MyTable t1，dbo .MyTable t2，dbo.MyTable t3，dbo.MyTable t4，dbo.MyTable t5，
dbo.MyTable t6，dbo.MyTable t7，dbo.MyTable t8，dbo.MyTable t9，dbo.MyTable t10
WHERE
（（（t2.cnxstartdatetime> = t1.cnxstartdatetime）和（t2.cnxstartdatetime< = t1.cnxenddatetime））
或（（t2.cnxenddatetime> = t1.cnxstartdatetime）和（t2.cnxenddatetime< = t1.cnxenddatetime））
AND
t2.id！= t1.id
AND
（（（t3.cnxstartdatetime> = t2） cnxstartdatetime）和（t3.cnxstartdatetime> = t1.cnxstartdatetime）和（t3.cnxstartdatetime< = t1.cnxenddatetime）和（t3.cnxstartdatetime< = t2.cnxenddatetime））
或（（t3.cnxenddatetime> = t2 .cnxstartdatetime）和（t3.cnxenddatetime> = t1.cnxstartdatetime）和（t3.cnxenddatetime< = t1.cnxenddatetime）和（t3.cnxenddatetime< = t2.cnxenddatetime））
AND
t3.id！= t2.id AND t3.id！= t1.id
AND
（（（t4.cnxstartdatetime> = t3.cnxstartdatetime））和（t4.cnxstartdatetime> = t1.cnxstartdatetime ）和（t4.cnxstartdatetime> = t2.cnxstartdatetime）和（t4.cnxstartdatetime< = t1.cnxenddatetime）和（t4.cnxstartdatetime< = t3.cnxenddatetime）和（t4.cnxstartdatetime< = t2.cnxenddatetime））
或（（t4.cnxenddatetime> = t3.cnxstartdatetime）和（t4.cnxenddatetime> = t1.cnxstartdatetime）和（t4.cnxenddatetime> = t2.cnxstartdatetime）和（t4.cnxenddatetime< = t1 .cnxenddatetime）和（t4.cnxenddatetime< = t3.cnxenddatetime）和（t4.cnxenddatetime< = t2.cnxen ddatetime）））
AND
t4.id！= t3.id AND t4.id！= t2.id AND t4.id！= t1.id
... * snip *
修改回应建议我使用交叉连接。这没有达到我正在寻找的结果。以下是一个记录重叠的交叉连接的结果示例。这是它给我的ID 11787 的列表如您所见， 11781 不<强>重叠 11774 这只是其时间跨度相交的任何记录的列表 11787
11774 2011-04-29 01：02：56.780 2011-04-29 01：02：58.793
11777 2011-04-29 01： 02：56.780 2011-04-29 01：02：58.843
11778 2011-04-29 01：02：56.780 2011-04-29 01：02：58.950
11775 2011-04-29 01： 02：56.793 2011-04-29 01：02：58.843
11776 2011-04-29 01：02：56.793 2011-04-29 01：02：58.890
11780 2011-04-29 01： 02：58.310 2011-04-29 01：03：02.687
11779 2011-04-29 01：02：58.327 2011-04-29 01：03：02.543
11787 2011-04-29 01： 02：58.530 2011-04-29 01：03：08.827 **
11781 2011-04-29 01：02：59.030 2011-04-29 01：03：05.187
11782 2011-04-29 01：02：59.247 2011-04-29 01：03：05.467
11784 2011-04-29 01：02：59.293 2011-04-29 01：03：05.810
11791 2011-04-29 01： 03：00.107 2011-04-29 01：03：13.623
11786 2011-04-29 01：03：00.843 2011-04-29 01：03：08.983
11783 2011-04-29 01： 03：02.560 2011-04-29 01：03：05.793
11785 2011-04-29 01：03：02.717 2011-04-29 01：03：07.357
11790 2011-04-29 01： 03：05.200 2011-04-29 01：03：14.153
11804 2011-04-29 01：03：05.687 2011-04-29 01：03：25.577
11811 2011-04-29 01： 03：07.093 2011-04-29 01：03：35.153
11799 2011-04-29 01：03：07.123 2011-04-29 01：03：24.437
11789 2011-04-29 01： 03：08.793 2011-04-29 01：03：13.577
我也试图用递归编写CTE，但是我无法确定如何确保当前的 ID 与以前的 ID 在当前并发堆栈中。
WITH TransmissionConcurrency（StartTime，EndTime，ConcurrencyLevel）AS
（
SELECT
CnxStartDatetime AS StartTime，
CnxEndDatetime AS EndTime，
1 AS ConcurrencyLevel
FROM dbo.MyTable
UNION ALL
SELECT
CASE WHEN d.CnxStartDatetime> tc.StartTime THEN d.CnxStartDatetime ELSE tc.StartTime END AS StartTime，
CASE WHEN d.CnxEndDatetime< tc.EndTime THEN d.CnxEndDatetime ELSE tc.EndTime END AS EndDate，
tc.ConcurrencyLevel + 1作为ConcurrencyLevel
FROM dbo.MyTable d
INNER JOIN TransmissionConcurrency tc ON
（（d.CnxStartDatetime between tc.StartTime和tc.EndTime）
或
（tc.StartTime和tc.EndTime之间的d.CnxEndDatetime）
或
（d.CnxStartDatetime< = tc.StartTime和d.CnxEndDa bt
SELECT *
FROM TransmissionConcurrency
ORDER BY ConcurrencyLevel，StartTime，EndTime
我已经找到了下图，试图更好地解释我正在寻找什么for。
A [--------]
B [-----]
C [------]
D [---]
E [---]
F [ - ]
在这种情况下，交叉连接方法会告诉我最大的并发性为code> A 是6（ A 与 B，C，D，E和F A 与 B，F 或 A with C，E ）
解决方案
declare @T table（ID int，Starts datetime，Ends datetime）
insert into @T（ID，Starts，Ends ）值
（1，'2000-12-30'，'2000-12-31'），
（2，'2001-01-01'，'2001-01-10'），
（3，'2001-01-02'，'2001-01-05'），
（4，'2001-01-03'，'2001-01-04'），
（5，'2001-01-05'，'2001-01-10'）
选择T1.ID，count（*）作为级别
从@T作为T1
cross join @T作为T2
其中
T1.Starts< T2.Ends和
T1.Starts> T2.Starts
group by T1.ID
选择top 1 T1.ID，count（*）as Levels
from @T as T1
cross join @T作为T2
其中
T1.Starts< T2.Ends和
T1.Starts> T2.Starts
group by T1.ID
order by count（*）desc
结果
ID级别
----------- ------ -----
3 1
4 2
5 1
（3行受影响）
ID级别
----------- -----------
4 2
如果你想要涉及的行可以使用这个：
选择T2 。*
从（选择前1个T1.ID
从@T作为T1
交叉连接@T作为T2
其中
T1.Starts< T2.Ends和
T1.Starts> T2.Starts
group by T1.ID
order by count（*）desc）as C
inner join @T as T1
在C.ID = T1.ID
内部连接@T作为T2
在T1.Starts< T2.Ends和
T1.Starts> T2.Starts或
T2.ID = C.ID
结果：
ID开始结束
----------- ------------ ----------- -----------------------
2 2001-01-01 00：00：00.000 2001 -01-10 00：00：00.000
3 2001-01-02 00：00：00.000 2001-01-05 00：00：00.000
4 2001-01-03 00：00：00.000 2001 -01-04 00：00：00.000
I have a SQL Server table with two datetime fields (CnxStartdatetime, CnxEnddatetime). Each row represents a transmission of information. I am trying to find the maximum number of concurrent transmissions based on these two timestamps. I have a working query but it is both slow and extremely cumbersome. I know there must be a better way to go about this but can't come up with any.
For the current version, if I run it with 5 "levels" and get results I have to go back and add a ton of SQL to test if there are instances of 6 concurrent transmissions, etc. Once the query gets 7-8 "levels" deep it becomes very slow.
Snippet of current version:
select
t1.id, t2.id, t3.id, t4.id, t5.id, t6.id, t7.id, t8.id, t9.id, t10.id
FROM
dbo.MyTable t1, dbo.MyTable t2, dbo.MyTable t3, dbo.MyTable t4, dbo.MyTable t5,
dbo.MyTable t6, dbo.MyTable t7, dbo.MyTable t8, dbo.MyTable t9, dbo.MyTable t10
WHERE
(((t2.cnxstartdatetime >= t1.cnxstartdatetime) and (t2.cnxstartdatetime <= t1.cnxenddatetime))
or ((t2.cnxenddatetime >= t1.cnxstartdatetime) and (t2.cnxenddatetime <= t1.cnxenddatetime)))
AND
t2.id != t1.id
AND
(((t3.cnxstartdatetime >= t2.cnxstartdatetime) and (t3.cnxstartdatetime >= t1.cnxstartdatetime)and (t3.cnxstartdatetime <= t1.cnxenddatetime) and (t3.cnxstartdatetime <= t2.cnxenddatetime))
or ((t3.cnxenddatetime >= t2.cnxstartdatetime) and (t3.cnxenddatetime >= t1.cnxstartdatetime)and (t3.cnxenddatetime <= t1.cnxenddatetime) and (t3.cnxenddatetime <= t2.cnxenddatetime)))
AND
t3.id != t2.id AND t3.id != t1.id
AND
(((t4.cnxstartdatetime >= t3.cnxstartdatetime) and (t4.cnxstartdatetime >= t1.cnxstartdatetime)and (t4.cnxstartdatetime >= t2.cnxstartdatetime) and (t4.cnxstartdatetime <= t1.cnxenddatetime) and (t4.cnxstartdatetime <= t3.cnxenddatetime)and (t4.cnxstartdatetime <= t2.cnxenddatetime))
or ((t4.cnxenddatetime >= t3.cnxstartdatetime) and (t4.cnxenddatetime >= t1.cnxstartdatetime)and (t4.cnxenddatetime >= t2.cnxstartdatetime) and (t4.cnxenddatetime <= t1.cnxenddatetime)and (t4.cnxenddatetime <= t3.cnxenddatetime)and (t4.cnxenddatetime <= t2.cnxenddatetime)))
AND
t4.id != t3.id AND t4.id != t2.id AND t4.id != t1.id
... *snip*
Edit Many of the responses are suggesting I use a cross join. This does not achieve the results I am looking for. Here's an example of the results of the cross join for one record's "overlaps." This is the list that it gives me for ID 11787 As you can see, 11781 does not overlap 11774 This is simply a list of any record whose time span intersects 11787
11774 2011-04-29 01:02:56.780 2011-04-29 01:02:58.793
11777 2011-04-29 01:02:56.780 2011-04-29 01:02:58.843
11778 2011-04-29 01:02:56.780 2011-04-29 01:02:58.950
11775 2011-04-29 01:02:56.793 2011-04-29 01:02:58.843
11776 2011-04-29 01:02:56.793 2011-04-29 01:02:58.890
11780 2011-04-29 01:02:58.310 2011-04-29 01:03:02.687
11779 2011-04-29 01:02:58.327 2011-04-29 01:03:02.543
11787 2011-04-29 01:02:58.530 2011-04-29 01:03:08.827 **
11781 2011-04-29 01:02:59.030 2011-04-29 01:03:05.187
11782 2011-04-29 01:02:59.247 2011-04-29 01:03:05.467
11784 2011-04-29 01:02:59.293 2011-04-29 01:03:05.810
11791 2011-04-29 01:03:00.107 2011-04-29 01:03:13.623
11786 2011-04-29 01:03:00.843 2011-04-29 01:03:08.983
11783 2011-04-29 01:03:02.560 2011-04-29 01:03:05.793
11785 2011-04-29 01:03:02.717 2011-04-29 01:03:07.357
11790 2011-04-29 01:03:05.200 2011-04-29 01:03:14.153
11804 2011-04-29 01:03:05.687 2011-04-29 01:03:25.577
11811 2011-04-29 01:03:07.093 2011-04-29 01:03:35.153
11799 2011-04-29 01:03:07.123 2011-04-29 01:03:24.437
11789 2011-04-29 01:03:08.793 2011-04-29 01:03:13.577
I've also attempted writing a CTE with recursion but I can't figure out how to insure the current ID doesn't match any previous ID in the current stack of concurrency. The below just recurses upon itself until it hits the limit.
WITH TransmissionConcurrency (StartTime, EndTime, ConcurrencyLevel) AS
(
SELECT
CnxStartDatetime AS StartTime,
CnxEndDatetime AS EndTime,
1 AS ConcurrencyLevel
FROM dbo.MyTable
UNION ALL
SELECT
CASE WHEN d.CnxStartDatetime > tc.StartTime THEN d.CnxStartDatetime ELSE tc.StartTime END AS StartTime,
CASE WHEN d.CnxEndDatetime < tc.EndTime THEN d.CnxEndDatetime ELSE tc.EndTime END AS EndDate,
tc.ConcurrencyLevel + 1 as ConcurrencyLevel
FROM dbo.MyTable d
INNER JOIN TransmissionConcurrency tc ON
((d.CnxStartDatetime between tc.StartTime and tc.EndTime)
or
(d.CnxEndDatetime between tc.StartTime and tc.EndTime)
or
(d.CnxStartDatetime <= tc.StartTime and d.CnxEndDatetime >= tc.EndTime))
)
SELECT *
FROM TransmissionConcurrency
ORDER BY ConcurrencyLevel, StartTime, EndTime
I've come up with the below diagram to try to better explain what I'm looking for.
A [--------]
B [-----]
C [------]
D [---]
E [---]
F [-]
In this instance, the cross join methods would tell me that the maximum concurrency with A is 6 (A with B, C, D, E and F) What I'm looking for would be a max concurrency of 3 (A with B,F or A with C,E)
解决方案
declare @T table (ID int, Starts datetime, Ends datetime)
insert into @T (ID, Starts, Ends) values
(1, '2000-12-30', '2000-12-31'),
(2, '2001-01-01', '2001-01-10'),
(3, '2001-01-02', '2001-01-05'),
(4, '2001-01-03', '2001-01-04'),
(5, '2001-01-05', '2001-01-10')
select T1.ID, count(*) as Levels
from @T as T1
cross join @T as T2
where
T1.Starts < T2.Ends and
T1.Starts > T2.Starts
group by T1.ID
select top 1 T1.ID, count(*) as Levels
from @T as T1
cross join @T as T2
where
T1.Starts < T2.Ends and
T1.Starts > T2.Starts
group by T1.ID
order by count(*) desc
Result
ID Levels
----------- -----------
3 1
4 2
5 1
(3 row(s) affected)
ID Levels
----------- -----------
4 2
If you want the rows that is the involved you can use this:
select T2.*
from (select top 1 T1.ID
from @T as T1
cross join @T as T2
where
T1.Starts < T2.Ends and
T1.Starts > T2.Starts
group by T1.ID
order by count(*) desc) as C
inner join @T as T1
on C.ID = T1.ID
inner join @T as T2
on T1.Starts < T2.Ends and
T1.Starts > T2.Starts or
T2.ID = C.ID
Result:
ID Starts Ends
----------- ----------------------- -----------------------
2 2001-01-01 00:00:00.000 2001-01-10 00:00:00.000
3 2001-01-02 00:00:00.000 2001-01-05 00:00:00.000
4 2001-01-03 00:00:00.000 2001-01-04 00:00:00.000
这篇关于查询并发时间跨度的最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
