Making use of ordered sets

Ordered sets are powerful features, but are not widely regarded as such and 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 would be the calculation of the median.

The median is the middle value. If you are, for example, earning the median income, the number of people earning less and more than you is 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...