Partial aggregation: The beautiful way

Partial aggregation: Aggregations are a fundamental feature of SQL. Functions such as min, max, count, sum, avg and so on are used in virtually every application – it is basically impossible to write a reasonably complex applications without them.

Here is a trivial aggregate: All it does is summing up numbers from 1 to 10:

test=# SELECT sum(x) FROM generate_series(1, 10) AS x;
 sum
-----
  55
(1 row)

So far so good. But what if we only want to sum up even numbers? We can rely on a simple trick: NULL values are ignored by aggregation functions. This makes a lot of sense because NULL really means “undefined” and you cannot average or sum up undefined values.  A simple CASE WHEN can do the job:

test=# SELECT sum(CASE WHEN x % 2 = 0 THEN x ELSE NULL END)
            FROM generate_series(1, 10) AS x;
 sum
-----
  30
(1 row)

This works perfectly – however, it is a bit old fashioned and pretty nasty to write (especially in case of more complex operations).

The fancy way to partial aggregation

There is a more fancy way to do that:

test=# SELECT     sum(x) FILTER (WHERE x % 2 = 0) AS even,
                  sum(x) FILTER (WHERE x % 2 = 1) AS odd
       FROM  generate_series(1, 10) AS x;
 even | odd
------+-----
   30 |  25
(1 row)

The FILTER keywords allow users to tell the systems which rows should make it into the aggregate function. The new syntax is far more readable, as well as a lot shorter.

Find out more about partial aggregation in PostgreSQL 16 in this blog post about Parallel Aggregates by Pavlo Golub.

In case you need any assistance, please feel free to contact us.
 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.