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

Откуда:
Сообщений: 27
Подскажите как вывести все средние по заказам м/у датами т.е.:
Есть таблица
DOCUMENTS (ID_DOC, NAM_COMPANI, DATE_ZAKAZ)
STROKA (ID_DOC, KOLV, CENA, SUMMA)
Таблицы связанны по полю ID_DOC

В таблице DOCUMENTS Компания и дата продажы, в таблице STROKA по каждому документу сумма и количество товара

Нужно вывести Наименование компаний и среднее количество купленного ими товара между каждыми двумя заказами за последние 6 месяцев
4 июл 13, 15:13    [14522824]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Glory
Member

Откуда:
Сообщений: 104751
Используйте функцию AVG(), например
4 июл 13, 15:17    [14522855]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
Glory, AVG вычислит общее среднее за все время или указанный интервал, а мне нужно между каждыми 2-мя заказами т.е

что то типа того:

1. Компания, дата1, дата2, среднее м/у ними
1. Компания, дата1, дата2, среднее м/у ними

ну или без второй даты
4 июл 13, 15:27    [14522949]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Glory
Member

Откуда:
Сообщений: 104751
ostoha
Glory, AVG вычислит общее среднее за все время или указанный интервал, а мне нужно между каждыми 2-мя заказами т.е

AVG вычислит то, что попадет в запрос, который вы напишите
4 июл 13, 15:29    [14522966]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
Glory, так у меня проблема с составлением запроса и сравнением предыдущего значения даты (строки) с текущим и есть )) не пойму с чего начать
4 июл 13, 15:31    [14522975]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
ostoha
Glory, так у меня проблема с составлением запроса и сравнением предыдущего значения даты (строки) с следующим и т.д. и есть )) не пойму с чего начать
4 июл 13, 15:31    [14522983]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Glory
Member

Откуда:
Сообщений: 104751
ostoha
не пойму с чего начать

С чтения Рекомендаций по оформлению сообщений. П.4 и п.6
4 июл 13, 15:32    [14522985]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
Пока у меня есть вот что:
SELECT k1.Nam_komp AS "Компания", AVG(d2.Kolv) "Ср.кол-во", d1.Date "Дата заказа", d2.Item "Тип продукта"
FROM dbo.KLIENTS k1
INNER JOIN DOCUMENT d1 ON d1.ID_KOMP = k1.ID_KOMP
INNER JOIN STROKA d2 ON d2.ID_DOC = d1.ID_DOC
WHERE d1.Date > DATEADD(MONTH,6,04-07-2013)
GROUP BY k1.Nam_komp, d1.Date, d2.Item
ORDER BY k1.Nam_komp


Выводится:

Компания Ср.кол-во Дата заказа Тип продукта
Клиент1 30 2013-05-05 00:00:00.000 1
Клиент1 30 2013-06-05 00:00:00.000 4
Клиент3 30 2013-04-04 00:00:00.000 1
Клиент3 50 2013-05-04 00:00:00.000 4
Клиент3 50 2013-06-04 00:00:00.000 4
Клиент3 50 2013-06-04 00:00:00.000 8
4 июл 13, 15:53    [14523172]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
ostoha
Нужно вывести Наименование компаний и среднее количество купленного ими товара между каждыми двумя заказами за последние 6 месяцев

честно говоря сломал мозг.. А это задание где-то записано? Не могли бы вы дословно перепечатать его? И да, Глори вам подсказал пункты 4 и 6 в правилах
4 июл 13, 15:58    [14523214]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
Microsoft SQL Server 2008
Рассчитать среднее дневное потребление между заказами по зарегистрированным заказам за последние 6 месяцев от даты расчета

KLIENTS
(
ID_KOMP,
NAM_COMPANI,
ADDRESS
)

DOCUMENTS
(
ID_DOC,
ID_KOMP,
DATE_ZAKAZ
)

STROKA
(
ID_DOC,
ITEM,
KOLV,
CENA,
SUMMA
)

ID_KOMP (KLIENTS) Связанно с ID_KOMP (DOCUMENTS )
ID_DOC (DOCUMENTS) Связанно с ID_DOC (STROKA)


SELECT k1.NAM_COMPANI AS "Компания", AVG(d2.KOLV) "Ср.кол-во", d1.DATE_ZAKAZ "Дата заказа", d2.ITEM "Тип продукта"
FROM dbo.KLIENTS k1
INNER JOIN DOCUMENT d1 ON d1.ID_KOMP = k1.ID_KOMP
INNER JOIN STROKA d2 ON d2.ID_DOC = d1.ID_DOC
WHERE d1.DATE_ZAKAZ > DATEADD(MONTH,6,04-07-2013)
GROUP BY k1.NAM_COMPANI, d1.DATE_ZAKAZ, d2.ITEM
ORDER BY k1.NAM_COMPANI



Что я смог
Компания Ср.кол-во Дата заказа Тип продукта
Клиент1302013-05-05 00:00:00.000 1
Клиент1302013-06-05 00:00:00.000 4
Клиент3302013-04-04 00:00:00.000 1
Клиент3502013-05-04 00:00:00.000 4
Клиент3502013-06-04 00:00:00.000 4
Клиент3502013-06-04 00:00:00.000 8
4 июл 13, 16:25    [14523400]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
ostoha,
пронумеруйте в разрезе компании заказы по дате (ROW_number() ). Потом свяжите получившееся множество само с собой по компании и номеру= номер -1 . Собственно получите уже пары заказов. Далее найти среднее арифметическое между ними не сложно.
4 июл 13, 16:32    [14523453]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Добрый Э - Эх
Guest
ostoha,

решение во многом будет зависеть от версии сервера, которую ты благополучно не сообщил.
НА MS SQL Server 2012 я бы заюзал lead / lag. На более ранних версиях - "классические" способы их эмуляции.
5 июл 13, 04:31    [14525250]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

Откуда:
Сообщений: 27
Добрый Э - Эх, ответил 2 сообщения назад читай внимательней 2008...

Вот что у меня на сегодны получилось

USE Test3

SELECT C1.NAM_COMPANI, T2.Item AS "Тип", 
	T.DATE_ZAKAZ AS "Предыдущий заказ", T1.DATE_ZAKAZ AS "Следующий заказ", T2.KOLV,
	DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ) AS "Кол-во дней",
	((T2.KOLV*1000)/DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ)) AS "Кол-во т.д. (мл)",
	DATEADD(day, DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ), T1.DATE_ZAKAZ) AS "Дата звонка",
	DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013) "Едрит тест отбора за 6 мес"
FROM dbo.DOCUMENTS T 
INNER JOIN dbo.DOCUMENTS AS T1 ON T.ID_DOC+1 = T1.ID_DOC
INNER JOIN dbo.STROKA AS T2 ON T.ID_DOC = T2.ID_DOC
INNER JOIN dbo.KLIENTS AS C1 ON T.ID_KOMP = C1.ID_KOMP
WHERE 
	t.ID_KOMP = t1.ID_KOMP AND 
	C1.IsActive = 1 AND
	T.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013) AND
	T1.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013)
GROUP BY C1.NAM_COMPANI, T2.Item, T.DATE_ZAKAZ, T1.DATE_ZAKAZ, T2.KOLV
HAVING	T.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013) AND
		T1.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013)


Теперь выводится все правильно и то что надо но, как сделать отбор за последние 6 месяцев?
Вот эти строки как изменить:
Либо:
WHERE
T.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013) AND
T1.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013)

Либо
HAVING T.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013) AND
T1.DATE_ZAKAZ > DATEADD(day, DATEDIFF(MONTH, 6, 05-07-2013), 05-07-2013)

А то как видно в таблице отнимается не месяцы а х.з. что...
NAM_COMPANI Тип Предыдущий заказ Следующий заказ KOLV Кол-во дней Кол-во т.д. (мл) Дата звонка Едрит тест отбора за 6 мес
Клиент1 1 2013-05-05 00:00:00.000 2013-06-06 00:00:00.000 30 32 937 2013-07-08 00:00:00.000 1894-04-20 00:00:00.000
Клиент3 1 2013-04-07 00:00:00.000 2013-05-08 00:00:00.000 30 31 967 2013-06-08 00:00:00.000 1894-04-20 00:00:00.000
Клиент3 4 2013-05-08 00:00:00.000 2013-06-09 00:00:00.000 50 32 1562 2013-07-11 00:00:00.000 1894-04-20 00:00:00.000
Клиент3 8 2013-06-09 00:00:00.000 2013-07-10 00:00:00.000 50 31 1612 2013-08-10 00:00:00.000 1894-04-20 00:00:00.000
5 июл 13, 06:38    [14525278]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
ostoha
Member

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

В общем сделал сам

USE Test3
SELECT DATEADD(MONTH,-1,'20130705')
SELECT C1.NAM_COMPANI, T2.Item AS "Тип", 
	T.DATE_ZAKAZ AS "Предыдущий заказ", T1.DATE_ZAKAZ AS "Следующий заказ", T2.KOLV,
	DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ) AS "Кол-во дней",
	((T2.KOLV*1000)/DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ)) AS "Кол-во т.д. (мл)",
	DATEADD(day, DATEDIFF("d", T.DATE_ZAKAZ, T1.DATE_ZAKAZ), T1.DATE_ZAKAZ) AS "Дата звонка"
FROM dbo.DOCUMENTS T 
INNER JOIN dbo.DOCUMENTS AS T1 ON T.ID_DOC+1 = T1.ID_DOC
INNER JOIN dbo.STROKA AS T2 ON T.ID_DOC = T2.ID_DOC
INNER JOIN dbo.KLIENTS AS C1 ON T.ID_KOMP = C1.ID_KOMP
WHERE 
	t.ID_KOMP = t1.ID_KOMP AND 
	C1.IsActive = 1
GROUP BY C1.NAM_COMPANI, T2.Item, T.DATE_ZAKAZ, T1.DATE_ZAKAZ, T2.KOLV
HAVING	T.DATE_ZAKAZ > DATEADD(MONTH,-6,'20130705') AND
		T1.DATE_ZAKAZ > DATEADD(MONTH,-6,'20130705')


Может конечно можно и лучше и более красиво, но по старинному и работает))
5 июл 13, 07:10    [14525293]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по полю между датами  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ostoha
В общем сделал сам

FROM dbo.DOCUMENTS T 
INNER JOIN dbo.DOCUMENTS AS T1 ON T.ID_DOC+1 = T1.ID_DOC

Плохо сделали. Где гарантии, что ID_DOC идёт строго последовательно, без дырок?
5 июл 13, 16:34    [14528858]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить