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

Откуда: Прага
Сообщений: 247
День добрый!
Сейчас занимаемся оптимизацией узких мест в приложении, наткнулись на такую проблему, есть гостиница, в ней номера, каждый номер имеет цены, разбитые на периоды. При этом периоды могут иметь разный приоритет и пересекаться. Например так:

create table #periods (datefrom smalldatetime,dateto smalldatetime,minstay int, freedays int)
create table #dates (dd smalldatetime)

INSERT INTO #periods
(
	datefrom,
	dateto,
	minstay,
	freedays
)
SELECT '20150101',
		'20150117',
		5,
		1
UNION ALL
SELECT '20150101',
		'20150117',
		7,
		1
UNION ALL
SELECT '20150101',
		'20150130',
		1,
		0



 drop table #periods


Задача - выбрать для проживания те периоды, которые лучше всего соотвествтуют по приоритету. Упорядочиваются они по minstay DESC,freedays DESC.

Например при проживании с 1.1 по 20.1 результат должен получиться такой, что
с 1.1-9.1 цена из (5/1)
с 10.1-17.1 цена из (7/1)
с 18.1-20.1 цена из (1/0).

Т.е. первый период укорачивается до 9.1, т.к. с 10.1 начинается более приоритетный.


В принципе, этот расчет решен с помощью циклов, но естесственно работает медленно, а как его привести к "правильному" с точки зрения SQL подхода решению? Есть идеи? Спасибо!
27 авг 14, 14:45    [16501091]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
1001
Guest
cavalero
День добрый!
Сейчас занимаемся оптимизацией узких мест в приложении, наткнулись на такую проблему, есть гостиница, в ней номера, каждый номер имеет цены, разбитые на периоды. При этом периоды могут иметь разный приоритет и пересекаться. Например так:

[
+
src]
create table #periods (datefrom smalldatetime,dateto smalldatetime,minstay int, freedays int)
create table #dates (dd smalldatetime)

INSERT INTO #periods
(
datefrom,
dateto,
minstay,
freedays
)
SELECT '20150101',
'20150117',
5,
1
UNION ALL
SELECT '20150101',
'20150117',
7,
1
UNION ALL
SELECT '20150101',
'20150130',
1,
0



drop table #periods

[/src]

Задача - выбрать для проживания те периоды, которые лучше всего соотвествтуют по приоритету. Упорядочиваются они по minstay DESC,freedays DESC.

Например при проживании с 1.1 по 20.1 результат должен получиться такой, что
с 1.1-9.1 цена из (5/1)
с 10.1-17.1 цена из (7/1)
с 18.1-20.1 цена из (1/0).

Т.е. первый период укорачивается до 9.1, т.к. с 10.1 начинается более приоритетный.


В принципе, этот расчет решен с помощью циклов, но естесственно работает медленно, а как его привести к "правильному" с точки зрения SQL подхода решению? Есть идеи? Спасибо!
выставить приоритеты - ну как еще безболезненнее

а мож у вас периоды еще продолжительность имеют неоднозначную
27 авг 14, 14:56    [16501246]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
1001,

выставить приоритеты - ну как еще безболезненнее

это как?

а мож у вас периоды еще продолжительность имеют неоднозначную

Тоже не понял.
27 авг 14, 14:59    [16501297]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
У тя проблемы с top(1) и outer apply?
27 авг 14, 18:20    [16502989]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2, судя по всему - да.
27 авг 14, 18:28    [16503023]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2, что вы таки имели в виду?
27 авг 14, 18:36    [16503064]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
Самый тупой и незамысловатый способ.
declare @periods table (id int identity primary key clustered, datefrom smalldatetime, dateto smalldatetime,minstay int, freedays int)
declare @numbers table (n int primary key clustered);

declare @d0 datetime = '20150101', @d1 datetime = '20150120';

-- естественно эту таблицу надо просто иметь в базе
insert @numbers
values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)

INSERT INTO @periods
(
	datefrom,
	dateto,
	minstay,
	freedays
)
SELECT '20150101',
		'20150117',
		5,
		1
UNION ALL
SELECT '20150110',
		'20150117',
		7,
		1
UNION ALL
SELECT '20150101',
		'20150130',
		1,
		0;

with 
    dates as ( select @d0 + n date from @numbers where n < DATEDIFF( DAY, @d0, @d1 ) )
  , bests as ( select dates.date, x.id 
                 from dates 
                 outer apply( select top(1) * from @periods where dates.date between datefrom and dateto order by minstay DESC, freedays DESC) x
              )
  , ranges as ( select id, MIN(date) as datefrom, MAX(date) as dateto from bests group by id )
  select p.*, r.datefrom, r.dateto  from ranges r inner join @periods p on r.id = p.id
27 авг 14, 18:55    [16503163]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2, классно! я ничего не понял, но буду разбираться! а как можно обработать ситуацию

declare @d0 datetime = '20150107', @d1 datetime = '20150120';


когда получается, что период 5/1 не подходит, т.к. в нем живут только 2 дня, с 7.1 по 9.1?
27 авг 14, 19:19    [16503259]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
да, алгоритм классный, но он не учитывает то, что результирующие периоды не должны быть короче, чем minstay, т.е. в случае

declare @d0 datetime = '20150107', @d1 datetime = '20150120';

это будет работать уже неправильно :(
28 авг 14, 15:02    [16507399]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Я тут немного поработал с этим алгоритмом, и актуализировал его для расчета цен с длительностью проживания 1-31 день в период с 1.1.2015-1.1.2016, получилась следующая вещь. Как избежать цикла для проверки того, выполнится ли условие, что полученный период будет не короче, чем его minstay я не знаю, и пока это не ключевой момент.

У меня получается, что 98 процентов времени, от выполнения запроса уходят на 2 операции сортировки, можно ли их как-то оптимизировать? Алгоритм получается следующий:

+
create table #periods (id int identity primary key clustered, datefrom smalldatetime, dateto smalldatetime,minstay int, freedays int,disGroup int, optionsid int)
--create nonclustered index foo on #periods (id,disGroup,optionsid,datefrom,dateto)
create table #allocations (id int IDENTITY primary key clustered,disGroup int,optionsid int, DateFrom smalldatetime,DateTo smalldatetime,stay int,summ int)
declare @newexceptions bit, @exceptionscnt int,@cnt int
create table #exceptions (allocationid int, dd smalldatetime, periodid int)
create clustered index foo on #exceptions (allocationid,periodid,dd)
create table #results (allocationid int,datefrom smalldatetime,dateto smalldatetime, minstay int, freedays int,periodid int, checked bit )
create nonclustered index foo on #results (allocationid)




 CREATE TABLE #Dates([Date] DATETIME NOT NULL, CONSTRAINT [pkDates] PRIMARY KEY CLUSTERED([Date]));
 WITH [T]([Date]) AS
 (
  SELECT CAST('2001' AS DATETIME)
  UNION ALL
  SELECT DATEADD(DAY,1,[Date]) FROM [T] WHERE [Date]<'2021'
 )
 INSERT into #Dates (Date)
 SELECT [Date]
 FROM [T]
 OPTION(MAXRECURSION 0);




					INSERT INTO #periods
					(
						datefrom,
						dateto,
						minstay,
						freedays,
						disGroup,
						optionsid
					)
					SELECT HRP.datefrom,
							HRP.dateto,
							HRP.minstay,
							HRP.freedays,
							M.groupid,
							M.optionsid
					FROM 
					(
						SELECT '20150101' datefrom,
							'20160101' dateto,
							1 minstay,
							0 freedays
						UNION ALL
						SELECT '20150101' datefrom,
							'20160101' dateto,
							7 minstay,
							1 freedays
						UNION ALL
						SELECT '20150101' datefrom,
							'20160101' dateto,
							10 minstay,
							2 freedays
					) HRP cross join 
					(
						SELECT 0 optionsid, 0 groupid
					--	UNION ALL
					--	SELECT 1 optionsid, 1 groupid
					) M
					ORDER BY HRP.minstay DESC,HRP.freedays DESC



					declare @enddate smalldatetime
					declare @currdate smalldatetime

					SELECT @enddate=MAX(DateTo)
					FROM #periods
					SELECT @currdate=MIN(DateFrom) 
					FROM #periods



					INSERT INTO #allocations 
					(
						DateFrom,
						DateTo,
						stay,
						summ,
						disGroup,
						optionsid
					)
					SELECT D.Date datefrom,dateadd(day,F.bd-1,D.Date) dateto,F.bd,0,M.groupid,M.optionsid
					FROM [master].[dbo].[Dates] D INNER JOIN
					(
						SELECT DATEPART(DAYOFYEAR,K.Date) bd
						FROM #Dates K
						WHERE K.Date>='20100101' AND K.Date<=DATEADD(DD,31,'20100101')								
					) F ON (D.Date>=@currdate AND D.Date<=@enddate) AND dateadd(day,F.bd,D.Date)<=dateadd(day,1,@enddate) 
					cross join 
					(
						SELECT 0 optionsid, 0 groupid
					--	UNION ALL
					--	SELECT 1 optionsid, 1 groupid
					) M



	set @newexceptions = 1
	set @cnt = 0
	
	while @newexceptions = 1
	begin
		set @newexceptions = 0;
		set @cnt=@cnt+1;
		with 
		 bests as (
						 select a.id allocationid, x.dd, x.id periodid
						 from #allocations a
						 outer apply( 
										select MIN(p.id) id, D.Date dd
										from #periods p inner join #Dates d on
										p.DateFrom<=d.Date and p.DateTo>=d.Date 
										where a.disGroup=p.disGroup and a.optionsid=p.optionsid and a.DateFrom<=d.Date and a.DateTo>=d.Date
										and not exists
										(
											select *
											from #exceptions e
											where a.id=e.allocationid and e.periodid=p.id and e.dd=d.Date
										)
										group by d.Date
									) x
						where not exists 
						(
							select * 
							from #results l
							where l.allocationid=a.id
						)									
			
					  )
					  
		  ,ranges as (
						select m.allocationid, m.periodid,  m.dd as datefrom, 
						(
							select MIN(x.dd) 
							from bests x 
							where m.allocationid=x.allocationid and m.periodid=x.periodid and x.dd>=m.dd and not exists 
							(
								select *
								from bests k
								where k.allocationid=x.allocationid and k.periodid=x.periodid and k.dd=DATEADD(DAY,1,x.dd)
							)	
						) 
						as dateto 
						from (
								select b.allocationid,b.dd, b.periodid
								from bests b
								where not exists
								(
									select *
									from bests x
									where x.allocationid=b.allocationid and x.periodid=b.periodid and x.dd=DATEADD(DAY,-1,b.dd)
								)
							) m	
					 )
		  

			  INSERT INTO #results
			  (
				allocationid,
				datefrom, 
				dateto, 
				minstay, 
				freedays,
				periodid,
				checked			  
			  )
			  select r.allocationid, r.datefrom, r.dateto, p.minstay,p.freedays,r.periodid, 0 
			  from ranges r inner join #periods p on 
			  r.periodid = p.id
			  
		  
			  insert into #exceptions
			  (
				allocationid,
				dd, 
				periodid
			  )
			  select r.allocationid, d.Date,r.periodid
			  from #results r inner join #Dates d on
			  r.datefrom<=d.Date and r.dateto>=d.Date
			  where DATEDIFF(DAY,r.datefrom,r.dateto)<r.minstay
			  
			  
			  select @newexceptions =0-- @@ROWCOUNT

				delete r
				from #results r
				where DATEDIFF(DAY,r.datefrom,r.dateto)<r.minstay

				update #results set checked=1

	  
	 end 



			select * from #results



select @cnt
drop table #allocations
drop table #exceptions
drop table #periods
drop table #results
drop table #Dates


При этом в качестве параметров (SELECT 0 optionsid, 0 groupid) - среднее значение это около 100 не повторяющихся комбинаций.
У кого-нибудь есть идеи как можно оптимизировать актуальный запрос (пока не обращая внимание на цикл)? Спасибо!

Сообщение было отредактировано: 15 сен 14, 17:11
15 сен 14, 14:59    [16576656]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
Это не алгоритм - это бред.

ЗЫ. Не надо тупо следовать неправильным примерам.
15 сен 14, 15:09    [16576736]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2, я лучше на данный момент придумать не умею. Если бы вы решали данную задачу, то каким путем шли бы?
15 сен 14, 15:17    [16576777]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
cavalero
aleks2, я лучше на данный момент придумать не умею. Если бы вы решали данную задачу, то каким путем шли бы?


1. Научись описывать свои алгоритмы словами. Ибо никто не будет вникать в громадье чужого кода. Разве тока на работе делать нечего...
2. Шо касаемо "правильного алгоритма", то
а) я бы нашел пересечение всех имеющихся интервалов размещения с искомым интервалом и выкинул бы все пересечения негодной длины
б) взял бы самое "подходящее из получившихся пересечений"
в) усек бы "искомый интервал"
г) повторил бы с а), пока "искомый интервал" не пуст.
15 сен 14, 17:10    [16577564]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
-- периоды
declare @periods table (id int identity primary key clustered, datefrom smalldatetime, dateto smalldatetime, minstay int, freedays int)

INSERT INTO @periods
(
	datefrom,
	dateto,
	minstay,
	freedays
)
SELECT '20150101',
		'20150117',
		5,
		1
UNION ALL
SELECT '20150110',
		'20150117',
		7,
		1
UNION ALL
SELECT '20150101',
		'20150130',
		1,
		0;

-- найденные
declare @found table (d0 smalldatetime, d1 smalldatetime, pid int, lvl int);

-- искомые
declare @find table (d0 smalldatetime, d1 smalldatetime, lvl int, fid int identity  );

declare @d0 datetime = '20150101', @d1 datetime = '20150120';

insert @find(d0, d1, lvl) values(@d0, @d1, 0);

declare @rc int = 1, @lvl int = 0;

while @rc > 0
begin
 ;with
	-- все пересечения
	isc as (select p.*
	              , f.fid 
				  , ( select max(x) from (values(p.datefrom), (f.d0)) y(x) ) as d0  
				  , ( select min(x) from (values(p.dateto), (f.d1)) y(x) ) as d1  
			  from  @periods p 
			        inner join ( select * from @find where lvl = @lvl ) f
			        on p.datefrom < f.d1 and f.d0 < p.dateto 
			)
	-- годные пересечения
	, vics as ( select *, DATEDIFF(DAY, d0, d1) dd from isc where DATEDIFF(DAY, d0, d1) > minstay )
	-- упорядоченные пересечения
	, ovics as ( select *, ROW_NUMBER() over(partition by fid order by minstay desc, freedays desc ) as n from vics )
	-- самые лучшие пересечения
	insert @found select d0, d1, id, @lvl from ovics where n = 1;
	
	set @rc = @@ROWCOUNT;
	
--	select 'found', * from @found where lvl = @lvl;
--	select 'find', * from @find where lvl = @lvl;
	
	insert @find(d0, d1, lvl) 
	   select f.d0, ff.d0, @lvl+1
	     from ( select * from @find where lvl = @lvl ) f
	          inner join (select * from @found where lvl = @lvl ) ff on f.d0 < ff.d0 and ff.d1 <= f.d1;

	insert @find(d0, d1, lvl) 
	   select ff.d1, f.d1, @lvl+1
	     from ( select * from @find where lvl = @lvl ) f
	          inner join (select * from @found where lvl = @lvl ) ff on f.d0 <= ff.d0 and ff.d1 < f.d1;
	
	set @lvl = @lvl + 1;

end;

select 'не нашли', * from @find where lvl = @lvl;
select 'нашли', * from @found;
15 сен 14, 18:11    [16577849]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
aleks2, спасибо за решение! Я попробую его адаптировать под реальный расчет с бОльшим количеством вариантов размещения, посмотрим что получится в плане производительности.
PS: скажите пожалуйста, как давно и как активно вы занимаетесь разработкой под SQL Server?
16 сен 14, 12:28    [16580289]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
cavalero
PS: скажите пожалуйста, как давно и как активно вы занимаетесь разработкой под SQL Server?

сейчас вы офигеете честно слово
16 сен 14, 12:31    [16580306]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
cavalero
Member

Откуда: Прага
Сообщений: 247
Maxx
сейчас вы офигеете честно слово


я готов :)
16 сен 14, 12:35    [16580335]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
cavalero
Maxx
сейчас вы офигеете честно слово


я готов :)

ну тогда ждем откровений от дядм Саши
16 сен 14, 12:36    [16580339]     Ответить | Цитировать Сообщить модератору
 Re: Выбор периодов для расчета цен  [new]
aleks2
Guest
Maxx
cavalero
PS: скажите пожалуйста, как давно и как активно вы занимаетесь разработкой под SQL Server?

сейчас вы офигеете честно слово


см. 44 страницу
https://www.sql.ru/forum/afsearch.aspx?s=aleks2&submit=?????&bid=1
16 сен 14, 12:48    [16580428]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить