AVG() as an Analytic Function

The AVG() function can also be utilized as an analytic function to calculate moving averages over a defined set of rows. This is particularly useful in identifying trends or smoothing out fluctuations in time-series data. For example, to calculate a moving average of order amounts over the last three orders, you could use:

sqlCopy codeSELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM orders;

This query computes the average of the current order amount along with the two previous orders, providing a more stable view of sales trends. Moving averages are commonly used in forecasting and performance analysis, helping businesses to make informed decisions based on historical data.