将同一表的多个列上的条件计数相加

Question

我正在寻找一种“更好”的方式来执行查询，在该查询中，我想向单个玩家显示他之前玩过的游戏以及与每个此类对手相关的获胜记录。

以下是涉及的表格，精简如下：

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

因此，约翰对玛丽的战绩是2胜1负。vs鲍勃1胜0负; 和爱丽丝的3胜2负。

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

我正在使用Postgres 9.4。我脑海中有些东西告诉我要考虑一下，LATERAL但是我很难理解这样的“形状”。

以下是我当前正在使用的查询，但是有些“感觉不到”。请帮助我学习和改进。

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;

这按预期工作。只是想学习一些技巧或更好但更合适的技术来做到这一点。

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

Answer 1

询问

该查询并不像乍看起来那样简单。最短的查询字符串不一定会产生最佳性能。这应该 尽可能快 ，为此应尽可能短：

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;

结果完全符合要求：

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

SQL Fiddle- 具有更多显示的测试数据！

关键功能是 FULL [OUTER] JOIN
两个子查询之间的输赢。这将产生一个表格，其中列出了我们的候选人与之对抗的所有玩家。USING连接条件中的子句可以方便地将两player_id列合并为
一个 。

在那之后，用一个JOIN来player获取名称，并将
COALESCE
NULL替换为0。Voil谩。

指数

使用两个多列 索引 甚至会更快：

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

仅 当您从中获得仅索引扫描时。然后Postgres甚至根本不访问match表 ， 您将获得超快速的结果。

使用两integer列，您碰巧达到了 局部最优值 ：这些索引的大小与简单索引的大小相同。细节：

• 复合索引对第一个字段的查询是否也有用？

较短但较慢

您可以运行相关的子查询，例如@Giorgi建议，只要工作
正常即可 ：

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;

适用于 小型
桌子，但不能扩展。这需要player在match每个现有播放器上进行顺序扫描，并在其上进行两次索引扫描。将效果与进行比较EXPLAIN ANALYZE。