SQL Query for 7 Day Rolling Average in SQL Server

SQL Query for 7 Day Rolling Average in SQL Server

Try:

select x.*,
       avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg
  from (select productid, date, sum(usagecount) as dailyusage
          from tbl
         group by productid, date) x

Fiddle:

http://sqlfiddle.com/#!6/f674a7/4/0

Replace "avg(dailusage) over...." with sum (rather than avg) if what you really want is the sum for the past week. In your title you say you want the average but later you say you want the sum. The query should be the same other than that, so use whichever you actually want.

As was pointed out by Gordon this is basically the average of the past 6 dates in which the product was used, which might be more than just the past 6 days if there are days without any rows for that product on the table because it wasn't used at all. To get around that you could use a date table and your products table.

How to calculate moving average in SQL?

I think you want something like this :

SELECT *,
       (SELECT Sum(output)
        FROM   table1 b
        WHERE  b.week IN( a.week, a.week - 1, a.week - 2 )) AS SUM
FROM   table1 a 

OR

In clause can be converted to between a.week-2 and a.week.

sql fiddle

Calculate a Recursive Rolling Average in SQL Server

Credit for the idea goes to this answer: https://stackoverflow.com/a/35152131/6305294 from @JesúsLópez

I have included comments in the code to explain it.

UPDATE

• I have corrected the query based on comments.
• I have swapped numbers in minuend and subtrahend to get difference as a positive number.
• Removed Diff2Ago column.

Results of the query now exactly match your sample output.

;WITH cte AS
(
    -- This is similar to your ItemWithComparison view
    SELECT i.Number, i.Value1, i2.Value1 AS ComparisonValue1,
        -- Calculated Differences; NULL will be returned when i.Value1 is NULL
        CONVERT( DECIMAL( 10, 3 ), i.Value1 - i2.Value1 ) AS Diff
    FROM Item AS i
            LEFT JOIN [Group] AS G ON g.ID = i.GroupID
            LEFT JOIN Item AS i2 ON i2.GroupID = g.ComparisonGroupID AND i2.Number = i.Number
    WHERE NOT i2.Id IS NULL
),
cte2 AS(
    /*
    Start with the first number

    Note if you do not have at least 2 consecutive numbers (in cte) with non-NULL Diff value and therefore Diff1Ago or Diff2Ago are NULL then everything else will not work;
    You may need to add additional logic to handle these cases */
    SELECT TOP 1 -- start with the 1st number (see ORDER BY)
            a.Number, a.Value1, a.ComparisonValue1, a.Diff, b.Diff AS Diff1Ago
    FROM cte AS a
            -- "1 number ago"
            LEFT JOIN cte AS b ON a.Number - 1 = b.Number
    WHERE NOT a.Value1 IS NULL
    ORDER BY a.Number
    UNION ALL
    SELECT b.Number, b.Value1, b.ComparisonValue1,
            ( CASE
                WHEN NOT b.Value1 IS NULL THEN b.Diff
                ELSE CONVERT( DECIMAL( 10, 3 ), ( a.Diff + a.Diff1Ago ) / 2.0 )
            END ) AS Diff,
        a.Diff AS Diff1Ago
    FROM cte2 AS a
        INNER JOIN cte AS b ON a.Number + 1 = b.Number
)
SELECT *, ( CASE WHEN Value1 IS NULL THEN ComparisonValue1 + Diff ELSE Value1 END ) AS NewValue1
FROM cte2 OPTION( MAXRECURSION 0 );

Limitations:
this solution works well only when you need to consider small number of preceding values.

How to do a moving average on a range of dates in SQL?

The MySQL example below covers a sliding 7-day window:

select t1.`DATE`, AVG(t2.`VALUE`) as MV_AVG
from MyTable t1
left outer join MyTable t2 
    on t2.`DATE` between DATE_ADD(t1.`DATE`, INTERVAL -6 DAY) 
        and t1.`DATE`
group by t1.`DATE`

SQL Fiddle Example

Output:

|                             DATE |    MV_AVG |
------------------------------------------------
|    August, 12 2012 20:00:00+0000 |       160 |
|    August, 19 2012 20:00:00+0000 |        52 |
|    August, 26 2012 20:00:00+0000 |        63 |
| September, 03 2012 20:00:00+0000 |        41 |
| September, 09 2012 20:00:00+0000 |      30.5 |
| September, 16 2012 20:00:00+0000 |        20 |
| September, 23 2012 20:00:00+0000 |       285 |
| September, 24 2012 20:00:00+0000 |     152.5 |
| September, 30 2012 20:00:00+0000 |      52.5 |
|   October, 08 2012 20:00:00+0000 |        41 |
|   October, 14 2012 20:00:00+0000 |      6037 |
|   October, 15 2012 20:00:00+0000 |      6610 |
|   October, 16 2012 20:00:00+0000 | 5624.6667 |
|   October, 21 2012 20:00:00+0000 | 1649.6667 |
|   October, 28 2012 20:00:00+0000 |        31 |
|  November, 04 2012 19:00:00+0000 |        10 |

SQL Server : moving average calculation

Thank you! I solved it as following:

SELECT  [Brand],
        [month],
        cast(sum([Volume]) AS INT) as [Volume (t)],
        AVG(cast(sum([Volume])AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)],

        CASE WHEN (Row_Number() OVER (ORDER BY [month]))>3
        THEN AVG(cast(sum([Volume])AS INT)) 
                    OVER (PARTITION BY [Brand] ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) 
                    ELSE NULL END AS [Moving Average (3)]
............

---

Related Topics