我有一个SQL Server表，其中包含两个日期时间字段(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) 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*
编辑 许多回复都建议我使用cross join。 这没有达到我想要的结果。 下面是一个记录"重叠"的cross join结果示例。 这是它给我的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 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
我想出了下面的图表，试图更好地解释我正在寻找什么。
A [--------]
B [-----]
C [------]
D [---]
E [---]
F [-]
在这种情况下，cross join方法会告诉我A的最大并发性是 6(A B, C, D, E and F (我正在寻找的最大并发性为 3(A 与 B,F 或 A 与 C,E (
Jeff。我曾经写过一个类似的查询 - 但在 Oracle 中 - 不确定这是否适用于 SQL-Server，但值得一试：也许它会给你一些想法：
select
t.time as b,
lead(t.time) over (order by t.time, t.weight desc) as e,
sum(t.weight) over (order by t.time, t.weight desc) as cnt
from
( select trunc(:aStartWith) as time, 0 as weight from dual
union all
select req_recieved as time, +1 as weight
from log_tbl
where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute
union all
select response_sent as time, -1 as weight
from log_tbl
where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute
union all
select trunc(:aEndWith) as time, 0 as weight from dual
) t
一般的想法是，我遍历:aStartWith日期和日期之间的所有请求:aEndWith为给定时间段内开始的每个请求分配 +1 权重部分，为在同一时间段内结束的每个请求分配 -1 权重部分。
在这里，我假设请求不再是10分钟(where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute(;select ... from dual是边界条件。
然后使用分析函数，我找到请求的结束时间(lead(t.time) over (order by t.time, t.weight desc) as e(并汇总当前请求的权重 - 这将给出许多从时间b开始到时间e结束的请求(sum(t.weight) over (order by t.time, t.weight desc) as cnt(。
若要查找最大请求数，只需将此查询包装为所需的评估。
你能试试这个场景是否适合你吗？希望它能:)
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
结果
ID Levels
----------- -----------
3 1
4 2
5 1
(3 row(s) affected)
ID Levels
----------- -----------
4 2
如果你想要所涉及的行，你可以使用这个：
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
结果：
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
它更像是报告解决方案，而不是"标准"数据库查询。最好的选择是在每笔交易开始时在某处写下交易数量(。所有其他解决方案都会很慢。但如果你真的需要这个...
最简单的解决方案是拆分小零件的时间段(例如几天(并分析每个时间段的计数。下面是一个示例：
DECLARE @table TABLE
(
starts DATETIME ,
ends DATETIME ,
trn INT
)
INSERT INTO @table
( starts ,
ends ,
trn
)
SELECT '2003-01-01' ,
'2003-01-03' ,
1
UNION
SELECT '2003-01-02' ,
'2003-01-04' ,
2
UNION
SELECT '2003-01-02' ,
'2005-06-06' ,
3 ;
WITH numbers
AS ( SELECT Row_NUmber() OVER ( ORDER BY o.object_id, o2.object_id ) Number
FROM sys.objects o
CROSS JOIN sys.objects o2
),
Maxx
AS ( SELECT MIN(starts) MaxStart ,
MAX(ends) MaxEnd
FROM @table
),
DDays
AS ( SELECT MIN(starts) DDay
FROM @table
UNION ALL
SELECT DDay + 1
FROM DDays
WHERE dday + 1 <= ( SELECT MaxEnd
FROM Maxx
)
)
SELECT DDay ,
COUNT(*) Transactions
FROM @Table T
JOIN DDays D ON D.DDay >= T.starts
AND D.DDay <= T.ends
GROUP BY DDay
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC
OPTION ( MAXRECURSION 0 )
您可以修改最后一条语句以获取所需的信息(最大加载周期内的事务等(
/* prepare sample data (if needed) */
CREATE TABLE MyTable (ID int, CnxStartdatetime datetime, CnxEnddatetime datetime);
INSERT INTO MyTable (ID, CnxStartdatetime, CnxEnddatetime)
SELECT 11774, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.793' UNION ALL
SELECT 11777, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.843' UNION ALL
SELECT 11778, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.950' UNION ALL
SELECT 11775, '2011-04-29 01:02:56.793', '2011-04-29 01:02:58.843' UNION ALL
SELECT 11776, '2011-04-29 01:02:56.793', '2011-04-29 01:02:58.890' UNION ALL
SELECT 11780, '2011-04-29 01:02:58.310', '2011-04-29 01:03:02.687' UNION ALL
SELECT 11779, '2011-04-29 01:02:58.327', '2011-04-29 01:03:02.543' UNION ALL
SELECT 11787, '2011-04-29 01:02:58.530', '2011-04-29 01:03:08.827' UNION ALL
SELECT 11781, '2011-04-29 01:02:59.030', '2011-04-29 01:03:05.187' UNION ALL
SELECT 11782, '2011-04-29 01:02:59.247', '2011-04-29 01:03:05.467' UNION ALL
SELECT 11784, '2011-04-29 01:02:59.293', '2011-04-29 01:03:05.810' UNION ALL
SELECT 11791, '2011-04-29 01:03:00.107', '2011-04-29 01:03:13.623' UNION ALL
SELECT 11786, '2011-04-29 01:03:00.843', '2011-04-29 01:03:08.983' UNION ALL
SELECT 11783, '2011-04-29 01:03:02.560', '2011-04-29 01:03:05.793' UNION ALL
SELECT 11785, '2011-04-29 01:03:02.717', '2011-04-29 01:03:07.357' UNION ALL
SELECT 11790, '2011-04-29 01:03:05.200', '2011-04-29 01:03:14.153' UNION ALL
SELECT 11804, '2011-04-29 01:03:05.687', '2011-04-29 01:03:25.577' UNION ALL
SELECT 11811, '2011-04-29 01:03:07.093', '2011-04-29 01:03:35.153' UNION ALL
SELECT 11799, '2011-04-29 01:03:07.123', '2011-04-29 01:03:24.437' UNION ALL
SELECT 11789, '2011-04-29 01:03:08.793', '2011-04-29 01:03:13.577';
/* start the job: */
WITH columnified AS (
/* transform every row of (ID, CnxStartdatetime, CnxEnddatetime)
into two rows as follows:
(ID, CnxStartdatetime, CountChange = 1)
(ID, CnxEnddatetime, CountChange = -1)
*/
SELECT
t.ID,
Time = CASE x.CountChange WHEN 1 THEN CnxStartdatetime ELSE CnxEnddatetime END,
x.CountChange
FROM dbo.MyTable t
CROSS JOIN (SELECT 1 AS CountChange UNION ALL SELECT -1) x
),
groupedandranked AS (
/* group and rank the timestamps */
SELECT
Time,
CountChange = SUM(CountChange),
TimeRN = ROW_NUMBER() OVER (ORDER BY Time)
FROM columnified
GROUP BY time
),
counted AS (
/* get the running counts by summing CountChange */
SELECT
Time,
TimeRN,
RunningCount = CountChange
FROM groupedandranked
WHERE TimeRN = 1
UNION ALL
SELECT
t.Time,
t.TimeRN,
RunningCount = t.CountChange + c.RunningCount
FROM groupedandranked t
INNER JOIN counted c ON t.TimeRN = c.TimeRN + 1
),
countsranked AS (
/* rank the running counts */
SELECT
*,
CountRN = DENSE_RANK() OVER (ORDER BY RunningCount DESC)
FROM counted
)
/* get the top ranked rows and their corresponding
subsequent rows (for the ending timestamps) */
SELECT
MaxCount = s.RunningCount,
MaxCountStart = s.Time,
MaxCountEnd = e.Time
FROM countsranked s
LEFT JOIN countsranked e ON e.TimeRN = s.TimeRN + 1
WHERE s.CountRN = 1;
/* remove the sample data (unless it's your table) */
DROP TABLE MyTable
我知道
游标不受欢迎，但交叉连接也是如此。这将返回所提供示例数据的8。
-- assuming times table with columns s and e
declare @s datetime, @e datetime;
declare @t table(d datetime);
declare c cursor for select s,e from times order by s;
open c
while(1=1) begin
fetch next from c into @s,@e
if @@FETCH_STATUS<>0 break;
update top(1) @t set d=@e where d<=@s;
if @@ROWCOUNT=0 insert @t(d) values(@e);
end
close c
deallocate c
select COUNT(*) as MaxConcurrentTimeSpans from @t