ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск пересечений периодов
5 сообщений из 5, страница 1 из 1
Поиск пересечений периодов
    #40028289
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Здравствуйте!

Есть SQL код. Создается таблица, где хранятся цены на определенные товары в зависимости от периода. Если в поле "ОкончаниеПериода" указано Null, то значит, что это как последняя действующая цена. Окончание периода это как граница.

Необходимо вывести строки, где периоды пересекаются.

 
SQL
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
USE TestSQL_2;

DROP TABLE IF EXISTS dbo.ЖурналЦен;

CREATE TABLE dbo.ЖурналЦен
(
	Id INT NOT NULL,
	Товар_Id INT NOT NULL,
	НачалаПериода DATE NOT NULL, -- Не как граница		
	ОкончаниеПериода DATE,	     -- Как граница
	Цена DECIMAL(8,2),
	-- Ограничения таблицы
	CONSTRAINT Period_unique UNIQUE ([Товар_Id], [НачалаПериода],[ОкончаниеПериода]),
	CONSTRAINT Period_equality CHECK ([НачалаПериода] <> [ОкончаниеПериода])	
);

INSERT INTO dbo.ЖурналЦен
	(Id, Товар_Id, НачалаПериода, ОкончаниеПериода, Цена)
VALUES
	(1, 1, '01-01-2020', '01-02-2020', 100),
	(2, 1, '01-02-2020', '01-03-2020', 105),
	(3, 1, '01-03-2020', NULL,120),
	(4, 2, '01-01-2020', '02-01-2020', 80),
	(5, 2, '02-01-2020', '03-01-2020', 82),
	(6, 2, '03-01-2020', '04-01-2020', 85),
	(7, 2, '04-01-2020', NULL, 90)
	--,(8, 1, '01-02-2020', '28-02-2020', 85)	-- Случай 1. Дублирование для id = 2
	--,(9, 1, '02-03-2020', NULL, 90)		-- Случай 2. Дублирование для id = 3
	--,(10, 1, '03-01-2020', '04-02-2020', 110)	-- Случай 3. Дублирование для id = 1 и id = 2
	--,(11, 2, '02-01-2020', '03-01-2020', 110)	-- Случай 4. Дублирование для id = 5
	                                                                -- UNIQUE Period_unique не разрешает ставить
                                                                        -- повторяющие значения ключа
	--,(11, 2, '03-01-2020', '05-01-2020', 110)	-- Случай 5. Дублирование для id = 6 и id = 7
	--,(12, 2, '04-01-2020', '05-01-2020', 110)	-- Случай 6. Дублирование для id = 7
	--,(13, 2, '04-01-2020', '04-01-2020', 110)	-- Случай 7. Дата начала и дата окончания совпадают.
									-- CHECK Period_equality не разрешает ставить
                                                                        -- одинаковые даты
	;

DECLARE @ТекущаяДата DATE;
SET @ТекущаяДата = GetDate();

SELECT
	ЖЦ1.Id,
	ЖЦ2.Id
FROM
	dbo.ЖурналЦен ЖЦ1
JOIN
	dbo.ЖурналЦен ЖЦ2
ON
	ЖЦ1.Id <> ЖЦ2.id
WHERE
	ЖЦ1.Товар_Id = ЖЦ2.Товар_Id
	AND	
	ЖЦ1.НачалаПериода < ISNULL(ЖЦ2.ОкончаниеПериода, @ТекущаяДата)
	AND
	ISNULL(ЖЦ1.ОкончаниеПериода, @ТекущаяДата) > ЖЦ2.НачалаПериода


Для указанных случаев в коде алгоритм находит пересечение периодов. Вроде как бы рабочий код. Хотелось бы поинтересоваться для текущего алгоритма есть то, что еще не учитывается?
...
Рейтинг: 0 / 0
16.12.2020, 12:28
| Ответить | Цитировать | Написать  
Поиск пересечений периодов
    #40028309
Фотография ptr128
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ptr128
Участник
ferzmikk, а разве цена не может быть установлена на завтрашний день?
...
Рейтинг: 0 / 0
16.12.2020, 13:30
| Ответить | Цитировать | Написать  
Поиск пересечений периодов
    #40028316
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
ptr128
ferzmikk, а разве цена не может быть установлена на завтрашний день?
Может. Упустил. Скорректирую.
...
Рейтинг: 0 / 0
16.12.2020, 14:04
| Ответить | Цитировать | Написать  
Поиск пересечений периодов
    #40028647
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Скорректировал
 
SQL
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
USE TestSQL_2;

DROP TABLE IF EXISTS dbo.ЖурналЦен;

CREATE TABLE dbo.ЖурналЦен
(
	Id INT NOT NULL,
	Товар_Id INT NOT NULL,
	НачалаПериода DATE NOT NULL, -- Не как граница		
	ОкончаниеПериода DATE,	     -- Как граница
	Цена DECIMAL(8,2),
	-- Ограничения таблицы
	CONSTRAINT Period_unique UNIQUE ([Товар_Id], [НачалаПериода],[ОкончаниеПериода]),
	CONSTRAINT Period_equality CHECK ([НачалаПериода] <> [ОкончаниеПериода])
);

INSERT INTO dbo.ЖурналЦен
	(Id, Товар_Id, НачалаПериода, ОкончаниеПериода, Цена)
VALUES
	(1, 1, '01-01-2020', '01-02-2020', 100),
	(2, 1, '01-02-2020', '01-03-2020', 105),
	(3, 1, '01-03-2020', NULL,120),
	(4, 2, '01-01-2020', '02-01-2020', 80),
	(5, 2, '02-01-2020', '03-01-2020', 82),
	(6, 2, '03-01-2020', '04-01-2020', 85),
	(7, 2, '04-01-2020', NULL, 90)
	--,(8, 1, '01-02-2020', '28-02-2020', 85)	-- Случай 1. Дублирование для id = 2
	--,(9, 1, '02-03-2020', NULL, 90)		-- Случай 2. Дублирование для id = 3
	--,(10, 1, '03-01-2020', '04-02-2020', 110)	-- Случай 3. Дублирование для id = 1 и id = 2
	--,(11, 2, '02-01-2020', '03-01-2020', 110)	-- Случай 4. Дублирование для id = 5
	                                                --           UNIQUE Period_unique не разрешает ставить повторяющие значения ключа
	--,(11, 2, '03-01-2020', '05-01-2020', 110)	-- Случай 5. Дублирование для id = 6 и id = 7
	--,(12, 2, '04-01-2020', '05-01-2020', 110)	-- Случай 6. Дублирование для id = 7
	--,(13, 2, '04-01-2020', '04-01-2020', 110)	-- Случай 7. дата начала и дата окончания совпадают.
	                                                --           CHECK Period_equality не разрешает ставить одинаковые даты
	,(14, 3, '01-01-2020', '01-02-2020', 100)
	,(15, 3, '01-02-2020', '02-02-2020', 105)
	,(16, 3, '02-02-2020', '01-01-2021', 110)
	,(17, 3, '01-01-2021', '03-01-2021', 105)
	,(18, 3, '03-01-2021', NULL, 115)
	-- ,(19, 3, '01-01-2021', '02-01-2021', 111)	-- Случай 8. Дублирование для id = 17

	;

DECLARE @ТекущаяДата DATE;
SET @ТекущаяДата = GetDate();

DECLARE @МаксимальнаяДата DATE;
SET @МаксимальнаяДата =
(
	SELECT 
		MAX(a)
		FROM
			(VALUES				
				(DATEADD(day, 1, CONVERT(date, @ТекущаяДата))),
				((SELECT MAX(ОкончаниеПериода) FROM dbo.ЖурналЦен))
			) AS data(a)
);

SELECT
	ЖЦ1.Id,
	ЖЦ2.Id
FROM
	dbo.ЖурналЦен ЖЦ1
JOIN
	dbo.ЖурналЦен ЖЦ2
ON
	ЖЦ1.Id <> ЖЦ2.id
WHERE
	ЖЦ1.Товар_Id = ЖЦ2.Товар_Id
	AND	
	ЖЦ1.НачалаПериода < ISNULL(ЖЦ2.ОкончаниеПериода, @МаксимальнаяДата )
	AND
	ISNULL(ЖЦ1.ОкончаниеПериода, @МаксимальнаяДата ) > ЖЦ2.НачалаПериода

...
Рейтинг: 0 / 0
17.12.2020, 11:43
| Ответить | Цитировать | Написать  
Поиск пересечений периодов
    #40028663
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Классическая задача gap and islands.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

1. Вначале объединить все непрерывные периоды, в которых цена на данный товар - одинаковая.
2. Потом искать те объединенные периоды, начало или конец которых находится внутри других объединенных периодов.
...
Рейтинг: 0 / 0
17.12.2020, 12:09
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поиск пересечений периодов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=39&tid=1685284]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 34ms
get tp. blocked users:
 1ms
others: 12ms
total:  119ms
созд. / загр.: 119ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]