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

Откуда:
Сообщений: 2
Доброго времени суток!

Передо мной стоит задача: свернуть непрерывные временные интервалы для каждого сочетания Id и DiscountId.
При поиске решений обнаружил несколько скриптов Ицик Бен-Гана. Решение попытался основать на SQL Challenge – packing time intervals and merging valid time periods (правда, там Оракл, но синтаксис в рамках задачи совпал).
Фрагмент тестовых данных
+ Скрипт для генерации таблицы

CREATE TABLE [dbo].[tbl_name]
(
	[Id]			[INT]		NULL
	,[DiscountId]	[INT]		NULL
	,[DateFrom]		[DATETIME]	NULL
	,[DateTo]		[DATETIME]	NULL
)
ON [PRIMARY]

GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, NULL, CAST(N'2017-09-01 14:00:00.000' AS DATETIME), CAST(N'2017-09-02 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-02 12:00:00.000' AS DATETIME), CAST(N'2017-09-03 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-03 12:00:00.000' AS DATETIME), CAST(N'2017-09-04 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-04 12:00:00.000' AS DATETIME), CAST(N'2017-09-05 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-05 12:00:00.000' AS DATETIME), CAST(N'2017-09-06 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, NULL, CAST(N'2017-09-06 12:00:00.000' AS DATETIME), CAST(N'2017-09-07 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, NULL, CAST(N'2017-09-07 12:00:00.000' AS DATETIME), CAST(N'2017-09-08 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 11, CAST(N'2017-09-08 12:00:00.000' AS DATETIME), CAST(N'2017-09-09 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 11, CAST(N'2017-09-09 12:00:00.000' AS DATETIME), CAST(N'2017-09-10 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 11, CAST(N'2017-09-10 12:00:00.000' AS DATETIME), CAST(N'2017-09-11 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, NULL, CAST(N'2017-09-11 12:00:00.000' AS DATETIME), CAST(N'2017-09-12 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 21, CAST(N'2017-09-12 12:00:00.000' AS DATETIME), CAST(N'2017-09-13 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 21, CAST(N'2017-09-13 12:00:00.000' AS DATETIME), CAST(N'2017-09-14 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 21, CAST(N'2017-09-14 12:00:00.000' AS DATETIME), CAST(N'2017-09-15 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 21, CAST(N'2017-09-15 12:00:00.000' AS DATETIME), CAST(N'2017-09-16 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-16 12:00:00.000' AS DATETIME), CAST(N'2017-09-17 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-17 12:00:00.000' AS DATETIME), CAST(N'2017-09-18 12:00:00.000' AS DATETIME))
GO
INSERT [dbo].[tbl_name] ([Id], [DiscountId], [DateFrom], [DateTo])
	VALUES (1, 6, CAST(N'2017-09-18 12:00:00.000' AS DATETIME), CAST(N'2017-09-19 12:00:00.000' AS DATETIME))
GO


Картинка с другого сайта.

+ Фрагмент кода с неверным результатом

WITH idDis AS
(
	SELECT
		tn.Id
		,ISNULL(tn.DiscountId, 0) DiscountId
		,tn.DateFrom
		,tn.DateTo
		,CASE 
			WHEN 
				tn.DateFrom = LAG(tn.DateTo) OVER (PARTITION BY tn.Id ORDER BY tn.DateFrom ASC)
				AND ISNULL(tn.DiscountId, 0) = ISNULL(LAG(tn.DiscountId) OVER (PARTITION BY tn.Id ORDER BY tn.DateFrom ASC), 0)
			THEN 'PACK'
		END [action]
		/*!!!*/,RANK()  OVER (PARTITION BY tn.Id, tn.DiscountId ORDER BY tn.DateFrom ASC) rnk /*!!!*/
	FROM tbl_name tn

),
packing_idDis
(
	Id
	,DiscountId
	,DateFrom
	,DateTo
	,lvl
)
AS
(
	SELECT
		Id
		,DiscountId
		,DateFrom
		,DateTo
		,1
	FROM idDis
	WHERE [action] IS NULL

	UNION ALL

	SELECT
		p.Id
		,p.DiscountId
		,p.DateFrom
		,c.DateTo
		,lvl + 1
	FROM
		idDis c
		JOIN packing_idDis p ON (c.Id = p.Id AND c.DiscountId = p.DiscountId AND /*!!!*/ c.rnk = p.lvl + 1 /*!!!*/)
	WHERE c.[action] = 'PACK'
),
packed_idDis
AS
(
	SELECT
		Id
		,DiscountId
		,DateFrom
		,MAX(DateTo) DateTo
	FROM packing_idDis
	GROUP BY
		Id
		,DateFrom
		,DiscountId
)

SELECT *
FROM packed_idDis
WHERE Id = 1
ORDER BY DateFrom


Текущий результат, красным обведены некорректные значения.
Картинка с другого сайта.
Строка 3 должна иметь DateTo 2017-09-08 12:00:00.000.
Строка 7 должна иметь DateTo 2017-09-19 12:00:00.000.
Ошибку получаю при наличии более одного временного интервала для одной пары Id и DiscountId (например Id = 1 и DiscountId = 0; Id=1 и DiscountId = 6). Фрагменты кода, которые считаю ошибочными, выделил /*!!!*/code/*!!!*/. Но не понимаю как исправить. Помогите, пожалуйста.
16 авг 17, 22:43    [20729698]     Ответить | Цитировать Сообщить модератору
 Re: Схлопывание непрерывных временных диапазонов  [new]
Aleks_U
Member

Откуда: Москва
Сообщений: 251
Посмотрите здесь
http://www.cyberforum.ru/sql-server/thread1661797.html
16 авг 17, 23:32    [20729754]     Ответить | Цитировать Сообщить модератору
 Re: Схлопывание непрерывных временных диапазонов  [new]
aleks222
Guest
https://www.sql.ru/forum/1266094/poisk-min-i-max-granic-peresekaushhihsya-otrezkov?hl=??????????
17 авг 17, 05:57    [20729880]     Ответить | Цитировать Сообщить модератору
 Re: Схлопывание непрерывных временных диапазонов  [new]
pavifed
Member

Откуда:
Сообщений: 2
Спасибо за ответы!
Так как у меня началом следующего диапазона всегда является конец предыдущего, то в исходном коде необходимо строку
JOIN packing_idDis p ON (c.Id = p.Id AND c.DiscountId = p.DiscountId AND c.rnk = p.lvl + 1)

заменить на
JOIN packing_idDis p ON (c.Id = p.Id AND c.DiscountId = p.DiscountId AND c.DateFrom = p.DateTo

Теперь работает корректно.
17 авг 17, 13:17    [20731050]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить