Mastering PostgreSQL 12 - Third Edition

Abstracting window clauses

A windowing function allows us to add columns to the result set that has been calculated on the fly. However, it is a frequent phenomenon that many columns are based on the same window. Putting the same clauses into your queries over and over again is definitely not a good idea, because your queries will be hard to read and therefore hard to maintain.

The WINDOW clause allows developers to predefine a window and use it in various places in the query. Here is how it works:

SELECT country, year, production,        min(production) OVER (w),        max(production) OVER (w) FROM t_oil WHERE country = 'Canada'       AND year BETWEEN 1980       AND 1985 WINDOW w AS (ORDER BY year); country | year | production | min | max --------+-------+------------+------+------ ...