Come posso semplificare questa query sulle statistiche di gioco?

Questo codice funziona come previsto, ma io è lungo e inquietante.

select p.name, p.played, w.won, l.lost from (select users.name, count(games.name) as played from users inner join games on games.player_1_id = users.id where games.winner_id > 0 group by users.name union select users.name, count(games.name) as played from users inner join games on games.player_2_id = users.id where games.winner_id > 0 group by users.name) as p inner join (select users.name, count(games.name) as won from users inner join games on games.player_1_id = users.id where games.winner_id = users.id group by users.name union select users.name, count(games.name) as won from users inner join games on games.player_2_id = users.id where games.winner_id = users.id group by users.name) as w on p.name = w.name inner join (select users.name, count(games.name) as lost from users inner join games on games.player_1_id = users.id where games.winner_id != users.id group by users.name union select users.name, count(games.name) as lost from users inner join games on games.player_2_id = users.id where games.winner_id != users.id group by users.name) as l on l.name = p.name 

Come puoi vedere, consiste di 3 parti ripetitive per il recupero:

  • nome del giocatore e la quantità di giochi che hanno giocato
  • nome del giocatore e la quantità di giochi che hanno vinto
  • nome del giocatore e la quantità di giochi che hanno perso

E ciascuno di questi comprende anche 2 parti:

  • nome del giocatore e la quantità di giochi a cui hanno partecipato come giocatore_1
  • nome del giocatore e la quantità di giochi a cui hanno partecipato come giocatore_2

Come potrebbe essere semplificato?

Il risultato è così:

  name | played | won | lost ---------------------------+--------+-----+------ player_a | 5 | 2 | 3 player_b | 3 | 2 | 1 player_c | 2 | 1 | 1 

Poiché questa è una ricerca contro “lunga e inquietante”, la query può essere notevolmente più breve, ancora. Anche in pg 9.3 (o in realtà qualsiasi versione):

 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; 

Altre spiegazioni:

  • Per prestazioni assolute, SUM è più veloce o COUNT?

In pg 9.4 questo può essere più pulito con la nuova clausola FILTER Aggregate (come @Joe già menzionato).

 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; 
  • Il manuale
  • Wiki di Postgres
  • Depesz blog post

Questo è un caso in cui le subquery correlate possono semplificare la logica:

 select u.*, (played - won) as lost from (select u.*, (select count(*) from games g where g.player_1_id = u.id or g.player_2_id = u.id ) as played, (select count(*) from games g where g.winner_id = u.id ) as won from users u ) u; 

Questo presuppone che non ci siano legami.

 select users.name, count(case when games.winner_id > 0 then games.name else null end) as played, count(case when games.winner_id = users.id then games.name else null end) as won, count(case when games.winner_id != users.id then games.name else null end) as lost from users inner join games on games.player_1_id = users.id or games.player_2_id = users.id group by users.name;