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 | 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 unable to process the query without it:
test=# SELECT country, year, production, consumption, avg(production) FROM t_oil;
psql: 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 any value.