Now we have discussed ordered sets, it is time to take a look at windowing functions. Aggregates follow a fairly simple principle: take many rows and turn them into fewer, aggregated rows. A windowing function is different. It compares the current row with all rows in the group. The number of rows returned does not change.
Here is an example:
test=# SELECT avg(production) FROM t_oil;
avg
-----------
2607.5139
(1 row) test=# SELECT country, year, production, consumption, avg(production) OVER () FROM t_oil LIMIT 4; country | year | production | consumption | avg ---------+-------+------------+-------------+---------- USA | 1965 | 9014 | 11522 | 2607.5139 USA | 1966 | 9579 | 12100 | 2607.5139 USA ...
