CODEWITHSUNDEEP
sqlsql-servert-sql
twmp
twmp

Reputation: 115

Finding moving average by attribute by date

I am using the following table structure as example: City Key, Invoice Date, Profit I need to find moving average for each city using last 5 days and following 5 days.

Am I using the right script ?

SELECT  [City Key],[Invoice Date Key], AVG([Profit])
OVER(ORDER BY [City Key],[Invoice Date Key] ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS d10_Mov_avg

FROM Fact.Sale
ORDER BY [City Key], [Invoice Date Key]

The script is working but I tried to double check in Excel and got different results, but I don't understand why and I am wondering if I get wrong result because of data, not script itself.

Upvotes: 0

Views: 145

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need PARTITION BY:

SELECT [City Key],[Invoice Date Key],
       AVG([Profit]) OVER (PARTITION BY [City Key]
                           ORDER BY [Invoice Date Key]
                           ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING
                          ) AS d10_Mov_avg

FROM Fact.Sale
ORDER BY [City Key], [Invoice Date Key];

This also assumes that you have a day for every city. If not, using window functions for this is challenging.

Upvotes: 2

Related Questions