Data is noisy. Daily sales spike on weekends, drop on holidays, and fluctuate randomly. To see the real trend, analysts use Moving Averages (or Rolling Averages).
A moving average takes the current value and averages it with a set number of previous values.
The Window Function Formula
To calculate a moving average in SQL, you use the standard AVG() aggregate function but apply it over a "window" of rows.
AVG(column) OVER (
ORDER BY date_column
ROWS BETWEEN X PRECEDING AND CURRENT ROW
)
ORDER BY date_column: Ensures rows are processed in chronological order.ROWS BETWEEN: Defines the "frame size".- For a 7-Day Moving Average, you want today + the previous 6 days. So:
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. - For a 3-Day Moving Average:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
- For a 7-Day Moving Average, you want today + the previous 6 days. So:
Real-World Example: Daily Stock Prices
Let's look at a volatile stock price and smooth it out.
1. Daily Volatility
First, observe the raw daily prices.
2. Calculating the 3-Day Moving Average
Now, let's calculate the 3-day moving average. For Jan 3rd, it will average prices from Jan 1, 2, and 3.
Result Analysis:
- On Jan 4, the price dropped to $90.
- The Moving Average was
(102+104+90)/3 = 98.66. - Notice how the moving average ($98.66) is less extreme than the drop ($90), showing the smoothing effect.
Handling the "Start" of the Window
You might notice that the first row is just the average of itself, and the second row is the average of 2 days. The window grows until it hits the full size (3 days).
If you want to strictly enforce that you ONLY calculate when you have full 3 days of data, you'd need slightly more complex logic (often involving COUNT() OVER (...) to check the window size), but for most visual trend/charting purposes, the growing window at the start is acceptable.
Centered vs Trailing Windows
The examples above differ from "Centered" moving averages, which look ahead and behind.
- Trailing (Standard):
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW - Centered:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Centered averages are good for historical analysis but impossible for real-time forecasting (since you don't know tomorrow's price yet!).
Practice Challenge
Try modifying the query to calculate a Cumulative Average (running average of all time) by removing the ROWS clause entirely or using ROWS UNBOUNDED PRECEDING.
Conclusion
Window functions with frame specifications (ROWS BETWEEN) give you precise control over rolling calculations. Whether it's stock prices, website traffic, or server load, Moving Averages help you ignore the noise and focus on the signal.