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

Откуда:
Сообщений: 49
Добрый день!

Помогите, пожалуйста с решением следующей задачи:
Есть таблица цен спектаклей в разные дни. Колонки ID, DATE, PRICE.
Нужно получить отчет со средней стоимостью спектакля в окрестостях каждой даты. Скажем, +/- один день.

Пример. Дано:
ID DATE PRICE
1 01.10.13 100
2 02.10.13 130
3 03.10.13 80
4 04.10.13 90
5 05.10.13 120
6 06.10.13 110
7 07.10.13 105

Решение:
DATE AVG_PRICE
01.10.13 115
02.10.13 103,33
03.10.13 100
04.10.13 96,66
05.10.13 106,66
06.10.13 111,66
07.10.13 107,5

Если это будет проще, то можно по краям (т.е. для дат по которым нет полной окрестности) не считать.
Сам, думаю, можно сделать так:
SELECT DATE
,(SELECT AVG(PRICE)
FROM TABLE T2
WHERE T2.DATE BETWEEN DATEADD(DAY, -1, T1.DATE) AND DATEADD(DAY, 1, T1.DATE)) AS AVG_PRICE
FROM TABLE T1

Что-то подсказывает, что можно воспользоваться аналитическими функциями, но не могу понять как описать PARTITION.
Спасибо.
14 окт 13, 23:08    [14970010]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Извините, вот таблицы в удобном виде:

Дано:
ID DATE PRICE
1 01.10.13 100
2 02.10.13 130
3 03.10.13 80
4 04.10.13 90
5 05.10.13 120
6 06.10.13 110
7 07.10.13 105

Решение:
DATE AVG_PRICE
01.10.13 115
02.10.13 103,33
03.10.13 100
04.10.13 96,66
05.10.13 106,66
06.10.13 111,66
07.10.13 107,5

14 окт 13, 23:19    [14970063]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
И запрос с отступами:

SELECT DATE
,(SELECT AVG(PRICE)
FROM TABLE T2
WHERE T2.DATE BETWEEN DATEADD(DAY, -1, T1.DATE) AND DATEADD(DAY, 1, T1.DATE)) AS AVG_PRICE
FROM TABLE T1
14 окт 13, 23:27    [14970107]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
?
Guest
select avg(price), date
from

(select price, date, 1 as xz from T
union all 
select price, DATEADD(DAY, -1, T1.DATE), 0 from T
union all 
select price, DATEADD(DAY, 1, T1.DATE), 0 from T) a

group by date
having sum(xz)>0
14 окт 13, 23:30    [14970124]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Ваш запрос на выходе возвращает в точности исходную таблицу (без ИД).
Но смысл примерно понятен. У вас есть основания, что так будет эффективнее, чем мой вариант?
14 окт 13, 23:49    [14970198]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Извините, я ошибся. Ваш запрос работает правильно. Спасибо.
Т.е., если понадобиться расширить окрестность, то придется дописывать код? Ок. А что, если нужно сделать, чтоб влиятельность была больше, чем ближе к основной дате? Подойдет любая функция, например распределение Гаусса.
14 окт 13, 23:58    [14970238]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Вообще нет, не правильно. Вот результат Вашего запроса:

2013-10-01 103
2013-10-02 103
2013-10-04 105
2013-10-05 106
2013-10-06 111
2013-10-07 107
15 окт 13, 00:02    [14970267]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
?
Guest
forrfor
Вообще нет, не правильно. Вот результат Вашего запроса:

2013-10-01 103
2013-10-02 103
2013-10-04 105
2013-10-05 106
2013-10-06 111
2013-10-07 107

... даже не представляю, что ж такого нужно было "нахомутать" с этим многостродальным запросом, что бы такое получить ...
declare @t table (ID int, DATE datetime, PRICE money)

set dateformat dmy

insert into @t (ID, DATE, PRICE)
select 1,	'01.10.13',	100 union all
select 2,	'02.10.13',	130 union all
select 3,	'03.10.13',	80  union all
select 4,	'04.10.13',	90  union all
select 5,	'05.10.13',	120 union all
select 6,	'06.10.13',	110 union all
select 7,	'07.10.13',	105

----------
select * from @t

print '-----------'

select DATE, avg(PRICE) as avg_PRICE
from

(select DATE, PRICE, 1 as xz from @t
union all
select dateadd(day, 1, DATE), PRICE, 0 from @t
union all
select dateadd(day, -1, DATE), PRICE, 0 from @t) a

group by DATE
having sum(xz)>0

(7 row(s) affected)
ID          DATE                    PRICE
----------- ----------------------- ---------------------
1           2013-10-01 00:00:00.000 100,00
2           2013-10-02 00:00:00.000 130,00
3           2013-10-03 00:00:00.000 80,00
4           2013-10-04 00:00:00.000 90,00
5           2013-10-05 00:00:00.000 120,00
6           2013-10-06 00:00:00.000 110,00
7           2013-10-07 00:00:00.000 105,00

(7 row(s) affected)

-----------
DATE                    avg_PRICE
----------------------- ---------------------
2013-10-01 00:00:00.000 115,00
2013-10-02 00:00:00.000 103,3333
2013-10-03 00:00:00.000 100,00
2013-10-04 00:00:00.000 96,6666
2013-10-05 00:00:00.000 106,6666
2013-10-06 00:00:00.000 111,6666
2013-10-07 00:00:00.000 107,50

(7 row(s) affected)


ТС, и на будующее,
раз у вас такая плохая карма, что вы предложенное решение и проверить не можете - выкладывайте исх.данные скриптами
вот чего спрашивается, мне их "набивать" что бы вам что-то доказывать ... ?
15 окт 13, 00:28    [14970381]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Спасибо большое, учту. Еще раз прошу извинить, с кем не бывает.

И все же, позвольте у Вас уточнить, какой подход Вы предлагаете, если необходимо расширить окрестности? А так же подтвердите, пожалуйста, неприменимость в данном случае оконных функций.

Спасибо.
15 окт 13, 00:52    [14970422]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
?
Guest
forrfor
Спасибо большое, учту. Еще раз прошу извинить, с кем не бывает.

И все же, позвольте у Вас уточнить, какой подход Вы предлагаете, если необходимо расширить окрестности?

self join я бы предложил
select T1.DATE, avg(T2.PRICE) as avg_PRICE
from @t T1 
inner join @t T2
  on T1.DATE between dateadd(day, -2, T2.DATE) and dateadd(day, 2, T2.DATE)
group by T1.DATE


forrfor
А так же подтвердите, пожалуйста, неприменимость в данном случае оконных функций.


подтвердить я не могу,
но для СКЛ2005, например, я вариантов решения такой задачи, с оконной функцией не знаю

и, главное - необходимости в ней не вижу
15 окт 13, 01:20    [14970444]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
forrfor
Member

Откуда:
Сообщений: 49
Спасибо! Ваш последний запрос - остановлюсь на нем.
15 окт 13, 01:34    [14970465]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
Добрый Э - Эх
Guest
forrfor
Что-то подсказывает, что можно воспользоваться аналитическими функциями, но не могу понять как описать PARTITION.
Спасибо.
нужно не PARTITION прикручивать к аналитике, а (диапазонное | строковое) окно (использовать конструкцию rows | range between 1 row prectding and 1 row following).
15 окт 13, 05:08    [14970524]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
Добрый Э - Эх
Guest
--
-- Тестовый набор данных:
with t(ID, DATE, PRICE) as (
select 1, '01.10.13',	100 union all
select 2, '02.10.13',	130 union all
select 3, '03.10.13',	80  union all
select 4, '04.10.13',	90  union all
select 5, '05.10.13',	120 union all
select 6, '06.10.13',	110 union all
select 7, '07.10.13',	105)
--
-- Основной запрос:
select ID, DATE, PRICE,
       avg(price) over(order by date 
                           rows between 1 preceding 
                                    and 1 following) as avg_price
  from t
on-line проверка на sqlfiddle.com
15 окт 13, 05:23    [14970531]     Ответить | Цитировать Сообщить модератору
 Re: Средняя цена в окрестностях даты  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
--
-- Тестовый набор данных:
with t(ID, DATE, PRICE) as (
select 1, '01.10.13',	100 union all
select 2, '02.10.13',	130 union all
select 3, '03.10.13',	80  union all
select 4, '04.10.13',	90  union all
select 5, '05.10.13',	120 union all
select 6, '06.10.13',	110 union all
select 7, '07.10.13',	105)
--
-- Основной запрос:
select ID, DATE, PRICE,
       avg(price) over(order by date 
                           rows between 1 preceding 
                                    and 1 following) as avg_price
  from t
on-line проверка на sqlfiddle.com
преобразование типов данных забыл прикрутить:
       avg(price*1.0) over(order by date 
15 окт 13, 05:26    [14970532]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить