Understanding hypothetical aggregates

Hypothetical aggregates are pretty similar to standard ordered sets. However, they help to answer a different kind of question: what would be the result if a value was in the data? As you can see, this is not about values inside the database but about the result if a certain value was actually there.

The only hypothetical function that’s provided by PostgreSQL is rank, as shown in the following code:

test=# SELECT region,
    rank(9000) WITHIN GROUP
(ORDER BY production DESC NULLS LAST)
FROM t_oil
GROUP BY ROLLUP (1);
    region     | rank
---------------+------
 Middle East   |  21
 North America |  27
               |  47
(3 rows)

The preceding code tells us this: if somebody produced 9,000 barrels of oil per day, it would be ranked the 27th-best...