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 production in our dataset is around 2.6 million barrels per day. The goal of this query is to add this value as a column. It is now easy to compare the current row to the overall average.

Keep in mind that the OVER clause is essential. PostgreSQL is not able to process the query without it:

test=# SELECT country, year, production, consumption, avg(production)  
FROM t_oil; 
ERROR:  column "t_oil.country" must appear in the GROUP BY clause or be used in an aggregate function 
LINE 1: SELECT country, year, production, consumption, avg(productio... 

This actually makes sense because the average has to be defined precisely. The database engine cannot just take any value, which might be right by doing guesswork.