3个月移动平均线-Redshift SQL [英] 3 Month Moving Average - Redshift SQL

问题描述

我正在尝试根据我在使用RedShift SQL或Domo BeastMode(如果有人熟悉)时所获得的一些数据来创建3个月移动平均值.

I am trying to create a 3 Month Moving Average based on some data that I have while using RedShift SQL or Domo BeastMode (if anyone is familiar with that).

数据是每日数据，但需要按月显示.因此，报价/收入需要按月份进行汇总，然后需要计算3MMA(不包括当月月份).

The data is on a day to day basis, but needs to be displayed by month. So the quotes/revenue need to be summarized by month, and then a 3MMA needs to be calculated (excluding the current month).

因此，如果报价是四月，我将需要一月，二月，三月的平均值.

So, if the quote was in April, I would need the average of Jan, Feb, Mar.

输入数据如下:

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

我需要输出看起来像这样:

And I need the output to look something like this:

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哈哈时我正在做什么.

If anyone is able to help, I would be extremely grateful! I have been stuck on this for quite a while and have no idea what I'm doing when it comes to SQL lol.

干杯，洛根.

推荐答案

您可以使用聚合和窗口函数来做到这一点:

You can do this using aggregation and window functions:

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.它还假设您每个月至少有一个记录.

Note: this uses average, so for the first and second row, it will divide by 1 and 2 respectively. It also assumes that you have at least one record for each month.

我想知道RedShift中聚合函数与分析函数混合是否存在问题.以下是更好的方法吗?

I wonder if there is an issue with aggregation functions mixed with analytic functions in RedShift. Is the following any better:

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;

这篇关于3个月移动平均线-Redshift SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！