SQL Pocket Guide, 2nd Edition

Window Functions

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.

Defining a Summary Window (Oracle, DB2, SQL Server)

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 northing values:

n1

The northing value for the waterfall described by the current row

n2

The lowest northing value of any waterfall in the same county

n3

The average northing value of all waterfalls

n4

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 ...