Making use of ordered sets

Ordered sets are powerful features, but are not widely regarded as such and are not widely known in the developer community. The idea is actually quite simple: data is grouped normally, and then the data inside each group is ordered given a certain condition. The calculation is then performed on this sorted data.

A classic example is the calculation of the median.

The median is the middle value. If you are, for example, earning the median income, the numbers of people earning less and more than you are identical; 50% of people do better and 50% of people do worse.

One way to get the median is to take sorted data and move 50% into the dataset. This is an example of what the WITHIN GROUP clause will ask PostgreSQL to do:

test=# SELECT region, 
   percentile_disc(0.5) WITHIN GROUP (ORDER BY production) 
FROM t_oil 
GROUP BY 1; 
 region         | percentile_disc 
----------------+-----------------  
 Middle East    |            1082 
 North  America |            3054...