Window functions enable you to look at different levels of aggregation in the same result row. They make it easy to specify cumulative sum, moving average, share-of, and many other important calculations. Window functions are supported in Oracle (where they are known as analytic functions) and DB2 (where they are called OLAP functions), and they are partially supported in SQL Server 2005.
The defining role of a window function is to specify a window, or partition of rows, over which the function operates. You specify a window using the
OVER ( . . . ) clause, which you can apply to any of the aggregate functions listed earlier in Table 11. For example:
SELECT u.id, u.county_id, u.northing n1, MIN(u.northing) OVER (PARTITION BY u.county_id) n2, AVG(u.northing) OVER ( ) n3, MAX(u.northing) OVER (PARTITION BY u.open_to_public) n4 FROM upfall u;
Each row returned by this query will have the following four
The northing value for the waterfall described by the current row
The lowest northing value of any waterfall in the same county
The average northing value of all waterfalls
The highest northing value of any waterfall having the same "open to public" status
An OVER( ) clause with nothing between the parentheses simply denotes an aggregate function's use as a window function. No GROUP BY clause is necessary, and the specified summary value is returned in each detail row. The summary comprises ...