SQL moving average

Question

How do you create a moving average in SQL?

Current table:

Date             Clicks 
2012-05-01       2,230
2012-05-02       3,150
2012-05-03       5,520
2012-05-04       1,330
2012-05-05       2,260
2012-05-06       3,540
2012-05-07       2,330

Desired table or output:

Date             Clicks    3 day Moving Average
2012-05-01       2,230
2012-05-02       3,150
2012-05-03       5,520          4,360
2012-05-04       1,330          3,330
2012-05-05       2,260          3,120
2012-05-06       3,540          3,320
2012-05-07       2,330          3,010

Answer 1

select t2.date, round(sum(ct.clicks)/3) as avg_clicks
from
(select date from clickstable) as t2,
(select date, clicks from clickstable) as ct
where datediff(t2.date, ct.date) between 0 and 2
group by t2.date

Example here.

Obviously you can change the interval to whatever you need. You could also use count() instead of a magic number to make it easier to change, but that will also slow it down.

Answer 2

General template for rolling averages that scales well for large data sets

WITH moving_avg AS (
  SELECT 0 AS [lag] UNION ALL
  SELECT 1 AS [lag] UNION ALL
  SELECT 2 AS [lag] UNION ALL
  SELECT 3 AS [lag] --ETC
)
SELECT
  DATEADD(day,[lag],[date]) AS [reference_date],
  [otherkey1],[otherkey2],[otherkey3],
  AVG([value1]) AS [avg_value1],
  AVG([value2]) AS [avg_value2]
FROM [data_table]
CROSS JOIN moving_avg
GROUP BY [otherkey1],[otherkey2],[otherkey3],DATEADD(day,[lag],[date])
ORDER BY [otherkey1],[otherkey2],[otherkey3],[reference_date];

And for weighted rolling averages:

WITH weighted_avg AS (
  SELECT 0 AS [lag], 1.0 AS [weight] UNION ALL
  SELECT 1 AS [lag], 0.6 AS [weight] UNION ALL
  SELECT 2 AS [lag], 0.3 AS [weight] UNION ALL
  SELECT 3 AS [lag], 0.1 AS [weight] --ETC
)
SELECT
  DATEADD(day,[lag],[date]) AS [reference_date],
  [otherkey1],[otherkey2],[otherkey3],
  AVG([value1] * [weight]) / AVG([weight]) AS [wavg_value1],
  AVG([value2] * [weight]) / AVG([weight]) AS [wavg_value2]
FROM [data_table]
CROSS JOIN weighted_avg
GROUP BY [otherkey1],[otherkey2],[otherkey3],DATEADD(day,[lag],[date])
ORDER BY [otherkey1],[otherkey2],[otherkey3],[reference_date];

Answer 3

I'm not sure that your expected result (output) shows classic "simple moving (rolling) average" for 3 days. Because, for example, the first triple of numbers by definition gives:

ThreeDaysMovingAverage = (2.230 + 3.150 + 5.520) / 3 = 3.6333333

but you expect 4.360 and it's confusing.

Nevertheless, I suggest the following solution, which uses window-function AVG. This approach is much more efficient (clear and less resource-intensive) than SELF-JOIN introduced in other answers (and I'm surprised that no one has given a better solution).

-- Oracle-SQL dialect 
with
  data_table as (
     select date '2012-05-01' AS dt, 2.230 AS clicks from dual union all
     select date '2012-05-02' AS dt, 3.150 AS clicks from dual union all
     select date '2012-05-03' AS dt, 5.520 AS clicks from dual union all
     select date '2012-05-04' AS dt, 1.330 AS clicks from dual union all
     select date '2012-05-05' AS dt, 2.260 AS clicks from dual union all
     select date '2012-05-06' AS dt, 3.540 AS clicks from dual union all
     select date '2012-05-07' AS dt, 2.330 AS clicks from dual  
  ),
  param as (select 3 days from dual)
select
   dt     AS "Date",
   clicks AS "Clicks",

   case when rownum >= p.days then 
       avg(clicks) over (order by dt
                          rows between p.days - 1 preceding and current row)
   end    
          AS "3 day Moving Average"
from data_table t, param p;

You see that AVG is wrapped with case when rownum >= p.days then to force NULLs in first rows, where "3 day Moving Average" is meaningless.

Answer 4

We can apply Joe Celko's "dirty" left outer join method (as cited above by Diego Scaravaggi) to answer the question as it was asked.

declare @ClicksTable table  ([Date] date, Clicks int)
insert into @ClicksTable
    select '2012-05-01', 2230 union all
    select '2012-05-02', 3150 union all
    select '2012-05-03', 5520 union all
    select '2012-05-04', 1330 union all
    select '2012-05-05', 2260 union all
    select '2012-05-06', 3540 union all
    select '2012-05-07', 2330

This query:

SELECT
    T1.[Date],
    T1.Clicks,
    -- AVG ignores NULL values so we have to explicitly NULLify
    -- the days when we don't have a full 3-day sample
    CASE WHEN count(T2.[Date]) < 3 THEN NULL
        ELSE AVG(T2.Clicks) 
    END AS [3-Day Moving Average] 
FROM @ClicksTable T1
LEFT OUTER JOIN @ClicksTable T2
    ON T2.[Date] BETWEEN DATEADD(d, -2, T1.[Date]) AND T1.[Date]
GROUP BY T1.[Date]

Generates the requested output:

Date             Clicks    3-Day Moving Average
2012-05-01       2,230
2012-05-02       3,150
2012-05-03       5,520          4,360
2012-05-04       1,330          3,330
2012-05-05       2,260          3,120
2012-05-06       3,540          3,320
2012-05-07       2,330          3,010

Answer 5

select *
        , (select avg(c2.clicks) from #clicks_table c2 
            where c2.date between dateadd(dd, -2, c1.date) and c1.date) mov_avg
from #clicks_table c1

Answer 6

--@p1 is period of moving average, @01 is offset

declare @p1 as int
declare @o1 as int
set @p1 = 5;
set @o1 = 3;

with np as(
select *, rank() over(partition by cmdty, tenor order by markdt) as r
from p_prices p1
where
1=1 
)
, x1 as (
select s1.*, avg(s2.val) as avgval from np s1
inner join np s2 
on s1.cmdty = s2.cmdty and s1.tenor = s2.tenor
and s2.r between s1.r - (@p1 - 1) - (@o1) and s1.r - (@o1)
group by s1.cmdty, s1.tenor, s1.markdt, s1.val, s1.r
)