How Can I Efficiently Aggregate Columns with Distinct Filters in PostgreSQL?

PostgreSQL efficient aggregation column and Distinct filter

The following code retrieves data for three separate aggregate columns: games played, games won, and games lost. Although this code works as expected, it is repetitive and potentially difficult to maintain. To simplify this query, consider using advanced PostgreSQL features.

PostgreSQL 9.4 and higher

PostgreSQL 9.4 introduces a new aggregate FILTER clause. This clause allows you to apply a filter to an aggregate calculation, considering only rows that satisfy the specified Boolean expression.

<code class="language-sql">SELECT u.name
     , count(*) FILTER (WHERE g.winner_id > 0)    AS played
     , count(*) FILTER (WHERE g.winner_id = u.id) AS won
     , count(*) FILTER (WHERE g.winner_id != u.id) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;</code>

PostgreSQL versions before 9.4

For earlier versions of PostgreSQL, you can use expression (expression OR NULL) as a workaround.

<code class="language-sql">SELECT u.name
     , count(g.winner_id > 0 OR NULL)    AS played
     , count(g.winner_id = u.id OR NULL) AS won
     , count(g.winner_id != u.id OR NULL) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;</code>

This workaround is shorter and faster than using nested subselects or CASE expressions. For further optimization considerations, see the references mentioned in the provided answers.

The above is the detailed content of How Can I Efficiently Aggregate Columns with Distinct Filters in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!