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

Откуда:
Сообщений: 96
 		Есть таблица с двумя столбцами - в первом первое число каждого месяца, во втором количество. Необходимо для каждой строки рассчитать среднее за последние три месяца.
Например для '2012-08-01' среднее равно (21+46+35)/3

create table #tmp (dt date, cnt int);

insert into #tmp values ('2012-06-01', 35);
insert into #tmp values ('2012-07-01', 46);
insert into #tmp values ('2012-08-01', 21);
insert into #tmp values ('2012-09-01', 34);
insert into #tmp values ('2012-10-01', 77);
insert into #tmp values ('2012-11-01', 32);
insert into #tmp values ('2012-12-01', 34);
insert into #tmp values ('2013-01-01', 56);
insert into #tmp values ('2013-02-01', 12);
insert into #tmp values ('2013-04-01', 56);
insert into #tmp values ('2013-05-01', 12);
16 июн 14, 17:17    [16170984]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
 
set nocount on
go
if object_id('tempdb..#tmp') is not null
    drop table #tmp
    
create table #tmp (dt date, cnt int);

insert into #tmp values ('2012-06-01', 35);
insert into #tmp values ('2012-07-01', 46);
insert into #tmp values ('2012-08-01', 21);
insert into #tmp values ('2012-09-01', 34);
insert into #tmp values ('2012-10-01', 77);
insert into #tmp values ('2012-11-01', 32);
insert into #tmp values ('2012-12-01', 34);
insert into #tmp values ('2013-01-01', 56);
insert into #tmp values ('2013-02-01', 12);
insert into #tmp values ('2013-04-01', 56);
insert into #tmp values ('2013-05-01', 12);

select
    *
from    #tmp    a
outer apply (
    select
        avrg = avg(x.cnt)
    from (
        select top 3
            x.cnt
        from    #tmp    x
        where
            x.dt <= a.dt
        order by
            x.dt desc        
    ) x            
) b
order by
    a.dt
16 июн 14, 17:24    [16171043]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
 		SQL2012:
SELECT *,AVG(cnt)OVER(ORDER BY dt ROWS 2 PRECEDING) AS [AVG] FROM #tmp;
16 июн 14, 17:37    [16171168]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 653
 		Вот же народ разбаловался apply-ями и прочими "наворотами" :)

Select 
  t.dt, AVG(tbefore.cnt)
from #tmp t 
 join #tmp tbefore on tbefore.dt between dateadd(month,-3, t.dt) and t.dt
group by t.dt
order by 1
16 июн 14, 17:38    [16171174]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
 		Гавриленко Сергей Алексеевич,

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

set nocount on
go
if object_id('tempdb..#tmp') is not null
    drop table #tmp
    
create table #tmp (dt date, cnt int);

insert into #tmp values ('2012-06-01', 35);
insert into #tmp values ('2012-07-01', 46);
insert into #tmp values ('2012-08-01', 21);
insert into #tmp values ('2012-09-01', 34);
insert into #tmp values ('2012-10-01', 77);
insert into #tmp values ('2012-11-01', 32);
insert into #tmp values ('2012-12-01', 34);
insert into #tmp values ('2013-01-01', 56);
insert into #tmp values ('2013-02-01', 12);
insert into #tmp values ('2013-04-01', 56);
insert into #tmp values ('2013-05-01', 12);

select
    *
from    #tmp    a
outer apply (
    select
        avrg = avg(x.cnt)
    from (
        select top 3
            x.cnt
        from    #tmp    x
        where
            x.dt BETWEEN dateadd(month,-2, a.dt) and a.dt
        order by
            x.dt desc        
    ) x            
) b
order by
    a.dt
16 июн 14, 17:39    [16171175]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
iap
SQL2012:
SELECT *,AVG(cnt)OVER(ORDER BY dt ROWS 2 PRECEDING) AS [AVG] FROM #tmp;


При наличии дырок в данных это даст ошибку.
16 июн 14, 17:40    [16171184]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
Mikle83
Вот же народ разбаловался apply-ями и прочими "наворотами" :)

Select 
  t.dt, AVG(tbefore.cnt)
from #tmp t 
 join #tmp tbefore on tbefore.dt between dateadd(month,-3, t.dt) and t.dt
group by t.dt
order by 1



 dateadd(month,-2, t.dt)
16 июн 14, 17:42    [16171201]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 653
a_voronin
Mikle83
Вот же народ разбаловался apply-ями и прочими "наворотами" :)

Select 
  t.dt, AVG(tbefore.cnt)
from #tmp t 
 join #tmp tbefore on tbefore.dt between dateadd(month,-3, t.dt) and t.dt
group by t.dt
order by 1



 dateadd(month,-2, t.dt)


Согласен, хотя последние три месяца - довольно размытое понятие - три с учетом "текущего" или три предыдущих месяца?
Но не суть. можно хоть переменную засунуть и каждый раз получать нужные значения, чего запросы с apply-ями и прочими Over Order by-ями не позволят...
16 июн 14, 17:47    [16171258]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
автор
три месяца - довольно размытое понятие

Не такое уж и размытое - коммерческие запросы подразумевают только зарытые периоды отчетности.
16 июн 14, 18:29    [16171471]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Merdoc
Member

Откуда: Новосибирск
Сообщений: 103
 		Владислав Колосов,

Но лучше не гадать и уточнять у заказчика )
17 июн 14, 05:19    [16173036]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 653
Владислав Колосов
Не такое уж и размытое - коммерческие запросы подразумевают только зарытые периоды отчетности.

Везет, если в системе реально закрывается период и нет задач от бизнеса "сделать механизм возможности корректировки закрытого периода". Само собой с мегасложной схемой согласования/введения/подтверждения таких корректировок.

Тут больше другой вопрос интересен - что делать, если реально есть "не рабочий" месяц, т.е. последовательность значений такая

insert into #tmp values ('2012-06-01', 35);
insert into #tmp values ('2012-07-01', 46);


insert into #tmp values ('2012-10-01', 77);
insert into #tmp values ('2012-11-01', 32);


Что показывать на 01.10? Среднее за последние три рабочих месяца? Вот этот момент у ТСа не раскрыт.
Возможно, это перестраховка, но я бы на месте ТС уточнил этот кейс у бизнеса, дабы не было потом мучительно больно при изменении алгоритма.
17 июн 14, 09:56    [16173640]     Ответить | Цитировать Сообщить модератору
 Re: Скользящее среднее?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
 		Корректировки закрытого периода выставляются текущим периодом и в статистику закрытых попадают при условии попадания в эти три месяца. Т.е. корректировка также должна находиться в закрытом периоде отчетности. Вот насчет того, что такое "последние" месяцы действительно надо уточнить.
17 июн 14, 11:24    [16174342]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить