Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Обединение временных интервалов  [new]
Hamlet
Member

Откуда: Armenia
Сообщений: 573
Привет.
Такой вопрос:
Заданы временные интервалы для обектов(Id) в виде начала и конца интервалов(sDate, eDate), нужно обединить эти интервалы.

DECLARE @a TABLE(Id int, sDate smalldatetime, eDate smalldatetime)

INSERT @a
SELECT 1,'20090601', '20090605' UNION ALL
SELECT 1,'20090602', '20090608' UNION ALL
SELECT 1,'20090607', '20090609' UNION ALL
SELECT 1,'20090611', '20090614' UNION ALL
SELECT 1,'20090611', '20090617' UNION ALL
SELECT 1,'20090613', '20090619' UNION ALL
SELECT 1,'20090612', '20090615' UNION ALL
SELECT 2,'20090601', '20090605' UNION ALL
SELECT 2,'20090602', '20090608' UNION ALL
SELECT 2,'20090607', '20090615' UNION ALL
SELECT 2,'20090611', '20090614' UNION ALL
SELECT 3,'20090611', '20090617' UNION ALL
SELECT 3,'20090613', '20090616' UNION ALL
SELECT 3,'20090612', '20090615'

Нужно получить
Id sDate eDate
1'20090601' '20090609'
1'20090611' '200906019'
2'20090601' '200906015'
3'200906011' '200906017'


Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) 

Заранее спасибо!
3 дек 09, 10:05    [8012042]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Можно, например, найти все интервалы, внутри которых нет ни одной точки, принадлежащей хотя бы одному интервалу из @a.
Они должны, конечно же, начинаться с какой-нибудь даты eDate и заканчиваться более поздней датой sDate.
А вот интервалы между "пустыми" интервалами и будут искомыми!

P.S. А это случайно не тестовое задание какое-нибудь?
3 дек 09, 10:26    [8012176]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
Hamlet
Member

Откуда: Armenia
Сообщений: 573
iap
Можно, например, найти все интервалы, внутри которых нет ни одной точки, принадлежащей хотя бы одному интервалу из @a.
Они должны, конечно же, начинаться с какой-нибудь даты eDate и заканчиваться более поздней датой sDate.
А вот интервалы между "пустыми" интервалами и будут искомыми!


Вариант, найти наибольший интервал, найти в нем дырки, потом спроецировать на нем дырки и получать результат. Такой вариант есть но громоздко!

iap
P.S. А это случайно не тестовое задание какое-нибудь?

Я тоже припоминаю такую задачку на http://sql-ex.ru , но это не то ;)
3 дек 09, 10:36    [8012230]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
Добрый Э - Эх
Guest
Тынц - пересекающиеся интервалы
3 дек 09, 10:37    [8012236]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Hamlet,
как то так
SELECT
	Id
	,min(d)
	,max(d)
FROM(
	SELECT
		a.Id
		,a.sDate + b.number d
		,DENSE_RANK() OVER(partition BY Id ORDER BY a.sDate + b.number) r
	FROM @a a
		JOIN(
			SELECT number FROM master.dbo.spt_values WHERE type='P'
		) b ON number <= datediff(day,a.sDate,a.eDate)
) v
GROUP BY
	Id
	,d-r
ORDER BY
	Id,min(d)
3 дек 09, 10:39    [8012254]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
DECLARE @a TABLE(PK int not null identity unique, Id int, sDate smalldatetime, eDate smalldatetime)

INSERT @a(Id,sDate,eDate) VALUES
 (1,'20090601','20090605')
,(1,'20090602','20090608')
,(1,'20090607','20090609')
,(1,'20090611','20090614')
,(1,'20090611','20090617')
,(1,'20090613','20090619')
,(1,'20090612','20090615')
,(2,'20090601','20090605')
,(2,'20090602','20090608')
,(2,'20090607','20090615')
,(2,'20090611','20090614')
,(3,'20090611','20090617')
,(3,'20090613','20090616')
,(3,'20090612','20090615');

SELECT * FROM @a;

WITH
 FromDate AS
 (
  SELECT DISTINCT ROW_NUMBER()OVER(PARTITION BY T.Id ORDER BY T.sDate) N, T.Id, T.sDate
  FROM @a T
  WHERE NOT EXISTS(SELECT * FROM @a TT WHERE TT.PK<>T.PK AND TT.Id=T.Id AND TT.sDate<T.sDate AND TT.eDate>T.sDate)
 )
,ToDate AS
 (
  SELECT DISTINCT ROW_NUMBER()OVER(PARTITION BY T.Id ORDER BY T.eDate) N, T.Id, T.eDate
  FROM @a T
  WHERE NOT EXISTS(SELECT * FROM @a TT WHERE TT.PK<>T.PK AND TT.Id=T.Id AND TT.sDate<T.eDate AND TT.eDate>T.eDate)
 )
SELECT FromDate.Id, FromDate.sDate, ToDate.eDate
FROM FromDate JOIN ToDate ON FromDate.Id=ToDate.Id AND FromDate.N=ToDate.N
ORDER BY FromDate.Id, FromDate.sDate;
3 дек 09, 11:07    [8012494]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
iap,
в Вашем запросе из CTE можно выкинуть DISTINCT и TT.PK<>T.PK -)
3 дек 09, 11:16    [8012563]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Сергей Мишин
iap,
в Вашем запросе из CTE можно выкинуть DISTINCT и TT.PK<>T.PK -)
Кое-что действительно забыл убрать - первоначально даты сравнивал нестрого...
3 дек 09, 11:28    [8012693]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
Hamlet
Member

Откуда: Armenia
Сообщений: 573
Спасибо всем...

А вот и мое решение (на основе идей Добрый Э - Эх)

DECLARE @a TABLE(Id int, sDate smalldatetime, eDate smalldatetime)

INSERT @a
SELECT 1,'20090601', '20090605' UNION ALL
SELECT 1,'20090602', '20090608' UNION ALL
SELECT 1,'20090607', '20090609' UNION ALL
SELECT 1,'20090611', '20090614' UNION ALL
SELECT 1,'20090611', '20090617' UNION ALL
SELECT 1,'20090613', '20090619' UNION ALL
SELECT 1,'20090612', '20090615' UNION ALL
SELECT 2,'20090601', '20090605' UNION ALL
SELECT 2,'20090602', '20090608' UNION ALL
SELECT 2,'20090607', '20090615' UNION ALL
SELECT 2,'20090611', '20090614' UNION ALL
SELECT 3,'20090611', '20090617' UNION ALL
SELECT 3,'20090613', '20090616' UNION ALL
SELECT 3,'20090612', '20090615'


SELECT
S.Id, S.sDate, E.eDate
FROM
	(
		SELECT DISTINCT 
			A1.Id,
			A1.sDate
		FROM @a A1
			LEFT JOIN @a A2
				ON A1.Id = A2.Id
					AND A1.sDate > A2.sDate
					AND A1.sDate <= A2.eDate + 1
		WHERE A2.Id IS NULL

	) S
	CROSS APPLY
	(
		SELECT
			MIN(eDate) eDate
		FROM
		(
			SELECT
				A1.Id,
				A1.eDate eDate
			FROM @a A1
				LEFT JOIN @a A2
					ON A1.Id = A2.Id
						AND A1.eDate >= A2.sDate - 1
						AND A1.eDate < A2.eDate
			WHERE A2.Id IS NULL
		) K
			
			WHERE K.Id = S.Id
			and S.sDate <= K.eDate
	) E
	
Прошу критиковать на предмет результативности все вышеприведенные запросы, так как запрос стоит в довольно узком месте.

Заранее спасибо!
3 дек 09, 13:19    [8013790]     Ответить | Цитировать Сообщить модератору
 Re: Обединение временных интервалов  [new]
utmax
Member

Откуда: Коломна
Сообщений: 148
Без использования специфики 2005 сервера можно так, по производительности планы очень сходны с запросом Hamlet

SELECT b.Id, b.sDate, MIN(e.eDate) AS eDate
FROM
	(
		SELECT a.Id, a.sDate
		FROM @a a
		LEFT OUTER JOIN @a b ON b.Id = a.Id AND a.sDate BETWEEN b.sDate AND b.eDate AND a.sDate <> b.sDate AND a.eDate <> b.eDate
		WHERE b.Id IS NULL
		GROUP BY a.Id, a.sDate
	) b
	INNER JOIN (
		SELECT a.Id, a.eDate
		FROM @a a
		LEFT OUTER JOIN @a b ON b.Id = a.Id AND a.eDate BETWEEN b.sDate AND b.eDate AND a.sDate <> b.sDate AND a.eDate <> b.eDate
		WHERE b.Id IS NULL
		GROUP BY a.Id, a.eDate
	) e ON e.Id = b.Id AND e.eDate >= b.sDate
GROUP BY
	b.Id, b.sDate
3 дек 09, 14:26    [8014417]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить