Utilizing windowing functions and analytics

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...