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 there? 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 provided by PostgreSQL is rank:

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)

It tells us: If somebody produced, 9000 barrels per day, it would be ranked the 27th best year in North America and 21st in the Middle East.

Note

In my example, I used NULLS LAST. When data is sorted, nulls are usually at the end. However, if sort order is reversed, nulls should still be at the end of the list. NULLS LAST ensures exactly...