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 
--------+-------+------------+------+------
Canada  | 1980  |       1764 | 1764 | 1764
Canada  | 1981  |       1610 | 1610 | 1764
Canada  | 1982  |       1590 | 1590 | 1764
Canada  | 1983  |       1661 | 1590 | 1764
Canada  | 1984  |       1775 | 1590 | 1775
Canada  | 1985  |       1812 | 1590 | 1812
(6 rows)  

The preceding example shows that min and max will use the same clause.

Of course, it is possible to have more than just one WINDOW clause—PostgreSQL does not impose serious restrictions on users here.