I have a table like so:
| Day | Value |
|---|---|
| 1 | 3 |
| 1 | 5 |
| 1 | 1 |
| 2 | 4 |
| 2 | 7 |
| 3 | 1 |
| 3 | 1 |
| 3 | 2 |
| 3 | 5 |
How do I create a rolling average that takes into account all previous days to produce a table like so:
| Day | Rolling_avg |
|---|---|
| 1 | 3 |
| 2 | 4 |
| 3 | 3.22 |
Day1 = avg(day 1 values)
Day2 = avg(day1 + day2 values)
Day3 = avg(day1 + day2 + day3 values)
so on so forth..thank you!
First aggregate by day to get the sum of values and counts for each day. Then use analytic functions to find the rolling averages.
WITH cte AS (
SELECT Day, SUM(Value) ValueSum, COUNT(*) AS Count
FROM yourTable
GROUP BY Day
)
SELECT Day, SUM(ValueSum) OVER (ORDER BY Day) /
SUM(Count) OVER (ORDER BY Day) AS Rolling_avg
FROM cte
ORDER BY Day;