The sort order you specify in the window specification defines the sort order of the rows over which the function is applied, not the ordering of the results.
For example, to compute the average sales for the months following the current month, order the rows by month:
SELECT StoreID, SMonth, ProdID, Sales, AVG(Sales) OVER (PARTITION BY StoreID ORDER BY SMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM sales_tbl;
Result:
StoreID SMonth ProdID Sales Remaining Avg(Sales) ------- ------ ------ --------- -------------------- 1001 6 C 30000.00 ? 1001 5 C 30000.00 30000.00 1001 4 C 25000.00 30000.00 1001 3 C 40000.00 28333.33 1001 2 C 25000.00 31250.00 1001 1 C 35000.00 30000.00
The default sort order is ASC for the computation. However, the results are returned in the reverse order.
To order the results, use an ORDER BY phrase in the SELECT statement. For example:
SELECT StoreID, SMonth, ProdID, Sales, AVG(Sales) OVER (PARTITION BY StoreID ORDER BY SMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM sales_tbl ORDER BY SMonth;
Result:
StoreID SMonth ProdID Sales Remaining Avg(Sales) ------- ------ ------ --------- -------------------- 1001 1 C 35000.00 30000.00 1001 2 C 25000.00 31250.00 1001 3 C 40000.00 28333.33 1001 4 C 25000.00 30000.00 1001 5 C 30000.00 30000.00 1001 6 C 30000.00 ?