У меня есть таблица 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*
Изменить Во многих ответах предлагается использовать перекрестное соединение
. Это не дает тех результатов, которые я ищу. Вот пример результатов перекрестного соединения
для «перекрытий» одной записи. Это список, который он дает мне для идентификатора 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
I ' Чтобы лучше объяснить, что я ищу, мы разработали диаграмму ниже.
A [--------]
B [-----]
C [------]
D [---]
E [---]
F [-]
В этом случае методы перекрестного соединения
сообщают мне, что максимальный параллелизм с A
равно 6 ( A
с B, C, D, E и F
) То, что я ищу, было бы максимальным параллелизмом 3 ( A
с B, F
или A
с C, E
)