sql - update - ¿Cómo puedo simplificar esta consulta de estadísticas del juego?

update table statistics sql server

Question

Este código funciona como se esperaba, pero es largo y espeluznante.

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

Como puede ver, consta de 3 partes repetitivas para recuperar:

• nombre del jugador y la cantidad de juegos que jugaron
• nombre del jugador y la cantidad de juegos que ganaron
• nombre del jugador y la cantidad de juegos que perdieron

Y cada uno de ellos también consta de 2 partes:

• nombre del jugador y la cantidad de juegos en los que participaron como jugador_1
• nombre del jugador y la cantidad de juegos en los que participaron como jugador_2

¿Cómo podría simplificarse esto?

El resultado se ve así:

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

Answer 1

Dado que esta es una búsqueda contra "largo y espeluznante", la consulta puede ser considerablemente más corta todavía. Incluso en la página 9.3 (o en realidad cualquier versión):

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;

Más explicaciones:

• Para un rendimiento absoluto, ¿es SUM más rápido o COUNT?

En la página 9.4 esto puede ser más limpio con la nueva cláusula AGREGAR FILTRO (como @Joe ya mencionado).

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;

• El manual
• Wiki Postgres
• Publicación de blog de Depesz

Answer 2

Este es un caso en el que las subconsultas correlacionadas pueden simplificar la lógica:

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;

Esto supone que no hay vínculos.

Answer 3

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;