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

Question

Я пытаюсь создать 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 LOL.

Ура, Логан.

Answer 1

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

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;

Answer 2

Вы могли бы сделать что-то вроде того, как мы создаем сегменты для скользящих 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

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

Answer 3

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

select m.*,
       avg(mon_revenue) over (order by mon rows between 3 preceding and 1 preceding row) 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;