After discussing 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 | 1967 | 10219 | 12567 | 2607.5139
USA | 1968 | 10600 | 13405 | 2607.5139
(4 rows)
The average...