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

Откуда:
Сообщений: 27
Доброе время суток, Уважаемые знатоки!!!
Прошу помощи (совета).
Задача.
Дано:
Несколько таблиц со столбцами
1) Значение (Цена)
2) Время
3) Тип (Название)

Необходимо написать универсальную процедуру, к.т. можно применить к разным таблицам.
Процедура должна высчитывать среднеарифметическое за период n (n- параметр).
Т.е. в итоге у нас должна появиться таблица или массив со значениями.

Пример n=3
X={x[1],x[2]…..x[m]}
x[i]=(x[i]+x[i-1]+x[i-2])/3
X-массив который надо получить.
19 янв 12, 16:12    [11933837]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Glory
Member

Откуда:
Сообщений: 104751
И что у вас не получается ?
19 янв 12, 16:13    [11933857]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
да ничего не получается - автор никогда не видел SQL Server.

автор
x[i]=(x[i]+x[i-1]+x[i-2])/3

рекурсия? )
19 янв 12, 16:25    [11933974]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Не могу сообразить, как сделать красивую процедуру, к.т. быстро и качественно работала при большом n и большом количестве записей.

Скажем при n=2 можно просто написать процедуру

Select (price1+(‘выборка предыдущей цены по time1’))/2, time1
From tab1

И все будет работать, но 'выборка предыдущей цены по time1’ будет сильно тормозить выполнение. Да и как написать процедуру для n=100.
19 янв 12, 16:26    [11933995]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Glory
Member

Откуда:
Сообщений: 104751
Vit1986
Скажем при n=2 можно просто написать процедуру

Select (price1+(‘выборка предыдущей цены по time1’))/2, time1
From tab1

Для уже давно есть функция AVG()
19 янв 12, 16:30    [11934043]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Glory, Как я понимаю, AVG – встроенная функция.
И ее можно использовать следующим образом
Select time1, Avg1
From (‘вычисляется AVG(price) для n значений перед time2 ‘)

Но, для разработки этого мало.
Прошу прощения, я не уточнил, что необходима своя процедура вычисления среднеарифметического значения.

Т.е. среднеарифметическое значение это в простом варианте.

В более сложном варианте, нам надо вычислить
А) x[i]=(3*x[i]+2*х[i-1]+x[i-2])/6
Б) x[i]=cos(x[i])+sin(х[i-1])+cos(x[i-2])
В) что угодно с использованием предыдущих значений

Еще раз прошу прощения, что не указал это в описании.
19 янв 12, 16:48    [11934236]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Vit1986
А) x[i]=(3*x[i]+2*х[i-1]+x[i-2])/6

а это ничего, что Xi является функцией от Xi?
Vit1986
x[i]=cos(x[i])+sin(х[i-1])+cos(x[i-2])

косинус цены? а цена у вас в градусах или радианах?
19 янв 12, 16:56    [11934330]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Jovanny
Member

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

А i - это номер строки, наверное?
19 янв 12, 16:58    [11934344]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Зайцев Фёдор
Vit1986
А) x[i]=(3*x[i]+2*х[i-1]+x[i-2])/6

а это ничего, что Xi является функцией от Xi?
Vit1986
x[i]=cos(x[i])+sin(х[i-1])+cos(x[i-2])

косинус цены? а цена у вас в градусах или радианах?




x[i]=(3*x[i]+2*х[i-1]+x[i-2])/6
следует понимать как
x'[i]=(3*x[i]+2*х[i-1]+x[i-2])/6
прошу прощения за опечатку.

Цена взята лишь для примера.
19 янв 12, 17:02    [11934384]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Vit1986, очень советую выложить нормальный скрипт для создания таблиц с тестовыми данными, а также пример результата сабжа
19 янв 12, 17:06    [11934409]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Jovanny
Vit1986,

А i - это номер строки, наверное?



Нет,
i- Соответствует значению, по которому в данный момент происходит вычисление.
i-1 предыдущему значению
и т.д.

т.е. обычная математическая формула
19 янв 12, 17:09    [11934435]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Vit1986, это вариации на тему нарастающего итога чтоль?
19 янв 12, 17:26    [11934581]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
скрипт для создания таблиц
CREATE DATABASE [example]
go

CREATE TABLE [example].[dbo].[First_] (
	[Price_] [real] NOT NULL ,
	[Name_] [char] NOT NULL,
	[DateTime_] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [example].[dbo].[second_] (
	[Price_] [real] NOT NULL ,
	[Name_] [char] NOT NULL,
	[DateTime_] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1000,'A','10:01:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1020,'A','10:02:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1040,'A','10:03:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (10100,'B','10:04:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1020,'A','10:05:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1060,'A','10:06:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (10320,'B','10:07:00')
INSERT INTO [example].[dbo].[second_] (Price_, Name_,DateTime_) VALUES (1100,'A','10:08:00')

INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (100,'A','11:01:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (102,'A','11:02:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (104,'A','11:03:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (1010,'B','11:04:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (102,'A','11:05:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (106,'A','11:06:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (1032,'B','11:07:00')
INSERT INTO [example].[dbo].[First_] (Price_, Name_,DateTime_) VALUES (110,'A','11:08:00')


Пример.
Нужно написать процедуру с МАТЕМАТИЧЕСКОЙ формулой (для примера)
y[i]=n*x[i]+(n-1)*x[i-1]+….+x[i-n+1]

Применяем данную процедуру к таблице First_ с параметром n=3, Name_='A'
Получаем:
Time 		Price
11:03:00	616
11:05:00	616
11:06:00	626
11:08:00	644

Если расписать формулу
104*3+102*2+100=616
102*3+104*2+102=616
106*3+102*2+104=626
110*3+106*2+102=644

Данную процедуру мы можем применить к другой таблице и с другими параметрами.

……………………………………………………………….

Математическая формула может быть другой:
1) y[i]=x[i]+(2)*x[i-1]+….+n*x[i-n+1]
2) y[i]=sin(x[i])+cos(x[i-1]) +sin(x[i-2])+cos(x[i-3])+….+sin(x[i-n+1])
3) и т.д.

т.е. процедура должна быть как можно универсальней.
19 янв 12, 18:22    [11935116]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
kDnZP
Vit1986, это вариации на тему нарастающего итога чтоль?


Нет, в первом описании опечатка вместо левой части нужно применить
y[i]
т.е.
y[i]=(x[i]+x[i-1]+x[i-2])/3
y[i]=(3*x[i]+2*х[i-1]+x[i-2])/6
y[i]=cos(x[i])+sin(х[i-1])+cos(x[i-2])
19 янв 12, 18:26    [11935146]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Vit1986
скрипт для создания таблиц

версия сервера какая?
19 янв 12, 18:27    [11935156]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1
19 янв 12, 18:31    [11935185]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
Попробовал решить задачу в лоб.
Если добавить столбец NUMBER – порядковый номер для каждого Name_.
То примерное решение для суммы n последних значений:

CREATE FUNCTION [dbo].[testFF](@n1 int, @n2 as int)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int;
IF @n2>0
	begin
		set @ISOweek=(SELECT max(Price_)
		FROM dbo.second_
		WHERE number = @n1 and Name_='A')
		set @ISOweek=@ISOweek+[dbo].[testFF](@n1-1,@n2-1)
	end
else
	begin
		set @ISOweek=(SELECT max(Price_)
		FROM dbo.second_
		WHERE number = @n1 and Name_='A')
	end		
RETURN (@ISOweek)
END
GO


Кто может, посоветуйте, как можно улучшить?
21 янв 12, 20:23    [11945793]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
вы можете создать свою аггрегирующую функцию - написав на бейсике )) я про CLR
22 янв 12, 07:32    [11946884]     Ответить | Цитировать Сообщить модератору
 Re: среднеарифметическое  [new]
Vit1986
Member

Откуда:
Сообщений: 27
BusyMan
вы можете создать свою аггрегирующую функцию - написав на бейсике )) я про CLR



Как вы считаете, агрегирующая функция является самой оптимальной для быстрого вычисления?

Возможно ли, представить столбец в виде массива и работать уже с массивом через функцию?
Т.е.
Указатель	     Price
X[1]    -->	     1000
X[2]    -->	     1020
X[3]    -->	     1040
X[4]    -->	     1020
X[5]    -->	     1060..

При добавлении Price, появляется X[n+1] указывающий на этот Price
22 янв 12, 17:37    [11947924]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить