Sume counts condicionales en varias columnas de la misma tabla

Estoy buscando una "mejor" manera de realizar una consulta en la que quiero mostrar a un solo jugador que ha jugado anteriormente y el logging de victorias y derrotas correspondiente para cada uno de esos oponentes.

Aquí están las tablas involucradas despojadas de lo esencial:

create table player (player_id int, username text); create table match (winner_id int, loser_id int); insert into player values (1, 'john'), (2, 'mary'), (3, 'bob'), (4, 'alice'); insert into match values (1, 2), (1, 2), (1, 3), (1, 4), (1, 4), (1, 4) , (2, 1), (4, 1), (4, 1); 

Por lo tanto, John tiene un récord de 2 victorias y 1 derrota frente a Mary; 1 victoria y 0 derrotas vs bob; y 3 victorias y 2 derrotas contra alice.

 create index idx_winners on match(winner_id); create index idx_winners on match(loser_id); 

Estoy usando Postgres 9.4. Algo en la parte posterior de mi cabeza me dice que considere LATERAL alguna manera, pero estoy teniendo dificultades para entender la "forma" de tal.

La siguiente es la consulta que estoy usando actualmente pero algo "se siente apagado". Por favor, ayúdame a aprender y mejorar esto.

 select p.username as opponent, coalesce(r.won, 0) as won, coalesce(r.lost, 0) as lost from ( select m.winner_id, m.loser_id, count(m.*) as won, ( select t.lost from ( select winner_id, loser_id, count(*) as lost from match where loser_id = m.winner_id and winner_id = m.loser_id group by winner_id, loser_id ) t ) from match m where m.winner_id = 1 -- this would be a parameter group by m.winner_id, m.loser_id ) r join player p on p.player_id = r.loser_id; 

Esto funciona como se esperaba Solo estoy buscando aprender algunos trucos o mejores técnicas para hacer lo mismo.

 opponent won lost -------- --- ---- alice 3 2 bob 1 0 mary 2 1 

Consulta

La consulta no es tan simple como parece al principio. La cadena de consulta más corta no produce necesariamente el mejor performance. Esto debería ser tan rápido como sea posible , siendo lo más breve posible para eso:

 SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT loser_id AS player_id, count(*) AS ct FROM match WHERE winner_id = 1 -- your player_id here GROUP BY 1 -- positional reference (not your player_id) ) w FULL JOIN ( SELECT winner_id AS player_id, count(*) AS ct FROM match WHERE loser_id = 1 -- your player_id here GROUP BY 1 ) l USING (player_id) JOIN player p USING (player_id) ORDER BY 1; 

Resultado exactamente como se solicitó:

 username | won | lost ---------+-----+----- alice | 3 | 2 bob | 1 | 0 mary | 2 | 1 

SQL Fiddle : ¡con datos de testing más reveladores!

La característica key es el FULL [OUTER] JOIN entre las dos subconsultas para pérdidas y ganancias. Esto produce una tabla de todos los jugadores contra los que nuestro candidato ha jugado. La cláusula USING en la condición de unión combina convenientemente las dos columnas player_id en una .

Después de eso, un solo JOIN to player para get el nombre, y COALESCE para replace NULL con 0. Voilá.

Índice

Sería aún más rápido con dos índices de columnas múltiples :

 CREATE INDEX idx_winner on match (winner_id, loser_id); CREATE INDEX idx_loser on match (loser_id, winner_id); 

Solo si obtiene escaneos index-only de esto. Luego, Postgres ni siquiera visita la table de match y obtienes resultados súper rápidos.

Con dos columnas integer se llega a un óptimo local : estos índices tienen el mismo tamaño que los simples que tenía. Detalles:

  • ¿Un índice compuesto también es bueno para consultas en el primer campo?

Más corto, pero lento

Podría ejecutar subconsultas correlacionadas, como sugirió @Giorgi , simplemente trabajando correctamente :

 SELECT * FROM ( SELECT username , (SELECT count(*) FROM match WHERE loser_id = p.player_id AND winner_id = 1) AS won , (SELECT count(*) FROM match WHERE winner_id = p.player_id AND loser_id = 1) AS lost FROM player p WHERE player_id <> 1 ) sub WHERE (won > 0 OR lost > 0) ORDER BY username; 

Funciona bien para tablas pequeñas , pero no escala. Esto requiere un escaneo secuencial en el player y dos escaneos de índice en el match por jugador existente. Compare el performance con EXPLAIN ANALYZE .

Solución con subconsulta correlacionada:

 SELECT *, (SELECT COUNT(*) FROM match WHERE loser_id = p.player_id), (SELECT COUNT(*) FROM match WHERE winner_id = p.player_id) FROM dbo.player p WHERE player_id <> 1 

Solución con UNION y agregación condicional:

 SELECT t.loser_id , SUM(CASE WHEN result = 1 THEN 1 ELSE 0 END) , SUM(CASE WHEN result = -1 THEN 1 ELSE 0 END) FROM ( SELECT * , 1 AS result FROM match WHERE winner_id = 1 UNION ALL SELECT loser_id , winner_id , -1 AS result FROM match WHERE loser_id = 1 ) t GROUP BY t.loser_id 

Para un solo jugador 'sujeto', simplemente uniría al jugador en los roles ganador y perdedor, y resumiría las ganancias / pérdidas:

 SELECT opponent, SUM(won) as won, SUM(lost) as lost FROM ( select w.username AS opponent, 0 AS won, 1 as lost, m.loser_id as me from "match" m inner join "player" w on m.winner_id = w.player_id UNION ALL select l.username AS opponent, 1 AS won, 0 as lost, m.winner_id as me from "match" m inner join "player" l on m.loser_id = l.player_id ) x WHERE me = 1 GROUP BY opponent; 

Para una operación basada en set, podemos simplemente unir los jugadores a la misma tabla de unión derivada:

 SELECT p.username as player, x.opponent, SUM(x.won) as won, SUM(x.lost) as lost FROM "player" p LEFT JOIN ( select w.username AS opponent, 0 AS won, 1 as lost, m.loser_id as me from "match" m inner join "player" w on m.winner_id = w.player_id UNION ALL select l.username AS opponent, 1 AS won, 0 as lost, m.winner_id as me from "match" m inner join "player" l on m.loser_id = l.player_id ) x on p.player_id = x.me GROUP BY player, opponent; 

SqlFiddles de ambos aquí

Un pequeño punto: los nombres de los índices deben ser únicos; presumiblemente, se refería a:

 create index idx_winners on match(winner_id); create index idx_losers on match(loser_id); 

Algo más legible que mi original. ¿Pensamientos?

 with W as ( select loser_id as opponent_id, count(*) as n from match where winner_id = 1 group by loser_id ), L as ( select winner_id as opponent_id, count(*) as n from match where loser_id = 1 group by winner_id ) select player.username, coalesce(Wn, 0) as wins, coalesce(Ln, 0) as losses from player left join W on W.opponent_id = player.player_id left join L on L.opponent_id = player.player_id where player.player_id != 1; QUERY PLAN ----------------------------------------------------------------------------- Hash Left Join (cost=73.78..108.58 rows=1224 width=48) Hash Cond: (player.player_id = l.opponent_id) CTE w -> HashAggregate (cost=36.81..36.83 rows=2 width=4) Group Key: match.loser_id -> Seq Scan on match (cost=0.00..36.75 rows=11 width=4) Filter: (winner_id = 1) CTE l -> HashAggregate (cost=36.81..36.83 rows=2 width=4) Group Key: match_1.winner_id -> Seq Scan on match match_1 (cost=0.00..36.75 rows=11 width=4) Filter: (loser_id = 1) -> Hash Left Join (cost=0.07..30.15 rows=1224 width=44) Hash Cond: (player.player_id = w.opponent_id) -> Seq Scan on player (cost=0.00..25.38 rows=1224 width=36) Filter: (player_id <> 1) -> Hash (cost=0.04..0.04 rows=2 width=12) -> CTE Scan on w (cost=0.00..0.04 rows=2 width=12) -> Hash (cost=0.04..0.04 rows=2 width=12) -> CTE Scan on l (cost=0.00..0.04 rows=2 width=12) 

¡Lo anterior tiene un asesino de performance con el player_id! = 1. Creo que puedo evitar eso escaneando solo los resultados de las uniones, ¿no?

 explain with W as ( select loser_id as opponent_id, count(*) as n from match where winner_id = 1 group by loser_id ), L as ( select winner_id as opponent_id, count(*) as n from match where loser_id = 1 group by winner_id ) select t.* from ( select player.player_id, player.username, coalesce(Wn, 0) as wins, coalesce(Ln, 0) as losses from player left join W on W.opponent_id = player.player_id left join L on L.opponent_id = player.player_id ) t where t.player_id != 1; QUERY PLAN ----------------------------------------------------------------------------- Hash Left Join (cost=73.78..74.89 rows=3 width=52) Hash Cond: (player.player_id = l.opponent_id) CTE w -> HashAggregate (cost=36.81..36.83 rows=2 width=4) Group Key: match.loser_id -> Seq Scan on match (cost=0.00..36.75 rows=11 width=4) Filter: (winner_id = 1) CTE l -> HashAggregate (cost=36.81..36.83 rows=2 width=4) Group Key: match_1.winner_id -> Seq Scan on match match_1 (cost=0.00..36.75 rows=11 width=4) Filter: (loser_id = 1) -> Hash Left Join (cost=0.07..1.15 rows=3 width=44) Hash Cond: (player.player_id = w.opponent_id) -> Seq Scan on player (cost=0.00..1.05 rows=3 width=36) Filter: (player_id <> 1) -> Hash (cost=0.04..0.04 rows=2 width=12) -> CTE Scan on w (cost=0.00..0.04 rows=2 width=12) -> Hash (cost=0.04..0.04 rows=2 width=12) -> CTE Scan on l (cost=0.00..0.04 rows=2 width=12)