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

Откуда:
Сообщений: 16
Всем, привет!
Есть таблица что то вроде
CREATE TABLE TempTable
(
TransDate	datetime,
Amount		numeric(28, 12),
Bad		bit
)
TransDate - рабочие дни (без суббот и воскресений)
Amount - сумма
Bad - считать или не считать.
Необходимо расчитать среднее значение поля Amount (AVG(Amount)) за последние Х дней начиная от заданной даты Y где поле Bad = 0 запросом без цикла. Страшеная проблема из-за условия "Х дней".
Уже всю голову поломал, ничего придумать немогу ...
Спасите, помогите :)
27 сен 11, 19:55    [11344634]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
maxodus, использовать для ограничения дат DATEDIFF() или DATEADD()?
27 сен 11, 19:57    [11344651]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
Shakill
Не пойдет. По условию не просто Х дней, а Х дней где Bad = 0
27 сен 11, 20:00    [11344664]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
select top (@x_дней)
  AVG(Amount)
from TempTable
where [Bad] = 0
order by TransDate desc	
?

Сообщение было отредактировано: 27 сен 11, 20:03
27 сен 11, 20:03    [11344679]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
Не может быть, что было все так просто! Сейчас попробую.
27 сен 11, 20:05    [11344684]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
mike909
Member

Откуда:
Сообщений: 662
Гавриленко Сергей Алексеевич
select top (@x_дней)
  AVG(Amount)
from TempTable
where [Bad] = 0
order by TransDate desc	
?

and TransDate < @от_заданной_даты_Y
27 сен 11, 20:08    [11344699]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
select
  AVG(Amount)
from TempTable
where 
  [Bad] = 0
  and TransDate between @y and dateadd(dd, @x, @y)
На самом деле, если @x дней - это @x любых дней. Если рабочих - то мой первый вариант, правда я условие с датой забыл.

Сообщение было отредактировано: 27 сен 11, 20:10
27 сен 11, 20:10    [11344707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
mike909
Гавриленко Сергей Алексеевич
select top (@x_дней)
  AVG(Amount)
from TempTable
where [Bad] = 0
order by TransDate desc	
?

and TransDate < @от_заданной_даты_Y


:( Не хочет. Говорит не хватает "ГРУП БАЙ". При добавлении оного, результат соответствующий :(

select
  AVG(Amount)
from TempTable
where 
  [Bad] = 0
  and TransDate between @y and dateadd(dd, @x, @y)
В любом случае не пойдет. Важно, чтобы выполнилось условие Х дней. Т.е. если это 50 дней, то обязательно 50, независимо от того, насколько далеко уйдет гаризонт расчета из-за условия [Bad] = 0. Например, если х = 10, y = 20.01.2011, то в наборе данных
Transdate 	Amount	Bad
-------------------------------------
20.01.2011 1325 0
19.01.2011 6521 0
18.01.2011 2150 0
17.01.2011 152562 0
16.01.2011 42321 0
15.01.2011 6541 1
14.01.2011 62165 1
13.01.2011 5165 0
12.01.2011 6565 0
11.01.2011 658 0
10.01.2011 652 0
09.01.2011 165 1
08.01.2011 41 1
07.01.2011 5132 0
06.01.2011 185956 0
05.01.2011 2165 0
04.01.2011 1235 0
03.01.2011 3565 0
02.01.2011 6512 0
01.01.2011 1235 0
В расчет среднего должны попасть записи 20.01.2011-16.01.2011, 13.01.2011-10.01.2011, 07.01.2011

Для корректности вопроса добавлю, что это подзапрос длинного update.
27 сен 11, 20:25    [11344751]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
Может выбрать все записи в отдельную таблицу по принципу Bad=0, расчитать там и потом вернуть значения? Но блин, у меня в TempTable 2 млн записей. :(
Может есть какойто еще вариант?
27 сен 11, 20:30    [11344777]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
Вот родилась такая идея.
CREATE TABLE TempTable
(
TransDate	datetime,
Amount		numeric(28, 12),
Bad		bit
)
...
CREATE TABLE TempTableClear
(
DateShift		int,
TransDate	datetime,
Amount		numeric(28, 12),
Bad		bit
)

INSERT TempTableClear 
SELECT row_number() OVER (ORDER BY TRANSDATE DESC),
TransDate, Amount, Bad
FROM TempTable WHERE BAD=0

...
SELECT @DateSH = DATESHIFT
from TempTableClear
where TransDate = @y

SELECT 
  AVG(Amount)
from TempTableClear
where TransDate = @y AND DateShift < (@DateSH + @x)
Это как направление в размышлениях. Но помоему - это какой-то моветон.
Помогите, люди добрые.
27 сен 11, 20:51    [11344848]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
последний запрос должен быть:
SELECT 
  AVG(Amount)
from TempTableClear
where TransDate < @y AND DateShift < (@DateSH + @x)
27 сен 11, 20:55    [11344865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
iljy
Member

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

select   AVG(Amount) from(
	select top (@x_дней) *
	from TempTable
	where [Bad] = 0 and TransDate < @от_заданной_даты_Y
	order by TransDate desc	
)t
27 сен 11, 22:43    [11345223]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
mike909
Member

Откуда:
Сообщений: 662
maxodus
В любом случае не пойдет. Важно, чтобы выполнилось условие Х дней. Т.е. если это 50 дней, то обязательно 50, независимо от того, насколько далеко уйдет гаризонт расчета из-за условия [Bad] = 0. Например, если х = 10, y = 20.01.2011, то
В расчет среднего должны попасть записи 20.01.2011-16.01.2011, 13.01.2011-10.01.2011, 07.01.2011

Для расчета StartDate:
declare @d int, @dtFrom datetime
select @d = 10, @dtFrom = '20110120'

select DATEADD(DD,-d.d, f.ds) as [Это левая граница]
from (select DATEadd(dd,-(@d + @d/7 * 2), @dtFrom) as ds) f
cross apply( select case DATEPART(WEEKDAY,f.ds) when 1 then 2 when 7 then 1 else 0 end as d ) as d
Ну а дальше between сами напишете
28 сен 11, 00:32    [11345461]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
mike909
Member

Откуда:
Сообщений: 662
mike909,
Полный диапазон:
declare @d int, @dtEnd datetime

select @d = 10, @dtEnd = '20110122'

select DATEADD(DD,-d.d, f.ds) as [dtFrom]
,case DATEPART(WEEKDAY,@dtEnd) when 1 then DateAdd(DD,1,@dtEnd) when 7 then DateAdd(DD,2,@dtEnd) else @dtEnd end as [dtEnd]
from (select DATEadd(dd,-(@d + @d/7 * 2), @dtEnd) as ds) f
cross apply( select case DATEPART(WEEKDAY,f.ds) when 1 then 2 when 7 then 1 else 0 end as d ) as d
28 сен 11, 00:46    [11345477]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
2 iljy: Не компилируется.
2 mike909: Извините, Вы меня неправильно поняли. На сколько я могу судить, Ваш вариант высчитывает рабочие дни. Так это не проблема. Проблема была в условии Bad=0.
На реальных таблицах это выглядит иначе, но в контексте моего вопроса, я проблему решил, таки через еще одну таблицу. Получилось что-то вроде:
CREATE TABLE TempTable
(
TransDate	datetime,
Amount		numeric(28, 12),
AVGAmount	numeric(28, 12),
Bad		bit
)
...
CREATE TABLE TempTableClear
(
DateShift		int, 
TransDate	datetime,
Amount		numeric(28,12),
AVGDAYS		int, 
AVGAMOUNT	numeric(28,12)
)

INSERT TempTableClear
SELECT row_number() OVER (ORDER BY TRANSDATE DESC),
TRANSDATE, 
AMOUNT,
@x,
0
FROM TempTable
WHERE BAD = 0;

UPDATE TempTableClear
SET 
	AVGAMOUN = (SELECT AVG(TC.Amount)
			FROM TempTableClear TC
			WHERE TC.TransDate < T.TRANSDATE 
				AND TC.DATESHIFT < (T.DATESHIFT + T.AVGDAYS))
	FROM TempTableClear T
	WHERE TRANSDATE = T.TRANSDATE;

UPDATE TempTable
	SET AVGAMOUN = TC.AVGAMOUN
FROM TempTableClear TC
WHERE TRANSDATE = TC.TRANSDATE

Работает.
Жаль только, что запрос так и неудалось построить, но это уже развлекуха. Буду ломать голову в свободное время.
Всем ОГРОМНОЕ спасибо за советы.
28 сен 11, 04:50    [11345582]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
aleks2
Guest
maxodus
2 iljy: Не компилируется.


С чего бы ему НЕ компилироваться? 2000-й сервер, чтоль?

declare @TempTable TABLE
(
TransDate datetime,
Amount numeric(28, 12),
Bad bit
)

declare @от_заданной_даты_Y datetime, @x_дней int

set @x_дней=3

select AVG(Amount) from(
select top (@x_дней) *
from @TempTable
where [Bad] = 0 and TransDate < @от_заданной_даты_Y
order by TransDate desc
) t
28 сен 11, 07:47    [11345664]     Ответить | Цитировать Сообщить модератору
 Re: Помогите избежать цикла с курсором, пожалуйста.  [new]
maxodus
Member

Откуда:
Сообщений: 16
Спасибо, aleks2.
Приношу свои извинения iljy.
Скорее всего, вчера я сделал ошибку при переносе кода из форума с студию. Ваш пример рабочий и считает правильно.
Вобщем, респект и уважуха :)
28 сен 11, 19:40    [11350956]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить