How to Calculate Running Averages

user_5560898 0
Viewed 97

Scenario:

I want to calculate the moving average for sales amount for the entire month that has taken place SO FAR. Each month has a different number of days. If the sales_amount is 0, that means that person has the day off and I need to ignore that value.

Sample Data:

sales_amount    date         
100            2021-04-01    
200            2021-04-02    
300            2021-04-03    
0              2021-04-04    
100            2021-04-05

The Final moving_average value is (100 + 200 + 300 + 100) / (4) since we are ignoring April 4th because it has a 0 sales amount.

What I have found on this site that doesn't meet my needs: https://www.sqlservercentral.com/articles/calculate-moving-averages-using-t-sql-in-sql-server but it is for a certain number of days, how can I apply this when each month has a different number of days and I want to ignore certain rows?

Expected Result:

sales_amount    date         moving_average
100            2021-04-01    100
200            2021-04-02    150
300            2021-04-03    200
0              2021-04-04    200
100            2021-04-05    175

What I have tried doing:

AVG (Sales_Amount) OVER (
Partition BY [Date]
Order by [Date]
,Rows Between Unbounded Preceding and Current Row)
tsql sql-server sql
4 Answers
  1. remove the PARTITION BY
  2. use NULLIF() on Sales_Amount since you are not interested in 0

also you have an extra comma before the ROWS

AVG ( NULLIF(Sales_Amount, 0) ) 
    OVER 
    (
        Order by [Date]
        Rows Between Unbounded Preceding and Current Row
    )
user_1856470

You can try to use 2 aggregate window functions to make it, one for counting your logic another for accumulation sales_amount

SELECT *,
       SUM(sales_amount) OVER(Order by [Date])/
       SUM(CASE WHEN sales_amount<>0 THEN 1 ELSE 0 END) OVER(Order by [Date]) moving_average
FROM T

sqlfiddle

user_51760710

You have a syntax error before the ROWS and don't need to partition by the date. But the ROWS is not actually needed. This should do what you want:

AVG(NULLIF(Sales_Amount, 0)) OVER (Order by [Date])

If you had duplicate dates, then you might want to specify a window frame -- but you would need to explain how the duplicates are handled.

user_11440350

You can use correlated queries, such that for all dates of inner query that is less than the outer query are considered for average calculation.

SELECT [sales_amount], [date], 
      (SELECT AVG(NULLIF(T2.sales_amount, 0)) 
       FROM your_table T2 
       WHERE T2.orderdate <= T1.orderdate
      ) AS 'moving_average'
FROM your_table T1
ORDER BY T1.date

It will consider all the rows that have a [date] less than or equal to current rows [date] and calculate the average for them. As @Squirrel has mentioned, the NULLIF(T2.sales_amount, 0) should skip the 0 values in average calculations.

user_83850010
Related