Mastering PostgreSQL 12 - Third Edition

Using ordered sets – the WITHIN GROUP clause

Ordered sets are fairly new to PostgreSQL. The difference between an ordered set and a normal aggregate is that, in the case of an ordered set, the way data is fed to the aggregate does make a difference. Suppose you want to find a trend in your data—the order of the data is relevant.

Here is a simple example of calculating a median value:

test=# SELECT id % 2,          percentile_disc(0.5) WITHIN GROUP (ORDER BY id)    FROM generate_series(1, 123) AS id    GROUP BY 1;  ?column? | percentile_disc ----------+-----------------         0 | 62         1 | 61 (2 rows) 

The median can only be determined if there is sorted input.