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. It tells us:

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)

If somebody produced 9000 barrels per day, it would be the 27 best year in North America and 21 in the Middle East.

Note

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