SQL Pocket Guide, 3rd 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), DB2 (where they are called OLAP functions), SQL Server, and PostgreSQL.

Defining a Summary Window

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 in Table 1-6 (under Grouping and Summarizing). 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 all rows in the result set.

To gain the ...