Скользящая средняя за 3 месяца — Redshift SQL

Я пытаюсь создать 3-месячную скользящую среднюю на основе некоторых данных, которые у меня есть при использовании RedShift SQL или Domo BeastMode (если кто-то знаком с этим).

Данные ежедневные, но должны отображаться по месяцам. Таким образом, котировки/выручка должны быть суммированы по месяцам, а затем необходимо рассчитать 3MMA (исключая текущий месяц).

Итак, если бы котировка была в апреле, мне нужно было бы среднее значение за январь, февраль, март.

Входные данные выглядят так:

Quote Date MM/DD/YYYY     Revenue
3/24/2015                 61214
8/4/2015                  22983
9/3/2015                  30000
9/15/2015                 171300
9/30/2015                 112000

И мне нужно, чтобы вывод выглядел примерно так:

Month               Revenue             3MMA
Jan 2015            =Sum of Jan Rev     =(Oct14 + Nov14 + Dec14) / 3
Feb 2015            =Sum of Feb Rev     =(Nov14 + Dec14 + Jan15) / 3
March 2015          =Sum of Mar Rev     =(Dec14 + Jan15 + Feb15) / 3
April 2015          =Sum of Apr Rev     =(Jan15 + Feb15 + Mar15) / 3
May 2015            =Sum of May Rev     =(Feb15 + Mar15 + Apr15) / 3

Если кто-то может помочь, буду очень благодарен! Я застрял на этом довольно долгое время и понятия не имею, что я делаю, когда дело доходит до SQL, лол.

Привет, Логан.


sql amazon-redshift domo
person user2427023    schedule 20.03.2016    source источник
comment
Ваши форматы даты несовместимы.   -  person Gordon Linoff    schedule 21.03.2016

Ответы (3)


arrow_upward
3
arrow_downward

Вы можете сделать это с помощью агрегации и оконных функций:

select date_trunc('month', quotedate) as mon,
       sum(revenue) as mon_revenue,
       avg(sum(revenue)) over (order by date_trunc('month', quotedate)  rows between 2 preceding and current row) as revenue_3mon
from t
group by date_trunc('month', quotedate) 
order by mon;

Примечание: здесь используется среднее значение, поэтому для первой и второй строки оно будет делиться на 1 и 2 соответственно. Также предполагается, что у вас есть хотя бы одна запись для каждого месяца.

РЕДАКТИРОВАТЬ:

Интересно, есть ли проблема с функциями агрегации, смешанными с аналитическими функциями в RedShift. Лучше ли следующее:

select m.*,
       avg(mon_revenue) over (order by mon rows between 2 preceding and current row) as revenue_3mon
from (select date_trunc('month', quotedate) as mon,
             sum(revenue) as mon_revenue
      from t
      group by date_trunc('month', quotedate) 
     ) m
order by mon;
person Gordon Linoff    schedule 20.03.2016
comment
Привет, Гордон, спасибо за ответ :) нажал Enter слишком рано... сейчас редактирую. - person user2427023; 21.03.2016
comment
Привет, Гордон, спасибо за ваш ответ :) Похоже, он уже на пути к тому, что я хочу, однако вывод 3MMA не выглядит на 100% правильным. Это то, что он сейчас выводит. 01.09.2012 00:00:00 17777 17777 01.01.2013 00:00:00 182367 82466 01.09.2013 00:00:00 3120 324974 01.12.2013 00:00:00 2918343 3333393 Дата также не выглядит так, как будто она упорядочивается должным образом, когда я иду дальше по данным. Любые предложения были бы замечательными! Привет, Логан. - person user2427023; 21.03.2016
comment
к сожалению, похоже, что он дает тот же результат :( В настоящее время я устанавливаю Postgre Server на свой компьютер, поэтому я могу убедиться, что это не проблема Domo/Redshift. Даже в другом сценарии группа по/порядок по командам вызывают проблемы. - person user2427023; 21.03.2016

arrow_upward
0
arrow_downward

вы можете сделать что-то вроде того, как мы создаем сегменты на скользящие 6 недель (столбец даты называется «дата»):

case 
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,1,current_date)) then 'CW'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-6,current_date)) then 'LW'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-13,current_date)) then '2W'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-20,current_date)) then '3W'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-27,current_date)) then '4W'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-34,current_date)) then '5W'
    when date_trunc('week',dateadd(day,1,date)) = date_trunc('week',dateadd(day,-41,current_date)) then '6W'  
end as dateweek

Затем вы можете создать среднее значение на следующем этапе потока данных...

person WHM    schedule 02.03.2017

arrow_upward
0
arrow_downward

Вы не можете использовать агрегатные функции и аналитические функции вместе, запрос должен быть

select m.*,
       avg(mon_revenue) over (order by mon rows between 3 preceding and 1 preceding) as revenue_3mon -- using 3 preceding and 1 preceding row you exclude the current row
from (select date_trunc('month', quotedate) as mon,
             sum(revenue) as mon_revenue
      from t
      group by date_trunc('month', quotedate) 
     ) m
order by mon;

строки между 3 предыдущими и 1 предыдущими (следует удалить строку в конце, иначе красное смещение не сработает)

person sushmit    schedule 31.08.2016