Utilizing windowing functions and analytics

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      | 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 guess at any value.