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

Откуда:
Сообщений: 16
Доброго времени суток, уважаемый All.
По сути, вопрос является продолжением моего предыдущего поста, на который ответил iljy. Не хочу показаться навящивым, но ситуация усложнилась, и безрезультатные поиски в Интернет подсказывают, что на самостоятельное решение у меня уйдет половина жизни.
Таблица:
CREATE TABLE TempTable
(
TransDate datetime,
ItemId varchar(10),
BranchId int,
Bad bit
);
TransDate - дата, ItemId - товар, BranchId - филиал, Bad -считаем\несчитаем. Уникальный ключ по комбинации TransDate, ItemId, BranchId. Существует таблица аналогичной структуры CalculatedTable.
Необходимо, для каждой комбинации (ItemId,BranchId) "выровнять" записи в таблице TempTable записями из CalculatedTable, таким образом, чтобы в результате в TempTable количество записей с полем Bad = 0 было равно параметру @X (тот-же что и предыдущем посте). При этом, соблюдая последовательность дат.
Возможно ли это сделать без цикла и если возможно, то как?

Это о причине такой задачи, в контексте предыдущего поста:
+

В предыдущем посте я говорил о расчете среднего значения. Теперь оказалось, что для расчета среднего не всегда хватает записей. Т.е. записей с полем Bad=1 бывает на столько много, что горизонт расчета выходит за рамки существующих в таблице данных. В настоящее время, я просто высчитываю максимальное количество Bad = 1 встречающееся в таблице для комбинации (ItemId,BranchId) и не глядя перекидываю данные в таблицу по всем комбинациям (ItemId,BranchId). База большая и такая неряшливость работает дооооолго. :(


Это скриптик, который создает вышеописанные таблицы с примерными данными:

+

CREATE TABLE TempTable
(
TransDate	datetime,
ItemId		varchar(10),
BranchId		int,
Bad		bit
);

INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','B',1,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','B',1,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','B',1,1);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','B',1,1);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','B',1,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','A',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','A',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','A',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','A',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','A',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','B',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','B',2,0);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','B',2,1);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','B',2,1);
INSERT TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','B',2,1);

CREATE TABLE CalculatedTable
(
TransDate	datetime,
ItemId		varchar(10),
BranchId		int,
Bad		bit
)
									
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','A',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','A',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','A',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','A',1,1);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','A',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','A',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','A',2,1);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','A',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','A',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','A',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','B',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','B',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','B',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','B',1,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','B',1,1);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','B',2,1);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','B',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','B',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','B',2,0);
INSERT CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','B',2,0);



Заранее, спасибо.
29 сен 11, 02:01    [11352222]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
aleks2
Guest
maxodus
В настоящее время, я просто высчитываю максимальное количество Bad = 1 встречающееся в таблице для комбинации (ItemId,BranchId) и не глядя перекидываю данные в таблицу по всем комбинациям (ItemId,BranchId). База большая и такая неряшливость работает дооооолго. :(


1. Ну да, а если вы начнете замороченно рассчитывать, а потом прекидывать - это будет быстрее чтоль?
2. Вы уж огласите великую ЛОГИЧЕСКУЮ цель это геморроя.
29 сен 11, 05:58    [11352293]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
maxodus
Member

Откуда:
Сообщений: 16
2 aleks2:
aleks2
1. Ну да, а если вы начнете замороченно рассчитывать, а потом прекидывать - это будет быстрее чтоль?


Текущая, не самая банальная, выборка данных возвращает 863 тысячи записей. Хотя необходимо добавить от силы несколько десятков тысяч (и то это по максимуму). Уменьшение на порядок количества данных не может не отразиться на производительности.

aleks2
2. Вы уж огласите великую ЛОГИЧЕСКУЮ цель это геморроя.


А в чем проблема? К чему здесь анатомические эпитеты и такой тон? Если Вас так пугает эта задача, просто не отвечайте на мой пост и все. Делов то ... И будет весьма "ЛОГИЧЕСКИ".
29 сен 11, 09:28    [11352580]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
SignOff
Member

Откуда: Stockholm
Сообщений: 424
автор
А в чем проблема? К чему здесь анатомические эпитеты и такой тон? Если Вас так пугает эта задача, просто не отвечайте на мой пост и все. Делов то ... И будет весьма "ЛОГИЧЕСКИ".


ИМХО, просто решать чьи то задачки без пользы для себя - тоже не интересно. А чтобы было интересно - важно знать контекст.
29 сен 11, 09:44    [11352653]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
maxodus
Member

Откуда:
Сообщений: 16
SignOff
ИМХО, просто решать чьи то задачки без пользы для себя - тоже не интересно. А чтобы было интересно - важно знать контекст.


Интерес может быть и в том, чтобы разобраться для себя. Самообразование, от нечего делать, от любви к головоломкам, если хотите. Да малоли причин может быть. Я ведь не настаиваю. При этом, всегда есть возможность промолчать или вежливо отказать.
29 сен 11, 09:49    [11352670]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
maxodus,

даже прочитав предыдущий пост, я не совсем понял что требуется. А именно постановку задачи "выровнять записи в таблице TempTable записями из CalculatedTable". Вы привели примеры тестовых таблиц, приведите еще пример результата который должен получиться после их выравнивания, так понятнее будет, имхо.
29 сен 11, 09:56    [11352698]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
maxodus
Member

Откуда:
Сообщений: 16
SomewhereSomehow,
Согласен, косноязыкая у меня формулировка получилась. Попробую исправиться.
Во временной таблице ведется расчет среднего значения. Структура таблицы указана выше TempTable (без полей неотносящихся к текущему вопросу). Среднее значение должно быть расчитано за определенное точное количество @X дней от даты @Y. X и Y зададаются параметрами. При этом, расчет среднего должен быть осуществлен по последним известным датам с признаком Bad=0. Расчет осуществляется не по всем записям таблицы, а для каждой уникальной комбинации (ItemId, BranchId, TransDate = @Y). Записи Bad=1 не должны учитываться. Т.о. каждая запись с полем Bad=1 увеличивает диапазон расчета (@X + 1). Расчитать среднее без цикла, мне помогли в предыдущем посте. Поэтому, я задаю вопрос в новом посте, и у примера нет поля Amount, сейчас оно не нужно. Но...

Выяснилось, что для некоторых комбинаций (ItemId, BranchId) количество Bad=1 на столько велико, что увеличивает диапазон расчета так, что он выходит за пределы данных в таблице TempTable. Условие "расчет за @X дней" должно быть выполнено обязательно. Т.о. необходимо (выровнять данные) дополнить таблицу TempTable записями из другой таблицы, так, чтобы их хватило для расчета за @X дней.
Если @X = 5, а @Y= '01/05/2011', то в примере, необходимо создать INSERT, который добавит в таблицу TempTable следуюущие записи из таблицы CalculatedTable:

TransDate	ItemId	BranchId	Bad
-----------------------------------------
12/29/2010 B 1 0
12/30/2010 B 1 0
12/29/2010 B 2 0
12/30/2010 B 2 0
12/31/2010 B 2 0

Т.е. добавит количество записей из таблицы CalculatedTable равное количеству записей таблицы TempTable с признаком Bad=1, с учетом комбинации (ItemId, BranchId) и сохраняя порядок, но не непрерывность, дат по убыванию.

Простите за многословность, старался как мог.
29 сен 11, 10:38    [11352958]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
maxodus,

кажется понял, а может вам вместо вставки использовать union в подзапросе?
Или в этом запросе 11345223, вместо AVG(Amount), посчитайте и запишите в переменные @sum1 = sum(Amount), @count1 = count(Amount), и сделайте второй такой же запрос @sum2, @count2@, только к таблице CalculatedTable заменив количество дней на @x - @count1 (количество дней которое уже было посчитано из первой таблицы). Если там пересечения по датам возможны, то еще и ограничить по дате во втором запросе. После этого, имея данные по двум таблицам в переменных, спокойно посчитаете общее среднее уже без всякого обращения к таблицам.
Имхо, ничего "довставлять" в любом случае не нужно.
29 сен 11, 11:06    [11353196]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
iljy
Member

Откуда:
Сообщений: 8711
maxodus,

в первом приближении как-то так.
declare @x int = 10
select * from(
	select *, ROW_NUMBER() over(partition by ItemId, BranchId order by r, TransDate desc) rr
	from(
		select 1 r, * from TempTable
			union all
		select 2 r, * from CalculatedTable
	)t
	where Bad = 0 and TransDate <= '20110105'
)t
where rr <= 10
если я правильно понял насчет "для каждой комбинации (ItemId,BranchId)". Дальше можно оптимизировать.
29 сен 11, 11:14    [11353284]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
aleks2
Guest
iljy
maxodus,

в первом приближении как-то так.
declare @x int = 10
select * from(
	select *, ROW_NUMBER() over(partition by ItemId, BranchId order by r, TransDate desc) rr
	from(
		select 1 r, * from TempTable
			union all
		select 2 r, * from CalculatedTable
	)t
	where Bad = 0 and TransDate <= '20110105'
)t
where rr <= 10
если я правильно понял насчет "для каждой комбинации (ItemId,BranchId)". Дальше можно оптимизировать.


1. Не. Union all - усе испортит.
2. Тредстартеру следует ТУПО
"высчитать количество Bad = 1 для КАЖДОЙ комбинации (ItemId,BranchId)" из TempTable
3. И заколбасить APPLY с запросом из предыдущей его темы.
29 сен 11, 12:08    [11353885]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2,

я об этом тоже думал. Но во-первых - писать много и лень, а во-вторых - все очень сильно зависит от индексов и прочего.
29 сен 11, 12:18    [11353994]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
iljy
Member

Откуда:
Сообщений: 8711
iljy,

в общем че-то такое получается
declare @x int = 10
select t1.ItemId, t1.BranchId, c from
	(select distinct ItemId, BranchId from TempTable) t1
		cross apply
	(
		select COUNT(*) c from(
			select top (@x) *
			from(
				select 1 r, * from TempTable
					union all
				select 2 r, * from CalculatedTable
			)t
			where t.Bad = 0 and t.TransDate <= '20110105'
				and t.BranchId = t1.BranchId and t.ItemId = t1.ItemId
			order by r, TransDate desc
		)t
	)t2
 
29 сен 11, 12:29    [11354102]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
aleks2
Guest
Херня получаецца. Проще надо быть.

set dateformat mdy -- вот ведь, блин, тредстартер даже дату вменяемо поставить не могет

declare @TempTable TABLE 
(
TransDate	datetime,
ItemId		varchar(10),
BranchId		int,
Bad		bit
);

INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','B',1,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','B',1,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','B',1,1);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','B',1,1);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','B',1,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','A',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','A',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','A',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','A',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','A',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/01/2011','B',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/02/2011','B',2,0);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/03/2011','B',2,1);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/04/2011','B',2,1);
INSERT @TempTable (TransDate, ItemId, BranchId, Bad) Values ('01/05/2011','B',2,1);

declare @CalculatedTable TABLE 
(
TransDate	datetime,
ItemId		varchar(10),
BranchId		int,
Bad		bit
)
									
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','A',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','A',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','A',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','A',1,1);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','A',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','A',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','A',2,1);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','A',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','A',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','A',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','B',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','B',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','B',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','B',1,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','B',1,1);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/27/2010','B',2,1);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/28/2010','B',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/29/2010','B',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/30/2010','B',2,0);
INSERT @CalculatedTable (TransDate, ItemId, BranchId, Bad) Values ('12/31/2010','B',2,0);


;with
[дефицит] as (select ItemId, BranchId, min(TransDate) TransDate, count(*) Cnt FROM @TempTable WHERE Bad=cast(1 as bit) GROUP BY ItemId, BranchId HAVING count(*)>0)

select X.*
FROM  [дефицит] D cross apply (select top (D.Cnt) *  from @CalculatedTable C where C.ItemId=D.ItemId AND C.BranchId=D.BranchId AND C.[Bad] = 0 and C.TransDate < D.TransDate order by TransDate desc) X

-- ну... INSERT в @TempTable сами напишите

29 сен 11, 13:26    [11354695]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
aleks2
Guest
хотя пардон - про @X забыл

declare @x int

set @x=6

;with
[дефицит] as (select ItemId, BranchId, min(TransDate) TransDate, @x-count(*) Cnt FROM @TempTable WHERE Bad=cast(0 as bit) GROUP BY ItemId, BranchId HAVING count(*)<@x)

select X.*
FROM  [дефицит] D cross apply (select top (D.Cnt) *  from @CalculatedTable C where C.ItemId=D.ItemId AND C.BranchId=D.BranchId AND C.[Bad] = cast(0 as bit) and C.TransDate < D.TransDate order by TransDate desc) X
29 сен 11, 13:35    [11354787]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
aleks2
Guest
maxodus
Интерес может быть и в том, чтобы разобраться для себя. Самообразование, от нечего делать, от любви к головоломкам, если хотите. Да малоли причин может быть.


1. Это не головоломка - это банальный идиотизм. А решение идиотских задач - отупляет.

2. Выражать неудовольствие вам невместно. Ему, панимаешь, интересно, а другие не имеют права и поинтересоваться.
29 сен 11, 18:21    [11357669]     Ответить | Цитировать Сообщить модератору
 Re: Опять сложный запрос с TOP (@var) ... Помогите, пожалуйста еще раз.  [new]
maxodus
Member

Откуда:
Сообщений: 16
2 SomewhereSomehow: Спасибо за идею. Развил в сторону union получилось без добавлений.

2 iljy: В точку! Жаль, что увидел Ваш пост уже после того как. Сэкономил бы массу времени.

2 aleks2: Незнаю сколько Вам лет, но ведете Вы себя как подросток в период полового созревания. Если Вы уже пережили этот розовый возраст, то ... это уже не лечится. В любом случае, учитывая правила форума, пункт "Содержание сообщений" подпункт "Запрещается", мой Вам ответ: ... :)

Всем, спасибо за участие. Разрешите откланяться.
29 сен 11, 20:38    [11358154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить