An aggregate function on which a window specification is applied is called a window aggregate function. Without a window specification, aggregate functions return one value for all qualified rows examined. Window aggregate functions return a new value for each of the qualifying rows participating in the query.
Thus, the following SELECT statement, which includes the aggregate AVG, returns one value only: the average of sales.
SELECT AVG(sale) FROM monthly_sales; Average(sale) ------------- 1368
The AVG window function retains each qualifying row.
The following SELECT statement might return the results that follow.
SELECT territory, smonth, sales, AVG(sales) OVER (PARTITION BY territory ORDER BY smonth ROWS 2 PRECEDING) FROM sales_history; territory smonth sales Moving Avg(sales) --------- ------- ----- ----------------- East 199810 10 10 East 199811 4 7 East 199812 10 8 East 199901 7 7 East 199902 10 9 West 199810 8 8 West 199811 12 10 West 199812 7 9 West 199901 11 10 West 199902 6 8