Learning PostgreSQL 11 - Third Edition

The WINDOW clause

Window definitions can be quite long, and in many cases, it isn't convenient to use them in the select-list. Several window functions can use the same or similar window definitions. PostgreSQL provides a way to define windows and give them names that can be used in the OVER clause in window functions. This is done using the WINDOW clause of the SELECT statement, which is specified after the HAVING clause, as follows:

SELECT count() OVER w, sum(b) OVER w,   avg(b) OVER (w ORDER BY c ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)FROM table1WINDOW w AS (PARTITION BY a)

The predefined window can be used as is. In the preceding example, the count and sum window functions do so. The window definition can also be further detailed, like ...