Слияние перекрывающиеся интервалы даты
Существует ли лучший способ объединить перекрывающиеся интервалы даты?
Решение, которое я предложил, так просто, который теперь интересно, есть ли у кого-то еще лучшая идея того, как это могло быть сделано.
/***** DATA EXAMPLE *****/
DECLARE @T TABLE (d1 DATETIME, d2 DATETIME)
INSERT INTO @T (d1, d2)
SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31'
UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10'
UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09'
UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08'
UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16'
UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13'
/***** INTERVAL ANALYSIS *****/
WHILE (1=1) BEGIN
UPDATE t1 SET t1.d2 = t2.d2
FROM @T AS t1 INNER JOIN @T AS t2 ON
DATEADD(day, 1, t1.d2) BETWEEN t2.d1 AND t2.d2
IF @@ROWCOUNT = 0 BREAK
END
/***** RESULT *****/
SELECT StartDate = MIN(d1) , EndDate = d2
FROM @T
GROUP BY d2
ORDER BY StartDate, EndDate
/***** OUTPUT *****/
/*****
StartDate EndDate
2010-01-01 2010-06-13
2010-06-15 2010-08-16
2010-11-01 2010-12-31
*****/
2 ответа
В этом решении я создал временную таблицу Calendar, в которой хранятся значения для каждого дня в диапазоне. Этот тип таблицы можно сделать статической. Вдобавок я храню только 400 каких-то нечетных дат, начиная с 31 декабря 2009 года. Очевидно, что если ваши даты охватывают более широкий диапазон, вам потребуется больше значений.
Кроме того, это решение будет работать только с SQL Server 2005+, поскольку я использую CTE.
With Calendar As
(
Select DateAdd(d, ROW_NUMBER() OVER ( ORDER BY s1.object_id ), '1900-01-01') As [Date]
From sys.columns as s1
Cross Join sys.columns as s2
)
, StopDates As
(
Select C.[Date]
From Calendar As C
Left Join @T As T
On C.[Date] Between T.d1 And T.d2
Where C.[Date] >= ( Select Min(T2.d1) From @T As T2 )
And C.[Date] <= ( Select Max(T2.d2) From @T As T2 )
And T.d1 Is Null
)
, StopDatesInUse As
(
Select D1.[Date]
From StopDates As D1
Left Join StopDates As D2
On D1.[Date] = DateAdd(d,1,D2.Date)
Where D2.[Date] Is Null
)
, DataWithEariestStopDate As
(
Select *
, (Select Min(SD2.[Date])
From StopDatesInUse As SD2
Where T.d2 < SD2.[Date] ) As StopDate
From @T As T
)
Select Min(d1), Max(d2)
From DataWithEariestStopDate
Group By StopDate
Order By Min(d1)
РЕДАКТИРОВАТЬ Проблема с использованием дат в 2009 году не имеет ничего общего с окончательным запросом. Проблема в том, что таблица Calendar недостаточно велика. Я запустил таблицу Calendar 31 декабря 2009 года. Я пересмотрел это начало 1900-01-01.
Попробуйте это
;WITH T1 AS
(
SELECT d1, d2, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
FROM @T
), NUMS AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
FROM T1 A
CROSS JOIN T1 B
CROSS JOIN T1 C
), ONERANGE AS
(
SELECT DISTINCT DATEADD(DAY, ROW_NUMBER() OVER(PARTITION BY T1.R ORDER BY (SELECT 0)) - 1, T1.D1) AS ELEMENT
FROM T1
CROSS JOIN NUMS
WHERE NUMS.R <= DATEDIFF(DAY, d1, d2) + 1
), SEQUENCE AS
(
SELECT ELEMENT, DATEDIFF(DAY, '19000101', ELEMENT) - ROW_NUMBER() OVER(ORDER BY ELEMENT) AS rownum
FROM ONERANGE
)
SELECT MIN(ELEMENT) AS StartDate, MAX(ELEMENT) as EndDate
FROM SEQUENCE
GROUP BY rownum
Основная идея состоит в том, чтобы сначала развернуть существующие данные, чтобы получить отдельную строку для каждого дня. Это делается в ONERANGE
. Затем определите взаимосвязь между увеличением дат и порядком номеров строк. Разница остается постоянной в пределах существующего диапазона / острова. Как только вы попадаете на новый остров данных, разница между ними увеличивается, поскольку дата увеличивается более чем на 1, а номер строки увеличивается на 1.