How Can I Efficiently Aggregate Game Statistics with Distinct Filters in PostgreSQL?

Streamlining Game Statistic Aggregation in PostgreSQL

The original code for aggregating game statistics is overly complex. This revised approach simplifies the process while retrieving player names and their game counts (played, won, and lost).

PostgreSQL 9.4 and Above (Using FILTER Clause)

PostgreSQL 9.4 and later versions provide an elegant solution using the FILTER clause:

<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>

The FILTER clause efficiently applies conditions to the aggregate functions, improving readability and performance.

PostgreSQL All Versions (Workaround)

For older PostgreSQL versions lacking the FILTER clause, this workaround achieves the same result:

<code class="language-sql">SELECT u.name,
       COUNT(CASE WHEN g.winner_id > 0 THEN 1 END) AS played,
       COUNT(CASE WHEN g.winner_id = u.id THEN 1 END) AS won,
       COUNT(CASE WHEN g.winner_id != u.id THEN 1 END) 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 method uses conditional aggregation with CASE statements to achieve the desired filtering. While functional across all versions, the FILTER clause (where available) is the preferred method for clarity and efficiency.

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