Utilizing windowing functions and analytics

Now that 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      | 1967  |     10219  |      12567  | 2607.5139 
USA    ...