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