How Can I Efficiently Aggregate Player Game Statistics in PostgreSQL?

Optimizing PostgreSQL Queries for Player Game Statistics Aggregation

This article addresses the efficient retrieval of player game statistics in PostgreSQL, specifically focusing on player name, games played, games won, and games lost. The challenge lies in consolidating data where players can participate as either player_1 or player_2. Traditional approaches often involve multiple, repetitive queries.

Streamlined Query Solutions

For PostgreSQL 9.4 and later:

Leverage the FILTER clause for concise and readable code:

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

For all PostgreSQL versions:

A compatible solution for older versions utilizes OR and NULL:

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

These optimized queries effectively aggregate player statistics, avoiding redundant queries and enhancing performance. Choosing the appropriate method depends on your PostgreSQL version.

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